SQL 2012 System Health Reporting Dashboard – Visualizing sp_server_diagnostics results.

  • Article

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

Comments

  • Anonymous
    April 15, 2013
      SQL Server 2008 and above provide an amazing diagnostics capability in the form of the System

  • Anonymous
    April 15, 2013
    very nice. Excellent use of the system health session data Thanks for sharing

  • Anonymous
    April 16, 2013
    Hi, could you make theses reports available on some other area plaes. Skydrive is blocked in many places.... Br, Staffan

  • Anonymous
    April 16, 2013
    Thanks Andreas, glad you liked it . Staffan, let me explore what I can do other than Skydrive and get back to you.

  • Anonymous
    April 18, 2013
    Hey y’all, Mark here with some quick links for you. We have two other PFE team blogs that have

  • Anonymous
    April 29, 2013
    Updated Download location to TechNet Script center, and fixed a few reports to work with Case sensitive servers.

  • Anonymous
    April 29, 2013
    The comment has been removed

  • Anonymous
    April 29, 2013
    Sorry about that, when moving the scripts from Skydrive to Technet Script Center, attached the wrong version-- the first 2 are warnings, should still run fine as it has to do with object dependency, the error should be fixed.

  • Anonymous
    April 30, 2013
    The comment has been removed

  • Anonymous
    May 01, 2013
    Charles, Running the proc re-imports all the data. This is more for post mortem analysis with the default data collection rather than an ongoing Perf collection given waitstats aren't a big part of it. It can be converted tmore of a permanent data store but would require some work to do that. What is imported is the System health Session .XEL files that reside in your log directory, truncating the existing tables and reloading them.

  • Anonymous
    May 12, 2013
    Are these sp going to be released in the next release ? --exec SpLoadQueryProcessingComponent_HighCPU @UTDDateDiff --exec SpLoadQueryProcessingComponent_QueryWaits @UTDDateDiff

  • Anonymous
    June 13, 2013
    The comment has been removed

  • Anonymous
    June 13, 2013
    Are you running this on the Report Server itself? If not it maybe a doublehop issue where you don't have Kerberos enabled and if that is the case store credentials in the Data source. If you give me your email I can contact if you offline if you need further help.

  • Anonymous
    June 15, 2013
    Yup, you're right, it's something to do with security - even after I change the data source for the Dashboard report to require I enter credentials, it still throws out the 'NT AUTHORITYANONYMOUS LOGON'. I'll try changing all of them.

  • Anonymous
    October 12, 2013
    Are the StartTime and End Time in UTC or Local time Zone of SQL Server .? Can we deploy the reports to one central SQL server , and create the  database XEvents_ImportSystemHealth  on multiple servers , and use reports from central server

  • Anonymous
    November 24, 2013
    The comment has been removed

  • Anonymous
    November 24, 2013
    Dennis, Have you created the Database with the Schema and are you pointing the reports to that database? It appears that you have not as it is missing several tables and columns. The reports can be modified to have a control table to check if the schema is created or not, am just not doing it at this time. I can contact you offline if you leave your email address. Kumar, Yes you can deploy reports to a central server  as the dashboard report onwards has a dynamic dataset that you can point to any server that has the Database schema and run those reports. AS far as the times go the import process captures both in the tables, the reports however generally show local server timestamp.

  • Anonymous
    November 25, 2013

  1. install SQL 2012 DB engine, Report server and Data Tools
  2. ALTER EVENT SESSION
  3. run CreateSystemhealthDBAndSchema.sql
  4. Exec spLoadSystemHealthSession
  5. Configure Report Server
  6. Configure RS-TargetServer and Deploy
  7. Connect to http://tccqas2/ReportServer_SSRS -> TopWaits and Blocking reports are ok. From dennis.lee@quantatw.com

  • Anonymous
    December 19, 2013
    Does anyone has the script for sql server 2008 from gissah@live.com

  • Anonymous
    February 14, 2014
    Denzil, howdy again.  I've got part of it running, but am trying to figure out why each page has the whole "SQLServerName" and DatabaseName.  Is there any reason I couldn't just change them all to use a shared datasource, and point that at the server/database?  I also see multiple datasources, but reading the docs it looks like it should always go to the XEvents_ImportSystemHealth database.   Also, on TopWaits, even with a successful import that CaptureTime dropdown is blank,and it requires an actual CaptureDate.  Thanks.

  • Anonymous
    February 14, 2014
    offline email is my last name (as shown above), at gmail. Thanks.

  • Anonymous
    February 14, 2014
    Yes you can change it to use a shared Data source but then you will be monitoring only that one server as it imports the XEL files into a database on the same instance as the XE Session

  • Anonymous
    February 20, 2014
    Hi Denzil, This solution looks great, but am not able to see any data being displayed in my reports. All am getting are just blank reports irrespective of what report I choose and what params I pass :( Can you please let me know What could be the reason? I verified my "System_Health" session is running.

  • Anonymous
    September 10, 2014
    This is great, but it would be great to not have to have tables and stored procs on all the servers. Is there a way to have a central server that would hold the information and the reports would feed from it.

  • Anonymous
    January 22, 2015
    The comment has been removed

  • Anonymous
    July 07, 2015
    Error: The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart

  • Anonymous
    August 10, 2015
    When attempting to run against a remote server where I've collected the data I'm getting a failure.  It works just fine on the data collection on the reporting server itself.  I think i need to use a dedicated SQL logon that i have available.  How do you display the data source of these reports? I'm not seeing it anywhere.  I running this in SQL Server Data Tools.  I'm also not seeing where the parameters are defined which i think is where I'll find the data source?

  • Anonymous
    November 25, 2015
    SpLoadResourceComponent was running forever. Checked the query plan, it had parallelism all over the place. Set it to MAXDOP 1 , finished in 10 seconds.

  • Anonymous
    December 13, 2015
    So, you first need to buy a copy of Visual Studio 2010 ( $500) before you can use this report? it doesn't seem to work with the community version of 2015. Are there alternatives?

  • Anonymous
    March 10, 2017
    Hi Denzil,Very nice tool.I tried to use it and it seems powerful.I used extended event recently and I cannot understand how I can monitoring system for a given period.Is there a way to do that by using your stored procedure?It seems that by running the stored procedure [spLoadSystemHealthSession] all data stored get lost, because all tables are dropped and re-created.Thanks in advance for your cooperation and Best Regards.Giuseppe

  • Anonymous
    November 29, 2017
    cannot open the .sql file to create the schema