As DBAs, we always want to have some kind of peripheral eye on the performance of our SQL Server instances. Until SQL Server 2008 was released, much of this type of performance monitoring was accomplished with 3rd party tools, or with custom processes cobbling together dynamic management views, SQL Trace, Performance Dashboard Reports, and other facilities. In SQL Server 2008 we get Data Collection, a built-in framework for collecting and analyzing performance and other metrics from multiple servers. It is definitely a “version 1.0” feature at this point, and I thought it would be useful to compare some of the features with a 3rd party tool that I use in my environment, Performance Advisor from SQL Sentry.
If you want to get a general overview of Data Collection and how it works, please start here:
http://msdn.microsoft.com/en-us/library/bb677179.aspx
The First Few Hurdles
As soon as you start, a very glaring weakness of Data Collection is that it can only collect data from non-Express instances of SQL Server 2008. Since there are many dependencies on new SQL Server 2008 features, and because you need to configure data collection on each instance, there is simply no way to collect and upload data on other instances because the nodes in Management Studio’s Object Explorer (and the underlying procedures they ultimately call) simply don’t exist. Performance Advisor, on the other hand, works against any SQL Server 2000, 2005 or 2008 instance, and you don’t need to install anything directly to monitor an individual instance. You can simply pull in all of the instances you want to monitor from within one section of the SQL Sentry Console:
SQL Sentry's Watch menu
(With Data Collection, you need to go to each instance in Object Explorer, right-click Data Collection, and follow the “Configure Management Data Warehouse” wizard, even if you simply want to allow Data Collection from this instance to an existing warehouse that is already configured. I didn't find this process very intuitive at all.)
The next thing you will find out about Data Collection is that it is quite limited out of the box. You only get three collection sets configured by default: Disk Usage, Query Statistics, and Server Activity. You can configure your own collection sets, but as of right now, this is a cumbersome process with a steep learning curve. You can learn more about this at the following URLs:
http://msdn.microsoft.com/en-us/library/bb677277.aspx
Disk Usage Report
Disk Usage refers to simply tracking the size and average growth of data and log files. The report is quite simple:
Data Collection's Disk Usage report
While you can't change any of the parameters of the report (for my systems it seems to always only show 48 hours in the averages), you can drill in to an individual database to see the disk usage breakdown (a.k.a. sp_spaceused), as well as a history of autogrow/autoshrink events:
Data Collection's Disk Usage report
Performance Advisor goes above and beyond in this category, providing disk capacity statistics like Data Collection does, and adding real-time disk activity views that show exactly where the I/Os are coming and going. In the following graphic, you can see light write activity going to tempdb’s log and to master’s log, and very heavy read activity coming from the right-most node (which represents non-SQL Server activity).
Performance Advisor's Disk Activity tab
Performance Advisor's Disk Space tab
Query Statistics Report
The Query Statistics report tracks queries by execution count, CPU, duration, reads and writes. One difficulty I found here is that the report shows individual statements, but not the stored procedure and/or batch they were called within. And to see the whole SQL statement, you have to click through on each individual statement and pull up a sub-report. And finally, there is no way to aggregate similar queries by removing actual parameter values and inserting placeholders instead:
Data Collection's Query Statistics History report
With Performance Advisor, you can get to all of these, and you can easily switch between individual statements and rolled up statements (and when rolled up, you can drill down to each individual statement, and see which parameter value(s) might have caused issues):
Performance Advisor's Top SQL tab - raw queries
Performance Advisor's Top SQL tab - normalized queries with drill-downs
Server Activity Report
The Server Activity report shows CPU, Memory, I/O, Network, Waits, and activity such as logins, (re-)compilations, transactions and user connections. You can see the report here:
Data Collection's Server Activity History report
Most of this data, as well as many more relevant metrics, are covered in great detail on Performance Advisor's dashboard view:
Performance Advisor's Dashboard - Sample view (real-time)
And it is very easy to switch from sample view (above), which shows real-time data, to history view (below), which can show anything from a minute to a year of data in one shot (Management Data Warehouse can only show you canned, rolled up reports for pre-defined intervals):
Performance Advisor's Dashboard - History view
Another great feature of Performance Advisor's dashoard is that it is interactive - you can highlight any point or date range and have it synchronize across all charts, which is invaluable when correlating what was happening with different metrics at a given time. Auto-context menus allow you to zoom in further, or jump to Top SQL, Disk Activity, the calendar, or any other tab using the highlighted date range. In fact this is the only product I have reviewed to date that has such a seamless and easy way to correlate all kinds of different performance metrics across the exact same time range.
Extending Reports
Apart from those canned reports that ship with SSMS, any extended reporting is up to you. For example, if you wanted to roll up query stats by statement (ignoring actual parameter values), or view reports across multiple instances, you could write reports against the tables where the Data Collector stores its data. But it will be a discovery process just to track it down, and you will need to become familiar with building reports if you want them to be available on demand within Management Studio. You can do a lot with this, but it can certainly become a daunting amount of work, depending on how much of what you need is already provided out of the box.
Performance Advisor already has far more reaching and flexible reporting already configured, and makes it very easy to filter and customize. For my own monitoring purposes, and with the caveat that I have only been using the tool for a couple of months, I have yet to come across a scenario I wanted to monitor or report on, but couldn't. Most everything I want to see is either on the Top SQL report or on the Dashboard.
Removing the Management Data Warehouse
Once you have configured a Management Data Warehouse, it cannot be uninstalled easily. Fellow SQL Server MVP Tibor Karaszi blogged about the challenges he encountered in trying to remove the warehouse once he had configured it:
And Lara R reported the problem on Connect, where Bill Ramos tells us that the fix is deferred until at least the next version of SQL Server:
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=334180
Some Added Benefits
What I really like about Performance Advisor is the automatic alerts that get set up when you configure a server to be watched. For example, without lifting a finger, you have an automatic trigger-like action in place that will send an e-mail whenever you have a deadlock, or a DTS package failure, or a SQL Server or SQL Server Agent start/stop event, or a cluster failover. These alerts are easy to configure, and there are a large number of pre-configured actions that you can have Performance Advisor respond to in various ways, from sending an e-mail to writing to the log to firing a job to executing ad hoc T-SQL. For example, in addition to the default behavior of sending an e-mail, I can add a T-SQL action that runs an INSERT statement:
Performance Advisor's General Actions panel
This makes monitoring much more proactive and flexible… no more drilling through reports or trace tables to find deadlock events, you can have them e-mailed to you or sent directly to your mobile! You could configure something like this manually with Data Collection, but it would involve triggers or query notifications against the tables that the system creates for you, and there is no guarantee the code would continue to work through system changes, for example if you were to disable and re-enable Data Collection, or if you installed a cumulative update or service pack that affects those components.
Another thing I like about Performance Advisor is the ability to set up a QuickTrace – just like it sounds, this is a very expedient way to set up a quick, lightweight trace, on demand and in real time:
Performance Advisor's QuickTraces tab
Unlike with Data Collection, where I have to go from a report to either profiler or a bunch of T-SQL statements to set up a trace, I can jump to a QuickTrace right from within the GUI as the problem is occurring. You can also automate a QuickTrace to respond to certain events, such as exceeding a query duration threshold. I can get a little bit of this behavior from within the new Activity Monitor in SSMS, but that just means I need to have another window up and monitoring my server, and catch the problem as it happens.
There is also a Blocking tab which provides very elaborate blocking analysis... it shows the blocking and blocked statements, the wait resource, and the wait type, allowing you to quickly solve nasty blocking issues:
Performance Advisor's Blocking SQL tab
Similarly, a Deadlocks tab allows you to perform the same type of analysis for deadlocks, which you would otherwise need to catch in a server-side trace:
Performance Advisor's Deadlocks tab
Conclusion
Keep in mind that with performance monitoring in general, you can't always get the full story. For example, a change in performance could be due to something you could correlate within a report (e.g. multiple queries running simultaneously), but it could be due to something else you are not capturing (e.g. changing features that control impact on CPU, disk, memory, etc. such as data compression or resource governor).
With that said, yes you can get some decent, basic performance monitoring out of the box with SQL Server 2008’s new Data Collection feature, and with a little work, you can make it more useful. Understanding that development time really equates to money, you can use a tool like Performance Advisor to get that out of the gate, with several additional bonuses as well. It all depends on whether you want monitoring to just be there for you, or whether you want to roll up your sleeves and dig in.