{"id":1038,"date":"2016-01-15T06:37:49","date_gmt":"2016-01-15T14:37:49","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=1038"},"modified":"2017-04-13T12:28:48","modified_gmt":"2017-04-13T19:28:48","slug":"sql-server-diagnostic-information-queries-detailed-day-15","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-15\/","title":{"rendered":"SQL Server Diagnostic Information Queries Detailed, Day 15"},"content":{"rendered":"

For Day 15 of this series, we start out with Query #35<\/strong>, which is Avg Task Counts. This query retrieves information from the sys.dm_os_schedulers<\/a> dynamic management view about the current average load across your SQL OS schedulers. Query #35 is shown in Figure 1.<\/p>\n

\n
\n
   1:<\/span> -- Get Average Task Counts (run multiple times)  (Query 35) (Avg Task Counts)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> AVG<\/span>(current_tasks_count) AS<\/span> [Avg<\/span> Task Count<\/span>], <\/pre>\n
<\/p>\n
   3:<\/span> AVG<\/span>(work_queue_count) AS<\/span> [Avg<\/span> Work<\/span> Queue Count<\/span>],<\/pre>\n
<\/p>\n
   4:<\/span> AVG<\/span>(runnable_tasks_count) AS<\/span> [Avg<\/span> Runnable Task Count<\/span>],<\/pre>\n
<\/p>\n
   5:<\/span> AVG<\/span>(pending_disk_io_count) AS<\/span> [Avg<\/span> Pending DiskIO Count<\/span>]<\/pre>\n
<\/p>\n
   6:<\/span> FROM<\/span> sys.dm_os_schedulers WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   7:<\/span> WHERE<\/span> scheduler_id < 255 OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
   8:<\/span>  <\/pre>\n
<\/p>\n
   9:<\/span> -- Sustained values above 10 suggest further investigation in that area<\/span><\/pre>\n
<\/p>\n
  10:<\/span> -- High Avg Task Counts are often caused by blocking\/deadlocking or other resource contention<\/span><\/pre>\n
<\/p>\n
  11:<\/span>  <\/pre>\n
<\/p>\n
  12:<\/span> -- Sustained values above 1 suggest further investigation in that area<\/span><\/pre>\n
<\/p>\n
  13:<\/span> -- High Avg Runnable Task Counts are a good sign of CPU pressure<\/span><\/pre>\n
<\/p>\n
  14:<\/span> -- High Avg<\/span> Pending DiskIO Counts are<\/span> a sign of<\/span> disk<\/span> pressure<\/pre>\n
<\/div>\n<\/div>\n
Figure 1: Query #35 Avg Task Counts<\/strong><\/p>\n
If you see high average task counts (above 10), that is usually a pretty good indicator of blocking\/deadlocking. In some cases, it just means that your instance is very busy, with a high sustained level of activity. If you see average runnable task counts above 0, that is a a good indicator of CPU pressure. If you see average pending IO counts above 0, that is a good indicator of I\/O pressure or bottlenecks. You need to run this query multiple times, since the results will change from second to second.<\/p>\n
Looking at the results of this query (after I have run it a few times over the course of a few minutes) gives me a good high-level sense of the workload and health of my SQL Server instance.<\/p>\n
 <\/p>\n
Query #36<\/strong> is Detect Blocking. This query retrieves information from the sys.dm_exec_requests<\/a> dynamic management view and the sys.dm_exec_sql_text<\/a> dynamic management function about any blocking activity that is occurring when you run the query. Query #36 is shown in Figure 2.<\/p>\n
\n
\n
   1:<\/span> -- Detect blocking (run multiple times)  (Query 36) (Detect Blocking)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> t1.resource_type AS<\/span> [lock type], DB_NAME(resource_database_id) AS<\/span> [database<\/span>],<\/pre>\n
<\/p>\n
   3:<\/span> t1.resource_associated_entity_id AS<\/span> [blk object<\/span>],t1.request_mode AS<\/span> [lock req],  --- lock requested<\/span><\/pre>\n
<\/p>\n
   4:<\/span> t1.request_session_id AS<\/span> [waiter sid], t2.wait_duration_ms AS<\/span> [wait time<\/span>],       -- spid of waiter  <\/span><\/pre>\n
<\/p>\n
   5:<\/span> (SELECT<\/span> [text][\/text] FROM<\/span> sys.dm_exec_requests AS<\/span> r WITH<\/span> (NOLOCK)                      -- get sql for waiter<\/span><\/pre>\n
<\/p>\n
   6:<\/span> CROSS<\/span> APPLY sys.dm_exec_sql_text(r.[sql_handle]) <\/pre>\n
<\/p>\n
   7:<\/span> WHERE<\/span> r.session_id = t1.request_session_id) AS<\/span> [waiter_batch],<\/pre>\n
<\/p>\n
   8:<\/span> (SELECT<\/span> SUBSTRING<\/span>(qt.[text][\/text],r.statement_start_offset\/2, <\/pre>\n
<\/p>\n
   9:<\/span>     (CASE<\/span> WHEN<\/span> r.statement_end_offset = -1 <\/pre>\n
<\/p>\n
  10:<\/span>     THEN<\/span> LEN(CONVERT<\/span>(nvarchar(max<\/span>), qt.[text][\/text])) * 2 <\/pre>\n
<\/p>\n
  11:<\/span>     ELSE<\/span> r.statement_end_offset END<\/span> - r.statement_start_offset)\/2) <\/pre>\n
<\/p>\n
  12:<\/span> FROM<\/span> sys.dm_exec_requests AS<\/span> r WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  13:<\/span> CROSS<\/span> APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS<\/span> qt<\/pre>\n
<\/p>\n
  14:<\/span> WHERE<\/span> r.session_id = t1.request_session_id) AS<\/span> [waiter_stmt],                    -- statement blocked<\/span><\/pre>\n
<\/p>\n
  15:<\/span> t2.blocking_session_id AS<\/span> [blocker sid],                                        -- spid of blocker<\/span><\/pre>\n
<\/p>\n
  16:<\/span> (SELECT<\/span> [text][\/text] FROM<\/span> sys.sysprocesses AS<\/span> p                                        -- get sql for blocker<\/span><\/pre>\n
<\/p>\n
  17:<\/span> CROSS<\/span> APPLY sys.dm_exec_sql_text(p.[sql_handle]) <\/pre>\n
<\/p>\n
  18:<\/span> WHERE<\/span> p.spid = t2.blocking_session_id) AS<\/span> [blocker_batch]<\/pre>\n
<\/p>\n
  19:<\/span> FROM<\/span> sys.dm_tran_locks AS<\/span> t1 WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  20:<\/span> INNER<\/span> JOIN<\/span> sys.dm_os_waiting_tasks AS<\/span> t2 WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
  21:<\/span> ON<\/span> t1.lock_owner_address = t2.resource_address OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  22:<\/span>  <\/pre>\n
<\/p>\n
  23:<\/span> -- Helps troubleshoot blocking and deadlocking issues<\/span><\/pre>\n
<\/p>\n
  24:<\/span> -- The results will change from second to second on a busy system<\/span><\/pre>\n
<\/p>\n
  25:<\/span> -- You should run this query multiple times when<\/span> you see signs of<\/span> blocking<\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #36 Detect Blocking<\/strong><\/p>\n
If no blocking is happening when you run this query, it will not return any results. This is what you want to see! You need to run this query multiple times, since the results will often change from second to second. Don\u2019t just run it once, and then conclude that there is no blocking happening at any time.<\/p>\n
If any blocking is occurring, then this query will show you the blocked query text and the query text of the blocker. This information can be very useful when it comes to understanding what is going on when blocking or deadlocking is happening. Many times, excessive blocking and deadlocking is caused by missing indexes on a table, so proper index tuning can be a very effective solution.<\/p>\n","protected":false},"excerpt":{"rendered":"
For Day 15 of this series, we start out with Query #35, which is Avg Task Counts. This query retrieves information from the sys.dm_os_schedulers dynamic management view about the current average load across your SQL OS schedulers. Query #35 is shown in Figure 1. 1: — Get Average Task Counts (run multiple times) (Query 35) […]<\/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-1038","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 15 - 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-15\/\" \/>\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 15 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"For Day 15 of this series, we start out with Query #35, which is Avg Task Counts. This query retrieves information from the sys.dm_os_schedulers dynamic management view about the current average load across your SQL OS schedulers. Query #35 is shown in Figure 1. 1: -- Get Average Task Counts (run multiple times) (Query 35) […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-15\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-15T14:37:49+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T19:28:48+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-15\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-15\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 15 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-15T14:37:49+00:00\",\"dateModified\":\"2017-04-13T19:28:48+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-15\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-15\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-15\/#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 15\"}]},{\"@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 15 - 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-15\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 15 - Glenn Berry","og_description":"For Day 15 of this series, we start out with Query #35, which is Avg Task Counts. This query retrieves information from the sys.dm_os_schedulers dynamic management view about the current average load across your SQL OS schedulers. Query #35 is shown in Figure 1. 1: -- Get Average Task Counts (run multiple times) (Query 35) […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-15\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-15T14:37:49+00:00","article_modified_time":"2017-04-13T19:28:48+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-15\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-15\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 15 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-15T14:37:49+00:00","dateModified":"2017-04-13T19:28:48+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-15\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-15\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-15\/#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 15"}]},{"@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\/1038","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=1038"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1038\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1038"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1038"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1038"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}