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 

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 

SQL Bangalore UG Meeting–July 13

sponsor-bangaloreUGEven though the 13th is considered unlucky by a lot of individuals, this 13th happened to be a good one! The SQL Bangalore UG meeting had another successful event today!

It always surprises me with the number of SQL community folks that turn up to attend the sessions on a Saturday morning! Thank you to all the attendees! Without you this event would not have been possible.

There were some fun filled, technical and interactive sessions delivered by Olga Medvedeva [T] on Pagination Methods in SQL Server, Vinod Kumar [B|T] on SQL Server 2014 CTP1: In-memory OLTP and Sourabh Agarwal [B|T] on SQL Server Memory Basics.

I had the privilege of presenting on SQL Server Scheduling Basics at this user group meeting. The slide deck used for this session is available on Slide Share and the embedded copy of the PPT is available below.

As usual it was good fun to meet the SQL Server community members. In case you have any questions regarding my session, please send a tweet to @banerjeeamit or post your question on the TroubleshootingSQL Facebook page or on the SQLBangalore Facebook group.

PowerView and System Health Session–CPU health

Over the past few months, I spent a lot of time using Excel 2013 and the Power View add-in for completing various kinds of analysis on System Health Session data for SQL Server 2012. I thought it would be a good idea to create an Excel sheet with a template which would allow me to do these activities at the click of a button. I had recently written a post on how to retrieve Report Server execution statistics using Power Pivot and Power View. I will use the same philosophy to generate the CPU usage statistics using the System Health Session.

Now, you may ask why not query the data directly into an Excel sheet. The reason I do not do this is because it allows me to add calculated fields. Additionally, it allows me to write a query to fetch the data from the System Health Session that runs on SQL Server 2012 and above instances.

I had written an article on SSWUG on how to parse the scheduler_monitor_system_health_ring_buffer_recorded data and retrieve the CPU usage statistics using a SSRS report. This time around I fetched the data into a PowerPivot table. Then I added a new column for retrieving the day of the month using the DAY function. I also created a new calculated column for tracking the CPU usage of other processes using the SQL process utilization and system idle values. The new column is named “CPU usage (other processes)”.

Now that the PowerPivot table is ready, I used the created table as the source for a PowerView sheet in Excel 2013.

I created clustered column chart to track the following information:

  • Max CPU utilization for the SQL Server instance
  • Max CPU usage of all other processes
  • Average CPU utilization for the SQL Server instance
  • Average CPU usage of all other processes
  • Minimum CPU utilization for the SQL Server instance
  • Minimum CPU usage of all other processes

The clustered column chart uses the calculated Day field to make the chart more reader friendly. The second chart in the sheet is a line graph which tracks the average CPU usage for the SQL Server instance and other processes across a timeline. The slider at the bottom of the chart allows you to zoom in and out interactively of a time window that you are interested in.

Here is a screenshot of the final output as shown in screenshot 1. The Excel file with this visualization is available on SkyDrive at http://sdrv.ms/10O0udO

CPU usage statistics

Stay tuned to TroubleshootingSQL.com for more such visualizations!