Vidas Matelis Analysis Services Blog

In SQL Server 2008 Microsoft introduced “Management Data Warehouse” (MDW). This blog post should give you a good idea on what to expect from this new feature. Note: All tests and screenshots for this post were done in SQL Server 2008 February CTP (CTP6). 

Before starting MDW configuration please make sure that your SQL Server Agent is running. You can configure Management Data Warehouse by going into SQL Server Management studio, then expanding Management folder and right mouse clicking on Data Collection folder and choosing “Configure Management Data Warehouse”. This will start wizard that will ask you MDW database connection information (server, database and authentication). As MDW database puts some overhead for the server, in production environment you probably will want to have this database on a separate server. After wizard completes you should see green arrows for Data collection items:

SQL Server 2008 CTP6 was shipped with 3 data collection sets. These sets run at set frequency and collects data into MDW database. To give you an idea – “Disk usage” collection set runs every 5 seconds, “Query Statistics” collection set runs every 10 seconds and “Server Activity” collection set runs every 60 seconds. Of course, you can easily change frequency for each collection set in properties.

So what are the results of these collections?

Disk usage

To see disk usage report in SQL Server Management Studio select “Disk Usage” collection set and then right mouse click “Reports”->”Disk Usage Summary”. Here is report from my virtual PC:

This report shows you database and log size growth trending and if you select a database, you can see even more information about it:

Query Statistics

Query statistics allows you to see reports about query executions. You can choose time interval for your report and then you can see top query report by CPU, Duration, Total I/O, Physical Reads or Logical writes. Here is example of top queries by duration:

And here is example of report you will get when you will select specific query:

Server Activity

Server Activity reports lets you choose time interval that you are interested in and then shows nice activity report for that interval. This report gives you information about SQL Server and/or System CPU usage, Memory usage, Disk I/O usage, Network usage, SQL Server Waits and other activity:

You can actually click on the graph and get more information about what happened on the server at that time. I clicked on the “Disk I/O Usage” graph on the spike and got following report:

Behind the scene

It does not look like SQL Server 2008 February CTP6 has an interface to create new data collection sets. But BOL has a list of stored procedures that allows you to do just that. There is very little description on how to use these stored procedures, but I found that best way to understand how to create new data collection set is by scripting existing collection set. When you right mouse click on connection set, you have an option to “Script data collection set as”->”Create”.

Here are create scripts for each data set collection:

• Create Disk collection set
• Create Query collection set
• Create Server collection set

I was analyzing these scripts and found that there is a table msdb.dbo.syscollector_collector_types that contains a list of available collector types.  February CTP 6 was shipped with 4 collector types:

• Generic T-SQL Query Collector Type
• Generic SQL Trace Collector Type
• Query Activity Collector Type
• Performance Counters Collector Type

Each of these collectors is associated with 2 SSIS packages: collection package and upload package. I found these packages in C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Install. For example in this folder there is package TSQLQueryCollect.dtsx and package TSQLQueryUpload.dtsx. I opened and try to analyze these packages, but at this point that was just too much for me.

Why did I started to look behind the scenes? Because I wanted to make sure that it is possible to create new collection sets and add new collector types. And now I am very sure that we can. At this point I strongly believe that what we see in CTP 6 it is just a beginning. And in the not so distant future we will see Microsoft or the 3rd party releasing new collectors and collection sets. Microsoft did a good job finally setting framework for SQL Server monitoring software.

Analysis Services and MDW

SQL Server 2008 February CTP does not have any specific collectors and collection sets to collect information about Analysis Services. But it is just a matter of time before Microsoft or 3rd party will create them – there is definitely a demand for the Analysis Services monitoring software and MDW clearly allows adding new collectors.

In the near future I am planning to test:

• Can I use “Generic T-SQL Query Collector Type” to execute OpenQuery or Linked Server query to Analysis Services 2008 DMVs and get same SSAS performance data that way?
• Can I use “Generic SQL Trace Collector Type” to trace Analysis Services events?
• Can I use “Performance Counters Collector Type” to get Analysis Services related performance data? At this point I believe that I am most likely to succeed for this item.

I will post my findings in my blog.

Note: For this post I used Snagit software to do screen shots and it was so much easier than doing this my old way: capturing screen (ALT+Prt Scr), then cutting out part of the screen in the Microsoft Paint and then changing size. I am very happy that I got this software.