Previous posts in this series:
PowerView and System Health Session–CPU health
PowerView and System Health Session–Scheduler Health
PowerView and System Health Session–SQL Memory Health
The SQL Server support team does get a lot of calls regarding slow performance which on analysis leads to a slow performing disk sub-system. The IO_SUBSYSTEM component of the sp_server_diagnostics output in SQL Server 2012 tracks I/O related latch timeouts and long duration I/Os reported along with the filename and the longest pending I/O duration. This information can be very useful when looking at the trends of slow I/O reported on the SQL Server database files on an instance.
As shown earlier in the series, I used this data captured by the sp_server_diagnostics output present in the System Health Session ring buffers to build visualizations using Power Pivot and Power View in Excel 2013. The query available at the bottom of this blog post allowed me to fetch the information from the System Health Session ring buffer into a Power Pivot table.
After that I created a two calculated fields for Hour and Day using the Event Time field in the table. Then, I created two calculated fields for tracking the maximum number of Long IOs and IO Latch Timeouts reported. Then I assigned KPIs to each of these calculated fields. After that I got down to designing the Powershell sheet which finally looked like the image in the screenshot!
The slider enables you to see the KPI status for each day on an hourly basis and the table on the right gives you insights into every snapshot captured by the sp_server_diagnostics output for the hour that you are interested in.
As usual the Excel sheet is available on SkyDrive at: http://sdrv.ms/10O0udO
Query to fetch the above data 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)[1]','datetime')) as [Event Time],
EventXML.value('(/event/data/text)[1]','varchar(255)') as Component,
EventXML.value('(/event/data/value/ioSubsystem/@ioLatchTimeouts)[1]','bigint') as [IO Latch Timeouts],
EventXML.value('(/event/data/value/ioSubsystem/@totalLongIos)[1]','bigint') as [Total Long IOs],
EventXML.value('(/event/data/value/ioSubsystem/longestPendingRequests/pendingRequest/@filePath)[1]','varchar(8000)') as [Longest Pending Request File],
EventXML.value('(/event/data/value/ioSubsystem/longestPendingRequests/pendingRequest/@duration)[1]','bigint') as [Longest Pending IO Duration]
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)') = 'IO_SUBSYSTEM'
ORDER BY [Event Time];
DROP TABLE #SystemHealthSessionData
END
TroubleshootingSQL 