About me

I work as SQL Server DBA / Developer and BI Consultant in Toronto, Canada.Canada Flag More...
Vidas Matelis picture


Report Portal

SQL Server 2008 Management Data Warehouse

April 23rd, 2008 by Vidas Matelis

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:

MDW in SQL Server Management Studio

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:

DMW Disk usage report

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

MDW Disk summary report - DB detail

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:

Query statistics - report1

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

Query statistics - report2

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:

MDW Server Activity report

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:

MDW - Server activity report 2

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:

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.

Posted in SQL Server 2008, SSAS 2008 - Katmai | 6 Comments »

6 Responses

  1. How to use MDW to collect Analysis Services 2008 performance counters | Vidas Matelis Analysis Services Blog Says:

    […] SQL Server 2008 Management Data Warehouse […]

  2. Varun Says:

    I just created centralized Management Data Warehouse for like 5 of our SQL Servers on one server, but the database is at like 29 GB in just three days, how can I manage the size of the database from getting it to grow so big, it seems quicke hard to manage its size. For my data collectors I have specified 7 days of retention period, would after 7 days the database gets cleaned up.

  3. Varun Says:

    Is there a way I can use MDW and deploy them as reports in my SSRS ?

  4. Bryan Says:

    I have been searching for a way to export these reports into a report server project. these local rdl files that run the data collection set are ran completely internally, but still must be able to be extracted somehow and rendered in reporting services. The point of this is to have the internal IT department have access to this information without going through the SQL servers. There is absolutely no documentation on this, and I would really appreciate some.

  5. Fahime Alizade Says:

    I enjoyed that . It was so useful.
    Thank You

  6. Newbie Says:

    Would you know where the RDL for the MDW reports are located so I can publish them in SSRS?