This is actually turning out to be a series. I had demonstrated the use of System Health session to retrieve CPU usage details earlier. In this post, I will demonstrate how to use Power Pivot and Power View with System Health Session data to fetch SQL OS scheduler health information.

Using the Transact-SQL query below, I shall fetch the SQL OS scheduler statistics from the System Health Session data in a PowerPivot table name “QueryProcessingComponent”.

SET NOCOUNT ON

-- Fetch data for only SQL Server 2012 instances
IF (SUBSTRING(CAST(SERVERPROPERTY ('ProductVersion') AS varchar(50)),1,CHARINDEX('.',CAST(SERVERPROPERTY ('ProductVersion') AS varchar(50)))-1) >= 11)
BEGIN
-- Get UTC time difference for reporting event times local to server time
DECLARE @UTCDateDiff int = DATEDIFF(mi,GETUTCDATE(),GETDATE());

-- Store XML data retrieved in temp table
SELECT TOP 1 CAST(xet.target_data AS XML) AS XMLDATA
INTO #SystemHealthSessionData
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'system_health'   AND xet.target_name = 'ring_buffer';

;WITH CTE_HealthSession (EventXML) AS   (
SELECT C.query('.') as EventXML
FROM #SystemHealthSessionData a
CROSS APPLY a.XMLDATA.nodes('/RingBufferTarget/event') as T(C) )
SELECT
DATEADD(mi,@UTCDateDiff,EventXML.value('(/event/@timestamp)[1]','datetime')) as [Event Time],
EventXML.value('(/event/data/value/queryProcessing/@maxWorkers)[1]','bigint') as [Max Workers],
EventXML.value('(/event/data/value/queryProcessing/@workersCreated)[1]','bigint') as [Workers Created],
EventXML.value('(/event/data/value/queryProcessing/@workersIdle)[1]','bigint') as [Idle Workers],
EventXML.value('(/event/data/value/queryProcessing/@pendingTasks)[1]','bigint') as [Pending Tasks],
EventXML.value('(/event/data/value/queryProcessing/@hasUnresolvableDeadlockOccurred)[1]','int') as [Unresolvable Deadlock],
EventXML.value('(/event/data/value/queryProcessing/@hasDeadlockedSchedulersOccurred)[1]','int') as [Deadlocked Schedulers]
FROM CTE_HealthSession
WHERE EventXML.value('(/event/@name)[1]', 'varchar(255)') = 'sp_server_diagnostics_component_result'
AND EventXML.value('(/event/data/text)[1]','varchar(255)') = 'QUERY_PROCESSING'
ORDER BY [Event Time];

DROP TABLE #SystemHealthSessionData

END  

Now I shall create three new columns in the Power Pivot sheet as follows:

1. Day using Day() function on the Event Time column

2. Hour using Hour() function on the Event Time column

3. Minute using the Minute() function the Event Time column

After this is done, I created two Power View sheets with the following layouts:

1. One sheet named Threads Timeline containing scatter graph with the hour column on the X-axis and the day column on the Y-axis. The play axis uses the event time data.

2. Another sheet named Worker Thread Stats with a line graph for workers created, pending tasks and idle workers using event time as the x-axis. Also there is a tabular output showing the same data with two additional columns showing the unresolvable deadlocks and deadlocked schedulers respectively.

A screenshot of one of the Power View sheets is shown below. The screenshot of the scatter graph would not do justice which is why you need to download the Excel sheet and see for yourself!

Excel file download location: http://sdrv.ms/10O0udO

Stay tuned to TroubleshootingSQL for new visualizations that I will be discussing soon.

Previous post in this series

PowerView and System Health Session–CPU health