System Health Session and beyond

I had blogged extensively about System Health Session available in SQL Server 2008 and above. I had also demonstrated how the System Health Session can be used in conjunction with SQL Server Management Studio reports in previous series that I ran on my blog.

System Health Session Dashboard Reports for SQL Server 2008 and SQL Server 2008 R2 – A set of reports available for SQL Server 2008/SQL Server 2008 R2 which provide visualizations for the events tracked by the System Health Session. The reports can be used using the custom reports option in SQL Server Management Studio.

System Health Session Dashboard – Shows the basic information of the information that can be fetched from the System Health Session.

System Health Session Dashboard — sp_server_diagnostics and more – A look into the different issues tracked by the sp_server_diagnostics script and how to build visualizations using that information.

System Health Session Dashboard – sp_server_diagnostics – The basics of what is covered by the sp_server_diagnostics procedure and how to build visualizations on top of the data captured.

System Health Session Dashboard – Error Summary Report
– Shows information on how to write queries to fetch information about various errors tracked by the System Health Session. Shows information tracked by the enhanced System Health Session in SQL Server 2012.

System Health Session Dashboard – Health Summary Report
– Gives you information about the SQL Server instance health like CPU usage, memory etc. A screenshot of the report is shown below:

I have added UTC time formatting so that the time is shown in the same time zone based on the server on which you are viewing the report.

I will keep updating this post once I keep updating with the new reports. As usual, feedback is always welcome.

The report definitions (.rdl) can be downloaded from here.

System Health Session and Deadlocks

I had blogged about retrieving deadlock related information using the default Extended Event session which runs by default on all SQL Server 2008 instances and above. However, once you have retrieved the XML deadlock graph, it could be quite cumbersome to read if the deadlock happens to be complex or involves multiple nodes. I frequently require the need to fetching the information about past deadlocks from the System Health Session data while working on customer environments. Due to the frequent repetitive nature of the data collection, I decided to automate this task.

I again decided to use a combination of Powershell and T-SQL to extract this information. The Powershell script (TransformtoXDL.ps1), which requires Powershell 2.0 or above, uses a T-SQL script (TransformtoXDL.sql ) to extract data from the System Health Session and outputs each individual deadlock graph as a separate .XDL file into a folder of your choice with the timestamp of the occurrence of the deadlock. Note that the time reported will be in GMT timezone.

The powershell script accepts two parameters: vServername for the SQL Server instance that you want to extract the data from and the vPath for the folder into which the XDL files should be saved into.

.\TransformToXDL.ps1 -vServername "<server name>" -vPath "C:\Tempdb\"

Yes… I have a folder called Tempdb on my C: drive!! Smile

A sample output is shown in the screenshot below:

image

The Transact-SQL script called TransformtoXDL.sql does the following:

1. Extracts the System Health Session data into a temporary table
2. Based on the version of your SQL Server instance, it performs the parsing to extract the deadlock graph. This script accounts for issues mentioned in KB978629. I would like to thank Michael Zilberstein [B] for the proposed corrective action on an issue that Jonathan Kehayias [B|T] had blogged about.
3. The last action that the script takes is to perform XML modification to get the XML deadlock data in the same format which is recognized by SQL Server 2012 Management Studio when viewing XDL files.

The powershell and T-SQL script can be downloaded here.


#    Script Name: TransformToXDL
#    Author: Amit Banerjee
#    Date: September 6, 2012
#    Description:
#    The script reads the deadlock graphs from the System Health Session
#    Ring Buffer and parses them to create an individual deadlock graph
#    in a folder of your choice.
#    Usage: .\TransformToXDL.ps1 -vServername "INST1" -vPath "C:\Tempdb\"
# This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.

Param ([string] $vServername,
[string] $vPath)

cls

# Load the SQL Server snap-in for using sqlcmd cmdlet
$ErrorActionPreference = "SilentlyContinue"
Import-Module sqlps
$ErrorActionPreference = "Continue"

Write-Host "`nConnecting to SQL Server instance " $vServerName " to extract deadlock information"
# Extract the deadlock graphs and parse them in the system health session
Invoke-Sqlcmd -InputFile "C:\Tempdb\TransformToXDL.sql" -ServerInstance $vServerName

# Function to get the information from the table stored in tempdb
function Get-SqlData
{
param(
[string]$serverName=$(throw 'serverName is required.'),
[string]$databaseName,
[string]$query
)

$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"
$da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString)
$dt = New-Object "System.Data.DataTable"
[void]$da.fill($dt)
$dt

}

# Get the data stored in tempdb using the function defined above
$rows = get-sqldata $vServername  "tempdb"  "select row_id,event_time,deadlockgraph from tempdb.dbo.deadlock_graphs"

$vCount = 0
# Extract each row retrieved into an individual XDL file with the timestamp of the issue
foreach ($row in $rows)
{
if($row -ne $null)
{
$vCount++
$vFileName = $vPath + $vServername.Replace("\","_")+ "_" + $row.event_time.ToString().Replace(":","_").Replace("/","_") + ".xdl"
Write-Host "`nCreating file: "  $vFileName
$row.deadlockgraph | Out-File $vFileName
}
}

Write-Host "`nDeadlocks found: " $vCount.ToString()
Write-Host "`nPerforming cleanup"
Invoke-Sqlcmd -Query "IF EXISTS (SELECT TOP 1 name FROM tempdb.sys.objects where name = 'deadlock_graphs')
BEGIN
DROP TABLE tempdb.dbo.deadlock_graphs
END" -ServerInstance $vServerName

The above has been tested on SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012. The resulting XDL files can be opened in SQL Server 2012 Management Studio. I am always looking for feedback. So please feel free to Tweet, Facebook or Email me regarding any issues or enhancements that you might need for the same.

You might want to remember that the T-SQL query used is a resource intensive query and it is preferable that you run this extraction exercise during non-business hours especially if your SQL Server instance is experiencing a large number of deadlocks.

Awesomesauce SQL Server 2012: System Health Session

There have been a spate of new features and enhancements to existing functionality in SQL Server 2012. The System Health Extended Events session is one of the benefactors. I had previously blogged about the usage of the System Health Session data using custom SSMS Reports in a four part series. Now it is time to talk about the enhancements to the System Health Session’s predecessor.

The SQL Server 2012 health session adds additional events to the default extended event health session running on the instance which is listed below to collect data for the following:

  • Out of memory errors for CLR and the database engine using the events:
    • sqlclr.clr_allocation_failure
    • sqlclr.clr_virtual_alloc_failure
    • sqlos.memory_broker_ring_buffer_recorded
    • sqlos.memory_node_oom_ring_buffer_recorded
  • Scheduler health issues using the events:
    • sqlos.scheduler_monitor_deadlock_ring_buffer_recorded
    • sqlos.scheduler_monitor_non_yielding_iocp_ring_buffer_recorded
    • sqlos.scheduler_monitor_non_yielding_ring_buffer_recorded
    • sqlos.scheduler_monitor_non_yielding_rm_ring_buffer_recorded
    • sqlos.scheduler_monitor_stalled_dispatcher_ring_buffer_recorded
    • sqlos.scheduler_monitor_system_health_ring_buffer_recorded
  • The results of the sp_server_diagnostics component
  • Security and connectivity errors encountered using the events:
    • sqlserver.connectivity_ring_buffer_recorded
    • sqlserver.security_error_ring_buffer_recorded

imageAdditional wait types are also tracked which adds to the existing list of waits captured by this session. Since SQL Server 2012 Management Studio allows you to view the configured Extended Events session on the SQL Server 2012 instance, you can get to the system_health session using the following steps from Object Explorer (see Screenshot 1):

Expand Management –> Extended Events –> system_health.

The Script Session as option now provides you with a way to view the session definition. Another enhancement made to this session is the target for this session is now also a .xel file which by default is located in your SQL Server LOG folder. The target definition for the System Health session are now:

a. Ring Buffer (as was in SQL Server 2008) 
b. XEL file – With a maximum size of 5 MB and a file retention policy of 4 files.

So now you can leverage the management studio XEvent UI to view the results. You can right-click on the ring buffer target in the UI and click on the View Target Data option which will give you an XML document. image

The coolest thing that I like here is the viewer’s capability to show you all the data from all the .xel files. Once you have the events loaded, you should see a view as shown in Screenshot 2. This gives you a list of all the events that were generated. You can sort on the event columns by clicking on them. The progress of the sorting will be visible towards the top right hand corner of SSMS as shown in Screenshot 3. Now that you have the basic view, let’s find out what more can we do with the System Health Session data displayed.

image

You can click on the Choose Columns option in the toolbar, select the columns that you want to view additionally apart from the name and timestamp by using the Available Columns list. See Screenshot 4 for an example.

image

Now that you have selected the additional columns, you can also set grouping (using the Grouping button) to view each of the event name categories, the number of events reported and aggregations on the column values (using the Aggregations button). So finally, I landed up with a view as shown below in Screenshot 5. What’s more, you can save the view settings using the Display Settings button!

image

I am currently writing a series on SSWUG for designing SSRS reports for viewing the events reported by the System Health Session. You can following the series here. If you have SQL Server 2012 RTM installed, then you need to download the T-SQL script attached to Mike Wachal’s blog post to address the wait type information captured.

Monitoring just got a whole lot easier!!