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)','datetime')) as [Event Time], EventXML.value('(/event/data/value/queryProcessing/@maxWorkers)','bigint') as [Max Workers], EventXML.value('(/event/data/value/queryProcessing/@workersCreated)','bigint') as [Workers Created], EventXML.value('(/event/data/value/queryProcessing/@workersIdle)','bigint') as [Idle Workers], EventXML.value('(/event/data/value/queryProcessing/@pendingTasks)','bigint') as [Pending Tasks], EventXML.value('(/event/data/value/queryProcessing/@hasUnresolvableDeadlockOccurred)','int') as [Unresolvable Deadlock], EventXML.value('(/event/data/value/queryProcessing/@hasDeadlockedSchedulersOccurred)','int') as [Deadlocked Schedulers] FROM CTE_HealthSession WHERE EventXML.value('(/event/@name)', 'varchar(255)') = 'sp_server_diagnostics_component_result' AND EventXML.value('(/event/data/text)','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
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.
What was the highest build of SQL Server 2012 that you tried this on?
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?
(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.
(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.
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?
Maybe you are running into this issue: http://support.microsoft.com/kb/2800256 which stops the sp_server_diagnostics collection. I believe the instances in question are stand-alone instances.
Fixed the >=11 formatting issue. Sorry about that!
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).
Pingback: PowerView and System Health Session–SQL Memory Health | TroubleshootingSQL
Pingback: PowerView and System Health Session– IO Health | TroubleshootingSQL
Pingback: PowerView and System Health Session– System | TroubleshootingSQL
Pingback: Another day at the SQL Bangalore UG | TroubleshootingSQL
Pingback: SQLBangalore Annual Day Event | TroubleshootingSQL
Pingback: Event Session을 이용한 SQL OS Session Schedule check – SQLANGELES