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:




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.



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.


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.


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




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



Connectivity Ring Buffer




Blocking > 30 seconds



System sp_Server_diagnostics component




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  http://www.sswug.org/articles/viewarticle.aspx?id=62732



Enjoy, and Feedback is always welcome J


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

Comments (31)
  1.   SQL Server 2008 and above provide an amazing diagnostics capability in the form of the System

  2. Andreas Wolter says:

    very nice. Excellent use of the system health session data

    Thanks for sharing

  3. Staffan says:


    could you make theses reports available on some other area plaes. Skydrive is blocked in many places….



  4. Thanks Andreas, glad you liked it .

    Staffan, let me explore what I can do other than Skydrive and get back to you.

  5. Hey y’all, Mark here with some quick links for you. We have two other PFE team blogs that have

  6. Updated Download location to TechNet Script center, and fixed a few reports to work with Case sensitive servers.

  7. mbourgon says:

    FYI, when I tried running the script to create, I got this:

    The module 'SpLoadQueryProcessingComponent' depends on the missing object 'SpLoadQueryProcessingComponent_TopWaits'. The module will still be created; however, it cannot run successfully until the object exists.

    The module 'SpLoadQueryProcessingComponent' depends on the missing object 'SpLoadQueryProcessingComponent_Blocking'. The module will still be created; however, it cannot run successfully until the object exists.

    Msg 1088, Level 16, State 12, Line 6

    Cannot find the object "dbo.tbl_Summary" because it does not exist or you do not have permissions.

  8. Denzil Ribeiro says:

    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.

  9. Charles Farr says:

    Per your example to execute the spLoadSystemHealthSession procs, are you just running them once a day, or are you doing this once an hour, etc…

  10. 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.

  11. Michel Bruggeman says:

    Are these sp going to be released in the next release ?

    –exec SpLoadQueryProcessingComponent_HighCPU @UTDDateDiff

    –exec SpLoadQueryProcessingComponent_QueryWaits @UTDDateDiff

  12. mbourgon says:

    Denzil, is there a trick to getting this working?  I cannot get the reports to work properly – though I have deployed it successfully to my Report Server.  Each time it's "Cannot create a connection to data source 'InitialDataSource'.  For ImportSessionStatus I never get to choose the database, even when the server is definitely a 2012 box that has the XEvents_ImportSystemHealth database.

  13. Denzil says:

    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.

  14. mbourgon says:

    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.

  15. Kumar Duvvuri says:

    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

  16. dennislee124 says:

    I found several reports caused errors. Please help.


    An error has occurred during report processing. (rsProcessingAborted)

    Cannot create a connection to data source 'InitialDataSource'. (rsErrorOpeningConnection)

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)


    An error has occurred during report processing. (rsProcessingAborted)

    Query execution failed for dataset 'CPU'. (rsErrorExecutingCommand)

    Invalid column name 'System_idle'.


    An error has occurred during report processing. (rsProcessingAborted)

    Query execution failed for dataset 'QueryProcessing'. (rsErrorExecutingCommand)

    Invalid column name 'Timestamp'.


    An error has occurred during report processing. (rsProcessingAborted)

    Query execution failed for dataset 'Memory'. (rsErrorExecutingCommand)

    Invalid column name 'state'. Invalid column name 'LastNotification'. Invalid column name 'Target_committed_kb'. Invalid column name 'Pages_free_kb'. Invalid column name 'Pages_in_use_kb'. Invalid column name 'Timestamp'.

  17. Denzil says:

    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.

  18. dennislee says:

    1. install SQL 2012 DB engine, Report server and Data Tools


    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

  19. gabe says:

    Does anyone has the script for sql server 2008 from gissah@live.com

  20. m bourgon says:

    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.

  21. m bourgon says:

    offline email is my last name (as shown above), at gmail. Thanks.

  22. Denzil says:

    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

  23. sree says:

    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.

  24. David says:

    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.

  25. Jamie says:

    I've tried stripping information out of the solution file and opening it using the sql 2012 data tools, but keep being told it needs to be upgraded. Unfortunately, I get the following error:

    Conversion Report – XEventReportingXEventReporting.rptproj:

    Could not find a part of the path '\tsclientCXEventReportingXEventReporting.rptproj'.

  26. G Britton says:

    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

  27. David W says:

    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?

  28. André X says:

    SpLoadResourceComponent was running forever.

    Checked the query plan, it had parallelism all over the place.

    Set it to MAXDOP 1 , finished in 10 seconds.

  29. willem says:

    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?

  30. Giuseppe says:

    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.

  31. ron williams says:

    cannot open the .sql file to create the schema

Comments are closed.

Skip to main content