PowerView and System Health Session–Scheduler Health


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! Smile

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

image

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 

Advertisements

14 thoughts on “PowerView and System Health Session–Scheduler Health

  1. Amit, I was trying this out and it didn’t return results on 7 of 8 of the 2012 boxes I ran this on.
    Originally I thought it was due to the HTML “a” tags in the WHERE line (looks like they’re in there erroneously). But if they’re inside the quotes I get an error, and if I remove them I get no results on most of my servers.

    Looking at the record from the system health session, I don’t see any instances of ‘sp_server_diagnostics_component_result’. Not sure why that is.
    Any idea greatly appreciated, and you might see if you can remove the “a” tags from the query.

  2. Thanks for reporting the formatting issue. I have fixed the issue. Also, do you see the sp_server_diagnostics output being captured in the event file target of the System Health Session?

  3. (apologies if this is a dupe)

    For some reason, I can’t get this to work on any (except 1) of my 2012 servers. It comes back with NULL, once I remove the errand “a” HTML tags in the query. It does come back on one, though. Is there some sort of condition in order to cause records for ‘sp_server_diagnostics_component_result’? Looking at the raw XML, I don’t see any.

  4. (crud, I did do a dupe)
    (fyi – new bug introduced: “>= 11)”)

    I’m using RTM on all of them, and you’re right – I’m only seeing the sp_server_diagnostics_component_result on one of them. No idea why that is; I thought I had all of them set up identically.

  5. Crud – there it is. All mine _aren’t_ RTM. The one that works is SP1. Is the sp_server_diagnostics_component_result new in SP1?

  6. Yup, that’s it! The wait time on SP_SERVER_DIAGNOSTICS_SLEEP keeps increasing on all my servers. Thanks. (Now to get it fixed so I can run this).

  7. Pingback: PowerView and System Health Session–SQL Memory Health | TroubleshootingSQL

  8. Pingback: PowerView and System Health Session– IO Health | TroubleshootingSQL

  9. Pingback: PowerView and System Health Session– System | TroubleshootingSQL

  10. Pingback: Another day at the SQL Bangalore UG | TroubleshootingSQL

  11. Pingback: SQLBangalore Annual Day Event | TroubleshootingSQL

It is always good to hear from you! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s