SQL Server Reference Guide

The SQL Server Central Management System: Collecting Performance Metrics

Last updated Jun 26, 2009.

In this tutorial I’m continuing a series on building a system that you can use to monitor and track your SQL Server systems, and in fact, any kind of system you like (The first article in this series is here). You can use the concepts you find here for lots of SQL Server applications, since in essence it uses many of the features and tools in SQL Server, specifically SQL Server 2008.

At the bottom of this article I reference a CodePlex (Microsoft’s open-source software site) where I’m creating a solution called the SQL Central Management System, or SQLCMS. If you’re interested in participating, just post a notice there that you want to join in the solution. We’ll design it together. I’m working through that project in this series of articles.

In the solution I’m building, there are three basic “components”:

1. Storage
2. Execution
3. Reporting

This tutorial covers one aspect of the execution component of the project, and in specific using this system as a central repository for your performance information.

Using the Management Data Warehouse (MDW) Feature for Performance Collection for SQL Server 2008 Instances

In a previous tutorial, I explained the new Data Collector and Management Data Warehouse feature in SQL Server 2008. The Data Collector is the part that is configured on each of the systems you want to monitor (which is why it is only available for SQL Server 2008) and it uses a series of SQL Server Agent Jobs and SQL Server Integration Services Packages to collect and store data about the performance of the system. It stores this locally in a file on each system, and then at a defined interval it loads that data up into the Management Data Warehouse, which I’m storing on my SQLCMS server.

The process for this, then, is quite straightforward. You simply set up the MDW database (using the Wizards explained in that previous tutorial) and specify the SQLCMS server as the storage unit. You then run the Wizards again on each SQL Server 2008 Instance (with the exception of SQL Server Express Edition, which doesn't have the Agent or SSIS installed) and point those to the SQLCMS server. The only caveat is that you should open each collection_set*_upload SQL Agent Job on these systems and stagger them by a few minutes or so. That way you won’t have everyone uploading their data to the MDW at the same time, and you’ll have a better performing SQLCMS.

You can also opt to collect data from the SQLCMS server as well, and that’s often a good idea. I normally only enable the disk collections, since that’s all I really care about.

I’ve presented various queries to deal with the MDW database in other places, but I’ll consolidate them here with comments to show you what they do. This example will show you the base tables and also a simple example of joining them to get database sizes and so on.

/* 
Title: MDW Queries.sql
Purpose: Holds the MAPS queries if that database is used.
Version: 1.0
Modification History:
04/30/2009 - Buck Woody - Initial Script Creation
Requirements: 
1. SQL Server 2008
2. The Management Data Warehouse feature from SQL Server 2008 to be installed and run once for each instance you wish to monitor.
(Replace the MDW database name if you’re using something else)
Optional: 
*/

USE MDW;
GO

/* Base Information  - More is here: http://msdn.microsoft.com/en-us/library/bb677306.aspx */
SELECT     *
FROM  mdw.sys.schemas;
GO

/* Instances that are tracked */
SELECT     *
FROM mdw.core.source_info_internal
--WHERE instance_name = 'ERP'

/* The snapshots that are tracked against an instance */
SELECT *
FROM mdw.core.snapshots_internal
WHERE source_id = 1;
GO

/* The type of Collectors that the MDW collects */
SELECT * 
FROM  msdb.dbo.syscollector_collector_types ct 
INNER JOIN mdw.core.supported_collector_types_internal ti 
ON ct.collector_type_uid = ti.collector_type_uid;
GO

/* A particular snapshot - this one is the disk information */
SELECT    *
FROM    mdw.snapshots.disk_usage
WHERE  snapshot_id = 1

/* Database Sizes and Growths */
SELECT
    c.instance_name
  , a.dbsize / 102 AS 'DBSizeMB'
  , a.logsize
  , a.ftsize
  , a.database_name
  , a.collection_time
FROM
    MDW.snapshots.disk_usage a INNER JOIN
    MDW.core.snapshots_internal b
    ON a.snapshot_id = b.snapshot_id INNER JOIN
    MDW.core.source_info_internal c
    ON b.source_id = c.source_id
ORDER BY
    c.instance_name
  , a.database_name
  , a.collection_time ASC

You can, of course, report on all kinds of information stored in the MDW database using those base tables at the top of the queries. And you can extend the Data Collectors to get your own counters — but that’s another tutorial.

The key now is to use the links in the Instance names in the MDW to link back to the SQL Server 2008 Central Management Server feature, which I documented in this series. Using those names, you can link up the MDW tables to show the MAPS data I've explained, or any other data that you collect. Because the Instance Name is the only element that is common across all of the features I’m using, I use it as a natural Primary Key — something I rarely do in other applications. It’s not the best practice, but it’s not the end of the world, either. There will only be a relatively small (less than a few thousand) servers that you’ll probably track with this system, so there is a low risk of data corruption due to a natural Primary Key.

Using your own processes for performance collection for SQL Server Instances

Perhaps you don’t want to use the Data Collector for your SQL Server 2008 Instances, or perhaps you still have SQL Server 2005 (or even lower) versions in your organization that you want to monitor.

That’s fine — you can still leverage the MDW feature to help you out with those collections. I recommend that you create the MDW database as a storage location for your data, so that in case you want to put your SQL Server 2008 Instances under Data Collection you can. I also advocate that you turn on the standard Data Collectors for the SQLCMS server (which is a SQL Server 2008 Instance), even if you subsequently disable them. If you do that, you’ll have access to the definitions of the data that they collect.

On my system, I’ve done that very thing. Then, I drilled down in SQL Server Management Studio (SSMS) to the Management node, and then expanded the Data Collection and then System Data Collection Sets node. From there I can see the three Data Collection Sets that were created using the Wizard I followed earlier, just as I described in my previous tutorial on the Management Data Warehouse feature.

Here’s where the payoff comes in. I can see what Microsoft is doing to collect data from SQL Server 2008, and I can use that information to collect it against “lower” versions of SQL Server, in particular SQL Server 2005. It’s like accessing the best minds at Microsoft for performance tuning advice.

Here’s how I do that (by the way, reverse engineering is normally frowned upon, but I checked with the team that owns this feature at Microsoft and they said in this case it’s OK):

I selected the Server Activity Data Collection Set, and then right-clicked it. From the menu that appeared, I selected Properties.

Recall that the Data Collection Sets can be made up of three kinds of actions: SQL Queries, Performance Monitor Counters, and SQL Traces. In this case, I see in the top panel that two of these were used: SQL Queries and Performance Monitor Counters.

I simply click on those items in the top panel, and the results are shown in the bottom panel. Now it’s a matter of copying out the data in the bottom and collecting it into the SQLCMS system into the MDW or other database.

So how do I do that? There are lots of options, from using SSIS to the Performance Monitor in Windows — and that’s something I’ll explore in a future tutorial. Stay tuned!

InformIT Articles and Sample Chapters

If you’re not familiar with the Windows Performance Monitor, you’ll need some background for the next article in this series. Windows 2000 Performance Tools: Leverage Native Tools for Performance Monitoring and Tuning will get you started.

Books and eBooks

You don’t have to use any of this to have a great solution for managing and monitoring not only SQL Server but all kinds of products. Microsoft has a good solution called System Center, and System Center Operations Manager 2007 Unleashed is a great book on it. (Also available as a downloadable eBook and in Safari Books Online.)

Online Resources

The SQL Central Management System (SQLCMS) CodePlex project is located here.