I’m here at the SQL Connections conference in April of 2008 and I’m giving a presentation on some of the new Manageability Improvements for SQL Server 2008. In this blog post I’ll talk about one three of the new performance troubleshooting features in SQL Server 2008 Management Studio: Data Collectors, the Management Data Warehouse, and Performance Reports.
To effectively diagnose a performance issue, you need to have an awareness of the state of the system before it had the problem. For instance, if someone walks into your office and says “The system is slow.” You’ll probably respond “When did you notice it?”, to which you’ll often get the reply, “oh, I don’t know. Sometime last week, I think.” That’s always a difficult thing to research, since you may not know exactly when the problem started, and you may not have anything to go on to narrow down the problem.
So most of us rely on custom scripts that let us review the Dynamic Management Views (DMVs)or other meta-data about the system, and we try and reverse-engineer the problem. SQL Server 2008 Management Studio includes three new features that allow you to deal with this problem, and these features also let you baseline your system and track it over time. The first feature is the Data Collector.
The Data Collector is a system that can collect data from any TSQL query like DMVs, Windows Performance Counters, and the SQL Trace. It bundles any selection of these items into a Collection Set. We’ve created a set of these for you already, and you can activate them with a simple wizard. But what are we collecting?
We actually consulted with Product Support - the folks that get called when there’s a problem with SQL Server. We collected the top items that plagued people with performance, and then we put the experts here at Microsoft to discover what they would do to find those kinds of issues. We took their knowledge and built that in to the Collection Sets we deliver with SQL Server Management Studio (SSMS). With all of that collecting going on, you’ll need a place to put the data.
The next feature we created was the Management Data Warehouse. This is a database, built with a wizard we supply for you, that has all of the necessary tables, views and so on to hold the Collection Sets we’ve created. You can extend this database, create reports from it, or even create an Analysis Services Cube over it if you wish. The real power is that you can place it on any SQL Server 2008 Instance that you want, and you can point multiple Data Collectors to it. That means you could run the Data Collector on several systems, but store the results of those collections in a central location.
With all of the data collected into a single database, you’ll definitely want a way to view the results. The next improvement we included is a set of Performance Data Warehouse Reports that show the data in the Management Data Warehouse. But these reports aren’t like the other 50 standard reports we have in SSMS. These reports are “drill-through”, meaning that the charts, text and other objects on the reports can be clicked on to see more detail. In fact, in the demonstration we do for this feature, we drill completely through a problem that occurred a week ago on a system, all the way down to the problematic query that caused the issue.
But it doesn’t even stop there. We also drill all the way through to the reason that the problem occurred – which in this case is a missing index (one that got accidentally dropped), and SSMS even displays the script you need to run to re-create the index. This is all done in a matter of minutes, and most importantly, without the overhead of running the Profiler all the time.
These are the kinds of features that will really improve your day. You’ll find yourself using this baselining and troubleshooting process on all of your new systems, and it will help you rapidly find and diagnose issues. It’s all about saving time and keeping you on top of your systems.
In future posts I’ll describe how you can use this technology to do more than just a particular problem – I’ll show you how to diagnose trends with it.
Books Online isn't complete for this feature yet - I'll update this post when it is.