SQL High CPU scenario troubleshooting using sys.dm_exec_query_stats and RING_BUFFER_SCHEDULER_MONITOR ring buffer in sys.dm_os_ring_buffershttp://blogs.msdn.com/b/sql_pfe_blog/archive/2009/07/17/sql-high-cpu-scenario-troubleshooting-using-sys-dm-exec-query-stats-and-ring-buffer-scheduler-monitor-ring-buffer-in-sys-dm-os-ring-buffers.aspxI ran into a scenario a while ago that I thought I would share, where we were troubleshooting high CPU on&#160; SQL Server.&#160; The sporadic High CPU ( 90+ % )&#160; pretty much brought the box down to its knees for a period of 5-10 minutes minutesen-USTelligent Evolution Platform Developer Build (Build: 5.6.50428.7875)re: SQL High CPU scenario troubleshooting using sys.dm_exec_query_stats and RING_BUFFER_SCHEDULER_MONITOR ring buffer in sys.dm_os_ring_buffershttp://blogs.msdn.com/b/sql_pfe_blog/archive/2009/07/17/sql-high-cpu-scenario-troubleshooting-using-sys-dm-exec-query-stats-and-ring-buffer-scheduler-monitor-ring-buffer-in-sys-dm-os-ring-buffers.aspx#10415291Wed, 01 May 2013 06:28:56 GMT91d46819-8472-40ad-a661-2c78acb4018c:10415291jogos<p>add to previous comment </p>
<p>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 </p>
<div style="clear:both;"></div><img src="http://blogs.msdn.com/aggbug.aspx?PostID=10415291" width="1" height="1">re: SQL High CPU scenario troubleshooting using sys.dm_exec_query_stats and RING_BUFFER_SCHEDULER_MONITOR ring buffer in sys.dm_os_ring_buffershttp://blogs.msdn.com/b/sql_pfe_blog/archive/2009/07/17/sql-high-cpu-scenario-troubleshooting-using-sys-dm-exec-query-stats-and-ring-buffer-scheduler-monitor-ring-buffer-in-sys-dm-os-ring-buffers.aspx#10415290Wed, 01 May 2013 06:05:03 GMT91d46819-8472-40ad-a661-2c78acb4018c:10415290jogos<p>Intresting indeed. &nbsp;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&#39;t hit the accumulated top 25.</p>
<p>Some suggestions</p>
<p>1) also catch the top 25 of the sys.dm_exec_sessions (now active) in a separate tbl_troubleshooting_sessions &nbsp;-&gt; 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 </p>
<p>2) &nbsp;add a column to the Tbl_troubleshootingPlans to make a difference between</p>
<p> &nbsp;a) &#39;ALL&#39; overall top 25 </p>
<p> &nbsp;b) &#39;NOW&#39; top 25 with &nbsp;filter of processes from last 2 minutes or so</p>
<p> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;last_execution_time &gt; dateadd(mi,getdate(),2) &nbsp; &nbsp;</p>
<p> &nbsp; &nbsp; &nbsp; Chances are that overlap will still be great and maybe a weighted cpu &nbsp;in correlation to the execution_count is an alternative to get track of heavy but less executed statements that hit your system recently &nbsp;</p>
<div style="clear:both;"></div><img src="http://blogs.msdn.com/aggbug.aspx?PostID=10415290" width="1" height="1">re: SQL High CPU scenario troubleshooting using sys.dm_exec_query_stats and RING_BUFFER_SCHEDULER_MONITOR ring buffer in sys.dm_os_ring_buffershttp://blogs.msdn.com/b/sql_pfe_blog/archive/2009/07/17/sql-high-cpu-scenario-troubleshooting-using-sys-dm-exec-query-stats-and-ring-buffer-scheduler-monitor-ring-buffer-in-sys-dm-os-ring-buffers.aspx#10402385Thu, 14 Mar 2013 18:03:29 GMT91d46819-8472-40ad-a661-2c78acb4018c:10402385SQL Server Premier Field Engineering<p>From an alerting perspective, you could either set Perfmon alerts - <a rel="nofollow" target="_new" href="http://technet.microsoft.com/en-us/library/cc722414.aspx">technet.microsoft.com/.../cc722414.aspx</a> &nbsp;or within the polling script itself use sp_send_dbmail</p>
<p> <a rel="nofollow" target="_new" href="http://msdn.microsoft.com/en-us/library/ms177580(v=sql.100).aspx">msdn.microsoft.com/.../ms177580(v=sql.100).aspx</a> . Note that the ring buffers have entries for CPU only every 60 seconds</p>
<div style="clear:both;"></div><img src="http://blogs.msdn.com/aggbug.aspx?PostID=10402385" width="1" height="1">re: SQL High CPU scenario troubleshooting using sys.dm_exec_query_stats and RING_BUFFER_SCHEDULER_MONITOR ring buffer in sys.dm_os_ring_buffershttp://blogs.msdn.com/b/sql_pfe_blog/archive/2009/07/17/sql-high-cpu-scenario-troubleshooting-using-sys-dm-exec-query-stats-and-ring-buffer-scheduler-monitor-ring-buffer-in-sys-dm-os-ring-buffers.aspx#10402350Thu, 14 Mar 2013 15:59:27 GMT91d46819-8472-40ad-a661-2c78acb4018c:10402350Sj<p>It is so interesting and one thing seems to be missing ,like alerting the user when cpu hits specified threshold</p>
<p>i was wondering if anyone could help me to set up an email alert when it meets threashold.. using tsql script</p>
<div style="clear:both;"></div><img src="http://blogs.msdn.com/aggbug.aspx?PostID=10402350" width="1" height="1">re: SQL High CPU scenario troubleshooting using sys.dm_exec_query_stats and RING_BUFFER_SCHEDULER_MONITOR ring buffer in sys.dm_os_ring_buffershttp://blogs.msdn.com/b/sql_pfe_blog/archive/2009/07/17/sql-high-cpu-scenario-troubleshooting-using-sys-dm-exec-query-stats-and-ring-buffer-scheduler-monitor-ring-buffer-in-sys-dm-os-ring-buffers.aspx#10394904Mon, 18 Feb 2013 16:16:00 GMT91d46819-8472-40ad-a661-2c78acb4018c:10394904JRStern<p>An excellent walk through some new-ish features, and a nifty solution to capturing plans for problem queries.</p>
<p>Of course simply putting a 100ms duration filter on profiler would cut your 350mb per minute by 50% to 90%, and putting another filter on login or appname would probably cut it to a couple of mb per minute, but if what you wanted was still down in the 165ms range, you&#39;d still be steps away from capturing its plans.</p>
<p>I have not yet used a plan guide, didn&#39;t even realize they would work on statements inside of SPs, so you can do the work of a hint without modifying the source!</p>
<p>Thanks.</p>
<div style="clear:both;"></div><img src="http://blogs.msdn.com/aggbug.aspx?PostID=10394904" width="1" height="1">re: SQL High CPU scenario troubleshooting using sys.dm_exec_query_stats and RING_BUFFER_SCHEDULER_MONITOR ring buffer in sys.dm_os_ring_buffershttp://blogs.msdn.com/b/sql_pfe_blog/archive/2009/07/17/sql-high-cpu-scenario-troubleshooting-using-sys-dm-exec-query-stats-and-ring-buffer-scheduler-monitor-ring-buffer-in-sys-dm-os-ring-buffers.aspx#10099608Thu, 02 Dec 2010 16:43:00 GMT91d46819-8472-40ad-a661-2c78acb4018c:10099608Sudeep Bhaskar<p>great post.</p>
<p>In past one year, i have seen 90% of our high CPU scenarios were caused by parameter sniffing.</p>
<p> Thanks for the details Denzil!</p>
<div style="clear:both;"></div><img src="http://blogs.msdn.com/aggbug.aspx?PostID=10099608" width="1" height="1">re: SQL High CPU scenario troubleshooting using sys.dm_exec_query_stats and RING_BUFFER_SCHEDULER_MONITOR ring buffer in sys.dm_os_ring_buffershttp://blogs.msdn.com/b/sql_pfe_blog/archive/2009/07/17/sql-high-cpu-scenario-troubleshooting-using-sys-dm-exec-query-stats-and-ring-buffer-scheduler-monitor-ring-buffer-in-sys-dm-os-ring-buffers.aspx#10028422Tue, 22 Jun 2010 11:06:02 GMT91d46819-8472-40ad-a661-2c78acb4018c:10028422dbaffaleuf<p>Hi Denzil,</p>
<p>Very interesting. But why do you use a cross join with sys.dm_os_sys_info in the first place ? Couldn&#39;t you get the same results only by simply querying sys.dm_os_ring_buffers ?</p>
<p>Thanks,</p>
<p>David B.</p>
<div style="clear:both;"></div><img src="http://blogs.msdn.com/aggbug.aspx?PostID=10028422" width="1" height="1">