SQL Server 2008 MDW Data Collectors for SQL Server 2005

UPDATED January 22: Ruggerio kindly pointed out that I had called MDW an Enterprise Edition feature in SQL Server. UCP, which uses MDW data, is an Enterprise Edition feature. However, the Data Collectors and the MDW Reports are available in Standard Edition as well. H/T to Ruggerio.

SQL Server 2008 introduced a great piece of functionality to help you monitor performance natively on your SQL Server called Management Data Warehouse or MDW. It is very easy to configure with a wizard from SSMS and comes with a prebuilt set of “data collectors” that you schedule to run against your databases to collect performance counters and stats. There are a number of very interesting reports that come out of the box and run from the SSMS right-click Reports capability. I pasted an example of one of the reports below. These reports include drill-down with interactive click-through and timeline slider capabilities. The data warehouse database that the tool creates on your instance is open and easy to understand so that you can modify, add your own reports and build your own data collectors. Very cool and out of the box with SQL Server 2008 & SQL Server 2008 R2 Enterprise Edition. Not as feature-rich as the awesome SQL Server performance tools that are sold on the market today. But it is more advanced way to monitor your performance and keep trends for historical baselines then was previously provided by Microsoft.

MDW is a SQL Server 2008 Enterprise Edition or Data Center Edition feature that relies on SQL Server’s performance data collectors, which is a feature that created the jobs, schedules and SSIS packages needed to parse, collect, upload and store your server’s performance metrics. So what I wanted to show you today was a way that you can pull in counters and stats from your SQL Server 2005 editions as well by creating a custom data collectors that will run on a schedule from your SQL Server 2008 instance that you can run as a monitoring hub. You cannot create a custom data collector from SSMS as a New object, you have to script it in T-SQL. But once you create it, you can manage it in SSMS by setting the collection schedule and data retention policy.

The easiest way to create the new data collector is by scripting one of the existing “System Data Collectors” that are provided for you out of the box and then modify that. I’m not going to go into the details here and instead will point you to an awesome posting that Greg Larson at SQLServerPerformance.com did when this feature first was introduced into SQL Server. Check it out here. It is one of the best tutorials that I have seen on the topic of SQL Server MDW custom data collectors and he did it over 4 pages of blog postings. Nice work!

Follow through that tutorial and then if you would like to collect performance from a SQL Server 2005 instance, follow the these steps:

1. Create a Linked Server to your SQL Server 2005 instance. My sample uses a SQL Server 2005 instance called “Chicago”.

2. Use the Linked Server in the system views query from Greg’s example in Section C like this:

— Begin Section C ————————————–
Declare @collection_item_id_4 int
EXEC [msdb].[dbo].[sp_syscollector_create_collection_item]
@name=N’Remote Number of Rows’,
@parameters=N'<ns:TSQLQueryCollector xmlns:ns=”DataCollectorType”><Query><Value>
SELECT OBJECT_NAME(o.object_id) ObjectName
, SCHEMA_NAME(o.schema_id) SchemaName
, SUM(p.Rows) NumOfRows
FROM Chicago.msdb.sys.partitions p
JOIN Chicago.msdb.sys.objects o ON p.object_id = o.object_id
WHERE Index_ID &lt; 2 AND o.type = ”U”
GROUP BY SCHEMA_NAME(o.schema_id), OBJECT_NAME(o.object_id)
ORDER BY SCHEMA_NAME(o.schema_id), OBJECT_NAME(o.object_id)
</Value><OutputTable>NumOfRows</OutputTable></Query><Databases UseSystemDatabases=”true” UseUserDatabases=”true” /></ns:TSQLQueryCollector>’,
@collection_item_id=@collection_item_id_4 OUTPUT,
— End Section C —————————————-

Notice that I added my Linked Server and database reference (MSDB) to the front of the sys views in the FROM clause. That’s it. But be sure to be careful with the syntax, because that SP parameter is looking for XML syntax, not general T-SQL, so things like less than (<) need to be changed to  &lt; .