Skip to content

SQL Server Performance Analysis as a Service

June 17, 2011

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!

  1. It’s free!
  2. You don’t need to install anything in order to use it (well, at least using parts of it 😉
  3. 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!
  4. 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:

  1. Collect a SQL Server trace file.
  2. Upload the trace file in order to get a performance report back.
  3. 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:

Project Lucy Workload Analysis From Trace

Project Lucy Workload Analysis From Trace

 

Advanced features

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.

Collecting performance data is easy with Spotlight on SQL Server

Collecting performance data is easy with Spotlight on SQL Server

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?

  1. 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
  2. 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):

Project Lucy reporting on I/O Statistics Highlights

Project Lucy reporting on I/O Statistics Highlights

 And here is another sample:

Project Lucy reporting on I/O statistics details

Project Lucy reporting on I/O statistics details

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:

Configure Spotlight to upload performance data into Project Lucy

Configure Spotlight to upload performance data into Project Lucy

 

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/

 
 
Advertisements

From → SQL Server

3 Comments
  1. Bart van Knijff permalink

    Cool post. very useful.

  2. It looks interesting . Does it give you advice on other aspects of the Performance stack , such as OS? Does it accept other input ssuch as Perfmon?

    • pschwanke permalink

      Not yet. But we are planning on accepting perfmon logs in the future and correlating that data as well.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: