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.
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 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 SystemAnonymous
April 15, 2013
very nice. Excellent use of the system health session data Thanks for sharingAnonymous
April 16, 2013
Hi, could you make theses reports available on some other area plaes. Skydrive is blocked in many places.... Br, StaffanAnonymous
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 haveAnonymous
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 removedAnonymous
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 removedAnonymous
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 @UTDDateDiffAnonymous
June 13, 2013
The comment has been removedAnonymous
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 serverAnonymous
November 24, 2013
The comment has been removedAnonymous
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
- install SQL 2012 DB engine, Report server and Data Tools
- ALTER EVENT SESSION
- run CreateSystemhealthDBAndSchema.sql
- Exec spLoadSystemHealthSession
- Configure Report Server
- Configure RS-TargetServer and Deploy
- 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.comAnonymous
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 SessionAnonymous
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 removedAnonymous
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 datepartAnonymous
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.GiuseppeAnonymous
November 29, 2017
cannot open the .sql file to create the schema