Skip navigation

Familiar SQL Server Tools in a New Wrapper

In response to my commentary last week, a reader commented that I shouldn’t participate in Microsoft’s marketing events. The reader seemed to imply that marketing is a “bad thing,” as in technical = good and marketing = bad. That comment made me start thinking about the function of marketing and reminded me of a particular example of how Microsoft’s marketing actually made me aware of functionality that I otherwise wouldn’t have known about. It’s the marketing information that inspired me to dig down into the technology.

I’m referring to a suite of solutions that not too long ago was called “Performance Studio.” However, one problem with Microsoft’s marketing message is that some of the names of SQL Server 2008 components are still in flux. It’s hard to market something if you’re not sure what to call it. Some people have been using the terms Performance Studio, Performance Data Collector, and Data Collection interchangeably. Lately, it seems that Microsoft has been using the term Data Collection the most often. To me that name seems less than ideal, as it doesn’t represent the fact that it includes more than just one tool. In fact, in the SQL Server 2008 February CTP, I didn't find any reference to the term “Performance Studio.” So I’ll call the suite “Data Collection.”

Whatever you call it, Data Collection isn't actually a tool, but rather a wrapper around three different solutions: Data Collector, performance and diagnostic monitoring, and Management Data Warehouse for historical reporting. This suite isn't really the first version of some brand-new technology. Most of the technology that supports these features has been around for at least two versions in the form of SQL Agent jobs, SQL Trace, performance monitoring, dynamic management views, and SQL Server Reporting Services. The technology is well understood and well developed; it's just packaged and presented differently in SQL Server 2008.
The Data Collector is a process that collects the data for Data Collection. Using SQL Server Management Studio (SSMS), you can configure the various types of data that you want to have collected. Of course, you also have the option to use scripts to configure your data collection, but like SQL Agent alerts and jobs, it’s quite a bit more complex, with a long list of parameters you're required to specify.  You can also configure a new database to be used to hold the collected data, and this database is referred to as the Management Data Warehouse. When you first enable Data Collection, three system data collector sets are set up for you: query statistics, disk usage, and server activity. All of these data sets are based on performance information that's available in earlier versions of SQL Server.

SQL Server 2008 also provides predefined reports that let you investigate and drill down into details stored in the Management Data Warehouse database. When looking at query statistics, you can generate a report that shows the slowest running queries and then drill down to see the different plans used by the queries, how often each plan was run, and even display the plan graphically. When looking at server activity, you can drill down into the longest held locks to see what queries were blocking and who was blocking whom. One of the most useful features is the ability to quickly find out all the details about the process that's the head of a blocking chain.
All of these capabilities have been available, but to have them all packaged up neatly for any DBA to use, with automatic report-generation capabilities, is invaluable.

This packaging doesn’t mean that SQL Server expertise is no longer required. You still have to know what to do with all this data to your improve your query and system performance. And the marketing message is a mess—Microsoft doesn’t even know what to call it! But if I hadn’t attending a marketing session, I never would have known just how easy it would be to have valuable performance-tuning data so readily available.



Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.