SQL 2012 System Health Reporting Dashboard – Visualizing sp_server_diagnostics results.

We have introduced the System Health Session in SQL 2008 in order to capture some critical Events to make post mortem analysis much easier. However in SQL 2008, by default the system_health session was only collected to a ring buffer and not persisted to disk. SQL 2012 truly provides more of a black box recorder with the introduction of sp_server_diagnostics and the associated data it exposes.

There is a fair amount of documentation on the System Health Session. This post isn’t going to focus on System Health session itself, rather on visualizing that data. Subsequent posts could focus on the small blocks that make up this bigger picture.

More details on the System Health session can be found below:

https://msdn.microsoft.com/en-us/library/ff877955.aspx

https://blogs.msdn.com/b/psssql/archive/2012/03/08/sql-server-2012-true-black-box-recorder.aspx

https://www.sqlskills.com/blogs/joe/under-the-covers-with-sp_server_diagnostics-data-part-i/

Now this rich set of data is collected but visualizing it isn’t as easy for certain types of Extended Events collected. In particular the Extended Event GUI isn’t conducive to visualizing some of the payload columns that have XML data.

 

clip_image002

For example, if you look at the sp_server_diagnostics_component_result, the data is XML so getting trends across the whole session is difficult as is correlating it with the other XEvents.

clip_image004

In order to visualize this data, I have put together a set of reports along with some code to import the System Health Session into a database with a pre-defined schema and report off of it. We are NOT capturing any “new” data here, rather just visualizing what is already captured through the System Health Session. By default, we maintain 5 rollover files of 5 MB each for System Health session, I do recommend bumping that value up in order to cover a bit of a longer period of time. I would recommend bumping up that size to 5 files of 20MB each as indicated in the Readme which is part of the download.

clip_image006

Here is a snippet of what that Dashboard looks like with parameters to drill into a timeline as well as get am overall picture of

- CPU utilization

- Memory Resource utilization

- Blocking that occurred > 30 seconds

- Queries that were waiting > 30 seconds for some wait_types, > 15 for others

- The status of all the sp_server_diagnostics subsystems and their underlying data

- Other Ring buffer data such as security and connectivity ring buffers

 

clip_image008

 

As part of the download you will have 3 components

  •         A Readme Word document that gives a gist of the import process and walks through the procedure and such.

     

  •         A script that creates the necessary database and schema required

  •         A set of reports that helps visualize the imported data ( Currently SQL 2008 R2 RDL files)  

 

  • There are 2 basic “import” modes as documented in the Readme    

     

  •         Connect to an existing server, and it imports the System Health Session

  •         Point the stored proc to system Health XEL files and import it into relational tables.      

     

Each Report should have a short description on top as to what it entails, here are a few of them:

Waiting Queries

 

clip_image010

Connectivity Ring Buffer

 

clip_image012

 

Blocking > 30 seconds

 

clip_image014

System sp_Server_diagnostics component

 

clip_image016

 

Note: These reports only work with the System Health Session. Sp_server_diagnostics is also captured as part of the Cluster Diagnostics, however those XEL files do not have the other pieces of information and the XML structure if a bit different though these reports can be modified to include that scenario too.

 

System Health Session is “definitely” an extremely useful diagnostic collected out of the box. My hope is that these reports help enhance the experience and enable easy visualization.

 

Note:   Modified the reports on 04/15 to account for Case Sensitive Servers – Thanks to Joe Sack for catching that. Also changing download location to TechNet Script Center given skydive can’t be accessed everywhere per comments

 

TechNet Script Center: Download SQL 2012 System Health Reports 

 

Amit Banerjee who is a SQL PFE in India also has some Dashboard reports for SQL 2008 System health Session  https://www.sswug.org/articles/viewarticle.aspx?id=62732

https://www.sswug.org/articlesection/default.aspx?TargetID=64148

 

Enjoy, and Feedback is always welcome J

 

-Denzil Ribeiro – Sr. Premier Field Engineer, SQL MCM