SQL Server Premier Field Engineer Blog - All Comments

re: SQL 2012 System Health Reporting Dashboard – Visualizing sp_server_diagnostics results.

Michel Bruggeman — Mon, 13 May 2013 10:18:32 GMT

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

--exec SpLoadQueryProcessingComponent_HighCPU @UTDDateDiff

--exec SpLoadQueryProcessingComponent_QueryWaits @UTDDateDiff

re: SQL 2012 System Health Reporting Dashboard – Visualizing sp_server_diagnostics results.

SQL Server Premier Field Engineering — Wed, 01 May 2013 15:54:35 GMT

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.

re: SQL 2012 System Health Reporting Dashboard – Visualizing sp_server_diagnostics results.

Charles Farr — Wed, 01 May 2013 13:40:02 GMT

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

re: SQL High CPU scenario troubleshooting using sys.dm_exec_query_stats and RING_BUFFER_SCHEDULER_MONITOR ring buffer in sys.dm_os_ring_buffers

jogos — Wed, 01 May 2013 06:28:56 GMT

add to previous comment

3) sys.dm_exec_requests also can be intresting to capture on the moment the cpu-peek is occuring because that identifies the process the most

re: SQL High CPU scenario troubleshooting using sys.dm_exec_query_stats and RING_BUFFER_SCHEDULER_MONITOR ring buffer in sys.dm_os_ring_buffers

jogos — Wed, 01 May 2013 06:05:03 GMT

Intresting indeed. By gathering the high CPU-info I notice that it does not have a correlation with the processes at this time. The overall high performers must be checked on a regular basis, the cause for the peek now could be something that is only running once a month so it won't hit the accumulated top 25.

Some suggestions

1) also catch the top 25 of the sys.dm_exec_sessions (now active) in a separate tbl_troubleshooting_sessions -> possible to see who/what/where and get quicker information about the source and in time also an overview if there are large things that can be planned on other moment

2) add a column to the Tbl_troubleshootingPlans to make a difference between

a) 'ALL' overall top 25

b) 'NOW' top 25 with filter of processes from last 2 minutes or so

last_execution_time > dateadd(mi,getdate(),2)

Chances are that overlap will still be great and maybe a weighted cpu in correlation to the execution_count is an alternative to get track of heavy but less executed statements that hit your system recently

re: SQL 2012 System Health Reporting Dashboard – Visualizing sp_server_diagnostics results.

Denzil Ribeiro — Mon, 29 Apr 2013 21:06:28 GMT

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.

re: SQL 2012 System Health Reporting Dashboard – Visualizing sp_server_diagnostics results.

mbourgon — Mon, 29 Apr 2013 20:47:20 GMT

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.

re: SQL 2012 System Health Reporting Dashboard – Visualizing sp_server_diagnostics results.

SQL Server Premier Field Engineering — Mon, 29 Apr 2013 16:24:33 GMT

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

re: Identifying the cause of SQL Server IO bottlenecks using XPerf

joemct_sql — Fri, 26 Apr 2013 16:13:30 GMT

Thanks for the feedback William.

re: Identifying the cause of SQL Server IO bottlenecks using XPerf

William Dinger — Tue, 23 Apr 2013 16:39:14 GMT

This is a fantastic article, thank you for going so in depth!