PowerView and System Health Session– IO Health


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

IO Statistics

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 
Advertisements

PowerView and System Health Session–SQL Memory Health


SQL Server Memory has been always a topic of discussion at most client locations that I visit. So, I thought I will dedicate the third post of my Power View and System Health Session series to Memory usage. SQL Server 2012 instances tracks some very useful information with the help of the sp_server_diagnostics output. I will be concentrating my efforts on the RESOURCE component’s output.

So get ready to have a plethora of information about your SQL Server instance’s memory usage available in a single Power View sheet in Excel 2013!

Continue reading

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