SQL Server Performance Analysis as a Service
There are lots of tools around Performance Diagnostics and Analysis on the market, of course including those from Quest Software, the company I’m working for.
So why do I want to introduce you to another one? One that has just recently started off and therefore is still like a 1.0 version?
Well, because this one is probaby quite different from the ones you know. And these differences really make a difference!
- It’s free!
- You don’t need to install anything in order to use it (well, at least using parts of it 😉
- Neither does the tool decide what is good or bad performance nor do you have to interpret lots of numbers. Instead, it gives you a detailed picture of how your SQL Server is handling its workload, compared to how other SQL servers from throughout the world are handling the same or similar workloads!
- Did I mention it’s free?
Sounds magic? But it’s straight from reality, and it’s called “Project Lucy”, and it’s live: https://www.projectlucy.com
How does it work?
In a nutshell, it’s a Performance Advisory SaaS (Software-as-a-Service), combined with a data warehouse of anonymized real-world performance data, along with their associated workloads.
Be aware that YOU have to add some value in order to get some value back. Specifically, one way to use it is:
- Collect a SQL Server trace file.
- Upload the trace file in order to get a performance report back.
- Your uploaded trace data will be anonymized and added to the real-world performance data warehouse in order to make future reports even more real-world and accurate.
All of this stuff is web based, so – after successfully logging in – you can access your reports any time from any web browser, and there are also plans for providing detailed trend analysis based on regular uploads.
The mentioned trace files are a standard technique that has been around in the SQL Server world for ages. Project Lucy provides adequate Trace Templates to make this as few clicks as possible for you: https://www.projectlucy.com/Developers/SQLServerTraceTemplates
Also, there is a description how we can fully automate/schedule trace file collection and upload: https://www.projectlucy.com/Developers/AutomatingUploadsToProjectLucy
To be honest, in the current version Project Lucy returns a detailed, browsable analysis of the trace data, but not yet a comparison with the rest of the world. But this is planned as soon as there is a statistically reasonable amount of data available. So, please keep the traces coming…
Here is a sample view of a trace file analysis report:
Not all performance data can be reasonably collected by a trace, as each trace also creates some overhead and a vast amount of raw data. While a trace fits best for specific performance investigations, other interfaces like dynamic management views are better suited for getting a general overview of SQL Server performance. Again, the challenge is always to interpret the numbers and decide whether it’s expected behaviour or an indication of severe performance issues…
For the first part – collecting performance metric and counters – Quest Software’s Spotlight on SQL Server has been around for many years now. Especially in its Enterprise Edition it does a 24-by-7 job of collecting various performance metrics from SQL Server and Windows in order to provide real-time alerting and incident management.
In its latest and greatest version (v8.0) Spotlight offers integration with Project Lucy. What does that mean? Well, Spotlight typically archives its data anyway. Therefore it’s very easy to have it regularly upload select performance metrics once a day into Project Lucy.
Again, what’s the trick behind it?
- You give some anonymized real-world performance data to the community. Here is a detailed description of which data is uploaded: https://www.projectlucy.com/static/spotlightdataupload
- You get back a report on how your SQL Server is doing and how others with a similar workload are doing.
Here is a sample screenshot of what you get back (among various other things):
And here is another sample:
So, if you are already using Spotlight on SQL Server (either the standalone Enterprise Edition or the Desktop Edition which is part of Toad for SQL Server DBA Suite), you can easily leverage this to benefit from Project Lucy. It’s actually one the main new features of this latest Spotlight version, so make sure you don’t miss it 😉
Configuration inside Spotlight is as easy as the following screenshot:
How to start?
If you want to give it a try, here are some useful links how to start with it:
Project Lucy Homepage: https://www.projectlucy.com/
Video on howto explore a workload: http://www.youtube.com/v/yRzeyRKwloQ?version=3&rel=0&autoplay=1
Project Lucy on Twitter: http://twitter.com/qsftprojectlucy
Spotlight on SQL Server Trial Download: http://www.quest.com/spotlight-on-sql-server-enterprise/