Previous posts in this series:
In the last post for this series, I had explained how to retrieve the I/O statistics from the System Health Session data. In this post, I will describe how to build a dashboard using the SYSTEM component of the sp_server_diagnostics output. This view will help DBAs track various errors which can get their blood pressure shooting to abnormal levels. The SYSTEM component tracks various errors like non-yielding conditions, latch related warnings, inconsistent pages detected and access violations for the SQL Server instance.
Armed with this information in a Power Pivot table, I created two calculated columns for DAY and HOUR on the time the event was reported. After that I created KPIs on the maximum number of non-yielding conditions, latch related warnings, inconsistent pages and access violations reported.
Now that I have my Power Pivot data, I created a new Power View sheet which tracks the created KPIs for each day and hour. The screenshot below shows the final view.
The first half is a 100% Stacked Bar graph showing the various errors that were reported each day. There is a slicer for Day available which allows to filter the data quickly.
The second half of the report is a matrix which shows the KPI status for which day with a drill-down capability for hour.
The third half of the report shows a card view with the actual number of issues reported for each event against a particular time.
As usual the Excel sheet is available on SkyDrive at: http://sdrv.ms/10O0udO
The query to fetch the data required to build this report is available below.
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'; -- Parse XML data and provide required values in the form of a table ;WITH CTE_HealthSession (EventXML) AS ( SELECT C.query('.') 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/text)','varchar(255)') as Component, EventXML.value('(/event/data/value/system/@latchWarnings)','bigint') as [Latch Warnings], EventXML.value('(/event/data/value/system/@isAccessViolationOccurred)','bigint') as [Access Violations], EventXML.value('(/event/data/value/system/@nonYieldingTasksReported)','bigint') as [Non Yields Reported], EventXML.value('(/event/data/value/system/@BadPagesDetected)','bigint') as [Bad Pages Detected], EventXML.value('(/event/data/value/system/@BadPagesFixed)','bigint') as [Bad Pages Fixed] FROM CTE_HealthSession WHERE EventXML.value('(/event/@name)', 'varchar(255)') = 'sp_server_diagnostics_component_result' AND EventXML.value('(/event/data/text)','varchar(255)') = 'SYSTEM' ORDER BY [Event Time]; DROP TABLE #SystemHealthSessionData END