2015-05-29

Data Collection: Making Wait Statistics Useful

I've become quite a fan of the SQL Server Data Collector over the last year. In particular, the Server Activity collection set is what really impressed me. But there is one part I found lacking: the Server Activity – DMV Snapshots collection item returns wait statistics that are useless out of the box. There are too many "white noise" wait types included and it's difficult to make any meaningful conclusions when you see the reports. Here's an example:

SQL Server Data Collector Wait Stats - Before

Almost everything falls under the "Other" Wait Category, which in this case consists of SQLTRACE_WAIT_ENTRIES (64.01%) and BROKER_TASK_STOP (35.05%) wait types. These are two of the many wait types that we can usually ignore. There's a great article on waits and queues by Paul Randal that includes a list of benign wait types. Using Paul's list, I decided to filter out the unwanted wait types.

At first, I considered tackling the issue from the perspective of the MDW. This was appealing because I'd only have to "fix it" in one place. Perhaps I could periodically delete the unwanted data or prevent it from getting inserted via an INSTEAD OF TRIGGER. After looking at the related system tables and giving it some more thought, I decided against this. Then I considered the issue from the perspective of the data collectors. In SSMS, I could view the properties of the "Server Activity" collection set:

SQL Server Data Collector Collection Set Properties

The Input Parameters text field has several queries, including one for os_wait_stats. Perfect! I'll just edit the wait stats query and click OK... Ugh! The text field isn't editable--I'd have to find another way. After some research, I'd discover those queries are stored hierarchically with other data in an XML column:

SELECT ci.collection_item_id, ci.name, ci.frequency, ci.[parameters]
FROM msdb.dbo.syscollector_collection_items ci
JOIN msdb.dbo.syscollector_collection_sets cs
 ON ci.collection_set_id = cs.collection_set_id
WHERE ci.name = 'Server Activity - DMV Snapshots'
AND cs.name = 'Server Activity'

I'd also discover that I could update the parameters via stored proc msdb.dbo.sp_syscollector_update_collection_item. This meant, of course, that I'd have to battle my old nemesis XML. I eventually put together this script * Special thanks to Robert Sheldon, whose article helped me navigate unfamiliar waters., which I deployed to multiple SQL instances:

DECLARE @ColItemId INT
DECLARE @DmvShapshotsParams XML

SELECT @ColItemId = ci.collection_item_id, 
 @DmvShapshotsParams = ci.[parameters]
FROM msdb.dbo.syscollector_collection_items ci
JOIN msdb.dbo.syscollector_collection_sets cs
 ON ci.collection_set_id = cs.collection_set_id
WHERE ci.name = 'Server Activity - DMV Snapshots'
AND cs.name = 'Server Activity'

IF @DmvShapshotsParams.value('declare namespace ns="DataCollectorType"; 
 (/ns:TSQLQueryCollector/Query[OutputTable="os_wait_stats"]/Value)[1]','nvarchar(max)') LIKE '%HAVING%'
BEGIN
 PRINT 'Already modified!  Do not proceed!';
 RETURN;
END
ELSE
BEGIN
 PRINT 'Continuing with modify()';

 --The entire chunk of XML data before any modification.
 SELECT @DmvShapshotsParams AS [XML Params Before]

 --The specific node that we will modify..
 SELECT @DmvShapshotsParams.value('declare namespace ns="DataCollectorType"; 
  (/ns:TSQLQueryCollector/Query[OutputTable="os_wait_stats"]/Value)[1]','nvarchar(max)')

 DECLARE @NewNodeVal VARCHAR(MAX) = @DmvShapshotsParams.value('declare namespace ns="DataCollectorType"; 
  (/ns:TSQLQueryCollector/Query[OutputTable="os_wait_stats"]/Value)[1]','nvarchar(max)') + '
HAVING [wait_type] NOT IN (
  N''BROKER_EVENTHANDLER'',             N''BROKER_RECEIVE_WAITFOR'',
  N''BROKER_TASK_STOP'',                N''BROKER_TO_FLUSH'',
  N''BROKER_TRANSMITTER'',              N''CHECKPOINT_QUEUE'',
  N''CHKPT'',                           N''CLR_AUTO_EVENT'',
  N''CLR_MANUAL_EVENT'',                N''CLR_SEMAPHORE'',
  N''DBMIRROR_DBM_EVENT'',              N''DBMIRROR_EVENTS_QUEUE'',
  N''DBMIRROR_WORKER_QUEUE'',           N''DBMIRRORING_CMD'',
  N''DIRTY_PAGE_POLL'',                 N''DISPATCHER_QUEUE_SEMAPHORE'',
  N''EXECSYNC'',                        N''FSAGENT'',
  N''FT_IFTS_SCHEDULER_IDLE_WAIT'',     N''FT_IFTSHC_MUTEX'',
  N''HADR_CLUSAPI_CALL'',               N''HADR_FILESTREAM_IOMGR_IOCOMPLETION'',
  N''HADR_LOGCAPTURE_WAIT'',            N''HADR_NOTIFICATION_DEQUEUE'',
  N''HADR_TIMER_TASK'',                 N''HADR_WORK_QUEUE'',
  N''KSOURCE_WAKEUP'',                  N''LAZYWRITER_SLEEP'',
  N''LOGMGR_QUEUE'',                    N''ONDEMAND_TASK_QUEUE'',
  N''PWAIT_ALL_COMPONENTS_INITIALIZED'',
  N''QDS_PERSIST_TASK_MAIN_LOOP_SLEEP'',
  N''QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'',
  N''REQUEST_FOR_DEADLOCK_SEARCH'',     N''RESOURCE_QUEUE'',
  N''SERVER_IDLE_CHECK'',               N''SLEEP_BPOOL_FLUSH'',
  N''SLEEP_DBSTARTUP'',                 N''SLEEP_DCOMSTARTUP'',
  N''SLEEP_MASTERDBREADY'',             N''SLEEP_MASTERMDREADY'',
  N''SLEEP_MASTERUPGRADED'',            N''SLEEP_MSDBSTARTUP'',
  N''SLEEP_SYSTEMTASK'',                N''SLEEP_TASK'',
  N''SLEEP_TEMPDBSTARTUP'',             N''SNI_HTTP_ACCEPT'',
  N''SP_SERVER_DIAGNOSTICS_SLEEP'',     N''SQLTRACE_BUFFER_FLUSH'',
  N''SQLTRACE_INCREMENTAL_FLUSH_SLEEP'',
  N''SQLTRACE_WAIT_ENTRIES'',           N''WAIT_FOR_RESULTS'',
  N''WAITFOR'',                         N''WAITFOR_TASKSHUTDOWN'',
  N''WAIT_XTP_HOST_WAIT'',              N''WAIT_XTP_OFFLINE_CKPT_NEW_LOG'',
  N''WAIT_XTP_CKPT_CLOSE'',             N''XE_DISPATCHER_JOIN'',
  N''XE_DISPATCHER_WAIT'',              N''XE_TIMER_EVENT'')
 '

 SET @DmvShapshotsParams.modify('declare namespace ns="DataCollectorType"; 
  replace value of (/ns:TSQLQueryCollector/Query[OutputTable="os_wait_stats"]/Value/text())[1]
  with sql:variable("@NewNodeVal")');

  --The entire chunk of XML data after modify().
 SELECT @DmvShapshotsParams AS [XML Params After]

 /*
  Take a moment to compare the before/after values
  for the xml parameters.  If everything looks ok,
  uncomment the SP below and run again.
 */
 -- msdb.dbo.sp_syscollector_update_collection_item
 -- @collection_item_id = @ColItemId,
 -- @parameters = @DmvShapshotsParams
END

Afterwards, I stopped and started the "Server Activity" data collection set. I also restarted the SQL Server Agent, which restarted related jobs for data collection. After a few hours of collection, the wait stats are so much more meaningful. Wouldn't you agree?

SQL Server Data Collector Wait Stats - After

SHARE