{"id":1039,"date":"2016-01-16T07:59:56","date_gmt":"2016-01-16T15:59:56","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=1039"},"modified":"2018-10-09T12:11:35","modified_gmt":"2018-10-09T19:11:35","slug":"sql-server-diagnostic-information-queries-detailed-day-16","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-16\/","title":{"rendered":"SQL Server Diagnostic Information Queries Detailed, Day 16"},"content":{"rendered":"

For Day 16 of this series, we start out with Query #37<\/strong>, which is CPU Utilization History. This query retrieves information from the somewhat undocumented sys.dm_os_ring_buffers<\/a> dynamic management view about recent CPU utilization by SQL Server. Query #37 is shown in Figure 1.<\/p>\n

\n
\n
   1:<\/span> -- Get CPU Utilization History for last 256 minutes (in one minute intervals)  (Query 37) (CPU Utilization History)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> -- This version works with SQL Server 2016<\/span><\/pre>\n
<\/p>\n
   3:<\/span> DECLARE<\/span> @ts_now bigint = (SELECT<\/span> cpu_ticks\/(cpu_ticks\/ms_ticks) FROM<\/span> sys.dm_os_sys_info WITH<\/span> (NOLOCK)); <\/pre>\n
<\/p>\n
   4:<\/span>  <\/pre>\n
<\/p>\n
   5:<\/span> SELECT<\/span> TOP<\/span>(256) SQLProcessUtilization AS<\/span> [SQL<\/span> Server Process CPU Utilization], <\/pre>\n
<\/p>\n
   6:<\/span>                SystemIdle AS<\/span> [System Idle Process], <\/pre>\n
<\/p>\n
   7:<\/span>                100 - SystemIdle - SQLProcessUtilization AS<\/span> [Other Process CPU Utilization], <\/pre>\n
<\/p>\n
   8:<\/span>                DATEADD(ms, -1 * (@ts_now - [timestamp<\/span>]), GETDATE()) AS<\/span> [Event Time<\/span>] <\/pre>\n
<\/p>\n
   9:<\/span> FROM<\/span> (SELECT<\/span> record.value<\/span>('(.\/Record\/@id)[1]'<\/span>, 'int'<\/span>) AS<\/span> record_id, <\/pre>\n
<\/p>\n
  10:<\/span>             record.value<\/span>('(.\/Record\/SchedulerMonitorEvent\/SystemHealth\/SystemIdle)[1]'<\/span>, 'int'<\/span>) <\/pre>\n
<\/p>\n
  11:<\/span>             AS<\/span> [SystemIdle], <\/pre>\n
<\/p>\n
  12:<\/span>             record.value<\/span>('(.\/Record\/SchedulerMonitorEvent\/SystemHealth\/ProcessUtilization)[1]'<\/span>, 'int'<\/span>) <\/pre>\n
<\/p>\n
  13:<\/span>             AS<\/span> [SQLProcessUtilization], [timestamp<\/span>] <\/pre>\n
<\/p>\n
  14:<\/span>       FROM<\/span> (SELECT<\/span> [timestamp<\/span>], CONVERT<\/span>(xml, record) AS<\/span> [record] <\/pre>\n
<\/p>\n
  15:<\/span>             FROM<\/span> sys.dm_os_ring_buffers WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  16:<\/span>             WHERE<\/span> ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'<\/span> <\/pre>\n
<\/p>\n
  17:<\/span>             AND<\/span> record LIKE<\/span> N'%<SystemHealth>%'<\/span>) AS<\/span> x) AS<\/span> y <\/pre>\n
<\/p>\n
  18:<\/span> ORDER<\/span> BY<\/span> record_id DESC<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  19:<\/span>  <\/pre>\n
<\/p>\n
  20:<\/span> -- Look at the trend over the entire period <\/span><\/pre>\n
<\/p>\n
  21:<\/span> -- Also look at<\/span> high sustained Other Process CPU Utilization values<\/pre>\n
<\/div>\n<\/div>\n
Figure 1: Query #37 CPU Utilization History<\/strong><\/p>\n
This query shows you the average CPU utilization history by the current instance of SQL Server, plus the summed average CPU utilization by all other processes on your machine are captured in one minute increments for the past 256 minutes. This lets you go back in time and see what has been happening with processor utilization over that period. It is always nice to know whether an episode of high CPU utilization has been sustained or whether it has been going on for just a short period. It is also nice to understand how much CPU other processes are using on your machine.<\/p>\n
Ideally, you have some sort of monitoring system in place to let you review your history and trends for more than four hours. Not all 3rd party monitoring systems are created equal. Some are much better than others. Personally, I really like SQLSentry PerformanceAdvisor<\/a>. <\/p>\n
Query #38<\/strong> is Top Worker Time Queries. This query retrieves information from the sys.dm_exec_query_stats<\/a> dynamic management view, the sys.dm_exec_sql_text<\/a> dynamic management function, and the sys.dm_exec_query_plan<\/a> dynamic management function about the highest cumulative worker time queries across your entire instance. Query #38 is shown in Figure 2.<\/p>\n
\n
\n
   1:<\/span> -- Get top total worker time queries for entire instance (Query 38) (Top Worker Time Queries)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> TOP<\/span>(50) DB_NAME(t.[dbid]) AS<\/span> [Database<\/span> Name], LEFT<\/span>(t.[text][\/text], 50) AS<\/span> [Short Query Text],  <\/pre>\n
<\/p>\n
   3:<\/span> qs.total_worker_time AS<\/span> [Total Worker Time<\/span>], qs.min_worker_time AS<\/span> [Min<\/span> Worker Time<\/span>],<\/pre>\n
<\/p>\n
   4:<\/span> qs.total_worker_time\/qs.execution_count AS<\/span> [Avg<\/span> Worker Time<\/span>], <\/pre>\n
<\/p>\n
   5:<\/span> qs.max_worker_time AS<\/span> [Max<\/span> Worker Time<\/span>], <\/pre>\n
<\/p>\n
   6:<\/span> qs.min_elapsed_time AS<\/span> [Min<\/span> Elapsed Time<\/span>], <\/pre>\n
<\/p>\n
   7:<\/span> qs.total_elapsed_time\/qs.execution_count AS<\/span> [Avg<\/span> Elapsed Time<\/span>], <\/pre>\n
<\/p>\n
   8:<\/span> qs.max_elapsed_time AS<\/span> [Max<\/span> Elapsed Time<\/span>],<\/pre>\n
<\/p>\n
   9:<\/span> qs.min_logical_reads AS<\/span> [Min<\/span> Logical Reads<\/span>],<\/pre>\n
<\/p>\n
  10:<\/span> qs.total_logical_reads\/qs.execution_count AS<\/span> [Avg<\/span> Logical Reads<\/span>],<\/pre>\n
<\/p>\n
  11:<\/span> qs.max_logical_reads AS<\/span> [Max<\/span> Logical Reads<\/span>], <\/pre>\n
<\/p>\n
  12:<\/span> qs.execution_count AS<\/span> [Execution Count<\/span>], qs.creation_time AS<\/span> [Creation Time<\/span>]<\/pre>\n
<\/p>\n
  13:<\/span> -- ,t.[text][\/text] AS [Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel<\/span><\/pre>\n
<\/p>\n
  14:<\/span> FROM<\/span> sys.dm_exec_query_stats AS<\/span> qs WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  15:<\/span> CROSS<\/span> APPLY sys.dm_exec_sql_text(plan_handle) AS<\/span> t <\/pre>\n
<\/p>\n
  16:<\/span> CROSS<\/span> APPLY sys.dm_exec_query_plan(plan_handle) AS<\/span> qp <\/pre>\n
<\/p>\n
  17:<\/span> ORDER<\/span> BY<\/span> qs.total_worker_time DESC<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  18:<\/span>  <\/pre>\n
<\/p>\n
  19:<\/span>  <\/pre>\n
<\/p>\n
  20:<\/span> -- Helps you find the most expensive queries from a CPU perspective across the entire instance<\/span><\/pre>\n
<\/p>\n
  21:<\/span> -- Can also help track down parameter sniffing issues<\/span><\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #38 Top Worker Time Queries<\/strong><\/p>\n
This query is very useful when you see any signs of CPU pressure. It helps you understand which cached queries are using the most CPU resources across your entire instance. You might notice that one line of the query is commented out. I do this so that the query results will copy and paste into Excel. If you are not going to copy\/paste the results, then you will usually get some more useful information by including the last two columns in the SELECT statement.<\/p>\n
If you are seeing very wide variations in minimum, average, and maximum worker time for a cached query, this might be evidence that you have a parameter sniffing issue (where some input values for a query produce results that are not well-suited for the cached version of the query plan that is being used). The new Query Store feature in SQL Server 2016 is going to be very useful for detecting and correcting these types of issues.<\/p>\n","protected":false},"excerpt":{"rendered":"
For Day 16 of this series, we start out with Query #37, which is CPU Utilization History. This query retrieves information from the somewhat undocumented sys.dm_os_ring_buffers dynamic management view about recent CPU utilization by SQL Server. Query #37 is shown in Figure 1. 1: — Get CPU Utilization History for last 256 minutes (in one […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[273],"tags":[270],"class_list":["post-1039","post","type-post","status-publish","format-standard","hentry","category-dmv-diagnostic-queries-detailed","tag-sql-server-diagnostic-information-queries-detailed"],"yoast_head":"\nSQL Server Diagnostic Information Queries Detailed, Day 16 - Glenn Berry<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-16\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Diagnostic Information Queries Detailed, Day 16 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"For Day 16 of this series, we start out with Query #37, which is CPU Utilization History. This query retrieves information from the somewhat undocumented sys.dm_os_ring_buffers dynamic management view about recent CPU utilization by SQL Server. Query #37 is shown in Figure 1. 1: -- Get CPU Utilization History for last 256 minutes (in one […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-16\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-16T15:59:56+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-10-09T19:11:35+00:00\" \/>\n<meta name=\"author\" content=\"Glenn Berry\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Glenn Berry\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-16\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-16\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 16 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-16T15:59:56+00:00\",\"dateModified\":\"2018-10-09T19:11:35+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-16\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-16\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-16\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 16\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\",\"name\":\"Glenn Berry\",\"description\":\"Semi-random musings about SQL Server performance\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\",\"name\":\"Glenn Berry\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"caption\":\"Glenn Berry\"},\"sameAs\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Diagnostic Information Queries Detailed, Day 16 - Glenn Berry","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-16\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 16 - Glenn Berry","og_description":"For Day 16 of this series, we start out with Query #37, which is CPU Utilization History. This query retrieves information from the somewhat undocumented sys.dm_os_ring_buffers dynamic management view about recent CPU utilization by SQL Server. Query #37 is shown in Figure 1. 1: -- Get CPU Utilization History for last 256 minutes (in one […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-16\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-16T15:59:56+00:00","article_modified_time":"2018-10-09T19:11:35+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-16\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-16\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 16 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-16T15:59:56+00:00","dateModified":"2018-10-09T19:11:35+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-16\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-16\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-16\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/glenn\/"},{"@type":"ListItem","position":2,"name":"SQL Server Diagnostic Information Queries Detailed, Day 16"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/","name":"Glenn Berry","description":"Semi-random musings about SQL Server performance","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7","name":"Glenn Berry","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","caption":"Glenn Berry"},"sameAs":["https:\/\/www.sqlskills.com\/blogs\/glenn\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1039","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/comments?post=1039"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1039\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1039"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1039"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1039"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}