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: http://en.search.wordpress.com/?q=mdw&site=mssqldude.wordpress.com.

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">
        <Query>
          <Value>SELECT * FROM UserDB.sys.dm_cdc_log_scan_sessions</Value>
          <OutputTable>cdc_log_scan_data</OutputTable>
        </Query>
      </ns:TSQLQueryCollector>');

Enjoy! Best, Mark

SQL Server Alerts for CPU Utilization Alert

It isn’t immediately obvious when looking at SQL Server’s built-in alerting mechanism how you can set-up alerts and automation for objects outside of the SQL Server service. But SQL Server agent alerts does expose the WMI interface that you can use to query for events. I don’t personally find it to be a very user-friendly mechanism and it seems like you actually need to query for events in WMI as opposed to just straight interrogation of the Windows objects themselves. But, nonetheless, since WMI is a well-known interface into Windows, you can use WMI to capture alerts that are occuring outside of SQL Server to do things like, say, change thresholds on runaway queries by altering Resource Governor settings when a threshold is breached on CPU utilization.

Something like that would need to look at the Processor object events in WMI. The way that I have created these for SQL Server alerts is by using PowerShell first to test out the queries. With the Get-WmiObject scriplet in PowerShell, it is easy to test things out in PowerShell first.

Then you can build out a SQL Agent alert like this:

USE [msdb]
GO

EXEC msdb.dbo.sp_add_alert @name=N’WMITest’,
  @message_id=0,
  @severity=0,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @category_name=N'[Uncategorized]’,
  @wmi_namespace=N’\\.\ROOT\CIMV2′,
  @wmi_query=N’SELECT * FROM __InstanceModificationEvent WITHIN 600 WHERE TargetInstance ISA “Win32_Processor” AND TargetInstance.LoadPercentage > 10′,
  @job_id=N’00000000-0000-0000-0000-000000000000′
GO