Monitor SQL Server CDC with Customer Data Collector

I’ve posted several times over the years here @MSSQLDUDE on the benefits of SQL Server Management Data Warehouse (MDW) for baselining your SQL Server performance and sending those measurements to a central server for monitoring. MDW makes use of the SQL Server Data Collector feature and is a feature you get out of the box. Here are some of my writings on MDW here @MSSQLDUDE:

There is a debate among SQL Server DBAs about the worthiness of MDW and Data Collectors because they can appear to be a “heavy burden” on your server and provide overhead that you may not wish to occur on your production server. That being said, the way things work in our world and jobs in databases is a give and take with performance, monitoring, scalability and providing functionality to our business users & customers to allow them to do their jobs. Any monitoring and baselining product incur a small performance cost to your servers. There are a number of options out there in the market and many DBAs write their own scripts to store DMV data into tables and files to provide similar functionality.

What I wanted to point you all to is something that I think proves the value of Data Collectors (and MDW) through extending the functionality with custom data collectors.

I’ve been working on a CDC project recently in a large BI/DW scenario and one thing that you notice when working on CDC instead of longer-run SQL Server features like Replication, is that there is not much support in SSMS or other built-in SQL Server tools for CDC.

But what is really nice is this custom data collector for CDC that is included on MSDN from this terrific article onMonitoring the Change Data Capture Process. Scroll down to the Data Collector portion and the code to paste into SSMS is right there for you to create and start your data collector. This is a great way to monitor your CDC log processes.

BTW, you have to make an update to the XML in code from MSDN … Look for the SELECT @parameters code and replace the entire SELECT … XML statement with this:

SELECT @paramters = CONVERT(xml, 
    N'<ns:TSQLQueryCollector xmlns:ns="DataCollectorType">
          <Value>SELECT * FROM UserDB.sys.dm_cdc_log_scan_sessions</Value>

Enjoy! Best, Mark

SQL Server 2008 R2 Performance Monitoring

In case you are not aware, there are several tools that SQL Server 2008 R2 provides for you as a DBA to use that allow you to monitor performance on your databases. I’ve been talking a lot about these to customers recently and will touch on this next week at the South Jersey SQL Server User group:

Some of these tools are just there and you may not ever open them or you may be on a previous version of SQL Server.

1. Management Data Warehouse

This one has gone through a few revisions and is an excellent part of the SQL Server 2008 R2 toolset that you can configure to run on your SQL Server simply by right-click and selecting “Configure Management Data Warehouse” on the Data Collector sets in your instance “Management” folder. The data collectors are configurable and will collect stats, typically at 15 minute intervals, across your instance databases for queries, server stats and disk stats. The MDW will also manage the archival of data history such that you can configure how much history to maintain online. Since this is a “data warehouse” and is based on data collectors that activate at set intervals, or on requested execution, the MDW is very good at providing history and baselines. In addition, the reports are all based on SSRS and include a rich set of reports out of the box that include drill-down to query text and query plans. Since these are SQL Server Reporting Services based performance reports for SQL Server, you can create your own MDW reports in SSRS as custom reports.

2. Utility Control Point

UCP is brand new in SQL Server 2008 R2 and with a single Data Center edition of the database, you can stand-up a monitoring server using just SQL Server and stay within SQL Server Management Studio. There are interactive dashboards that allow you to monitor SQL Server 2008 R2 databases using the data collectors mentioned above in MDW also within SSMS, making both of these closely related to each other and very easy for a SQL Server DBA because you can stay within your Management Studio environment.

In both the case of MDW reports and UCP, data is collected and pushed into a data warehouse and you should be cognizant of a possible hit to performance as well as data storage with all of these counters that are collected, processed and stored. UCP is a good candidate to stand alone in your environment and manage the servers centrally here.

3. Activity Monitor

Another tool that has gone through many SQL Server revisions, the SQL Server 2008 R2 version of Activity Monitor. Like MDW, you can get performance stats wih query details in the activity monitor. But unlike data collector driven tools like MDW & UCP, Activity Monitor produces real-time results in areas such as waits, processes and expensive queries. And unlike a data warehouse, the data is not persisted. The monitoring begins when you open Activity Monitor and ends when you close it.

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 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; .