{"id":4466,"date":"2015-01-05T11:12:59","date_gmt":"2015-01-05T19:12:59","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4466"},"modified":"2019-10-30T14:10:30","modified_gmt":"2019-10-30T21:10:30","slug":"updated-sys-dm_os_waiting_tasks-script","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script\/","title":{"rendered":"Updated sys.dm_os_waiting_tasks script"},"content":{"rendered":"<p style=\"text-align: justify;\"><strong>Edit 10\/30\/19: the latest version of this script, with additions\u00a0and adapted for newer versions is <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script-to-correctly-parse-nodeid\/\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a>.<\/strong><\/p>\n<p style=\"text-align: justify;\">Over the holidays I was playing around with parallelism and updated my <em>sys.dm_os_waiting_tasks<\/em> script to add in the scheduler distribution of the waiting threads. Here it is for your use.<\/p>\n<p>Enjoy!<\/p>\n<p>(Note that &#8216;text&#8217; on one line does not have delimiters because that messes up the code formatting plugin):<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\n\/*============================================================================\r\n  File:     WaitingTasks.sql\r\n\r\n  Summary:  Snapshot of waiting tasks\r\n\r\n  SQL Server Versions: 2005 onwards\r\n------------------------------------------------------------------------------\r\n  Written by Paul S. Randal, SQLskills.com\r\n\r\n  (c) 2015, SQLskills.com. All rights reserved.\r\n\r\n  For more scripts and sample code, check out \r\n    http:\/\/www.SQLskills.com\r\n\r\n  You may alter this code for your own *non-commercial* purposes. You may\r\n  republish altered code as long as you include this copyright and give due\r\n  credit, but you must obtain prior permission before blogging this code.\r\n  \r\n  THIS CODE AND INFORMATION ARE PROVIDED &quot;AS IS&quot; WITHOUT WARRANTY OF \r\n  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED \r\n  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND\/OR FITNESS FOR A\r\n  PARTICULAR PURPOSE.\r\n============================================================================*\/\r\nSELECT\r\n    &#x5B;owt].&#x5B;session_id],\r\n    &#x5B;owt].&#x5B;exec_context_id],\r\n    &#x5B;ot].&#x5B;scheduler_id],\r\n    &#x5B;owt].&#x5B;wait_duration_ms],\r\n    &#x5B;owt].&#x5B;wait_type],\r\n    &#x5B;owt].&#x5B;blocking_session_id],\r\n    &#x5B;owt].&#x5B;resource_description],\r\n    CASE &#x5B;owt].&#x5B;wait_type]\r\n        WHEN N'CXPACKET' THEN\r\n            RIGHT (&#x5B;owt].&#x5B;resource_description],\r\n                CHARINDEX (N'=', REVERSE (&#x5B;owt].&#x5B;resource_description])) - 1)\r\n        ELSE NULL\r\n    END AS &#x5B;Node ID],\r\n    --&#x5B;es].&#x5B;program_name],\r\n    &#x5B;est].text,\r\n    &#x5B;er].&#x5B;database_id],\r\n    &#x5B;eqp].&#x5B;query_plan],\r\n    &#x5B;er].&#x5B;cpu_time]\r\nFROM sys.dm_os_waiting_tasks &#x5B;owt]\r\nINNER JOIN sys.dm_os_tasks &#x5B;ot] ON\r\n    &#x5B;owt].&#x5B;waiting_task_address] = &#x5B;ot].&#x5B;task_address]\r\nINNER JOIN sys.dm_exec_sessions &#x5B;es] ON\r\n    &#x5B;owt].&#x5B;session_id] = &#x5B;es].&#x5B;session_id]\r\nINNER JOIN sys.dm_exec_requests &#x5B;er] ON\r\n    &#x5B;es].&#x5B;session_id] = &#x5B;er].&#x5B;session_id]\r\nOUTER APPLY sys.dm_exec_sql_text (&#x5B;er].&#x5B;sql_handle]) &#x5B;est]\r\nOUTER APPLY sys.dm_exec_query_plan (&#x5B;er].&#x5B;plan_handle]) &#x5B;eqp]\r\nWHERE\r\n    &#x5B;es].&#x5B;is_user_process] = 1\r\nORDER BY\r\n    &#x5B;owt].&#x5B;session_id],\r\n    &#x5B;owt].&#x5B;exec_context_id];\r\nGO\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Edit 10\/30\/19: the latest version of this script, with additions\u00a0and adapted for newer versions is here. Over the holidays I was playing around with parallelism and updated my sys.dm_os_waiting_tasks script to add in the scheduler distribution of the waiting threads. Here it is for your use. Enjoy! (Note that &#8216;text&#8217; on one line does not [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38,66,101],"tags":[],"class_list":["post-4466","post","type-post","status-publish","format-standard","hentry","category-example-scripts","category-performance-tuning","category-wait-stats"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Updated sys.dm_os_waiting_tasks script - Paul S. Randal<\/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\/paul\/updated-sys-dm_os_waiting_tasks-script\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Updated sys.dm_os_waiting_tasks script - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Edit 10\/30\/19: the latest version of this script, with additions\u00a0and adapted for newer versions is here. Over the holidays I was playing around with parallelism and updated my sys.dm_os_waiting_tasks script to add in the scheduler distribution of the waiting threads. Here it is for your use. Enjoy! (Note that &#8216;text&#8217; on one line does not [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2015-01-05T19:12:59+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-10-30T21:10:30+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script\/\",\"name\":\"Updated sys.dm_os_waiting_tasks script - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2015-01-05T19:12:59+00:00\",\"dateModified\":\"2019-10-30T21:10:30+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Updated sys.dm_os_waiting_tasks script\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?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\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Updated sys.dm_os_waiting_tasks script - Paul S. Randal","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\/paul\/updated-sys-dm_os_waiting_tasks-script\/","og_locale":"en_US","og_type":"article","og_title":"Updated sys.dm_os_waiting_tasks script - Paul S. Randal","og_description":"Edit 10\/30\/19: the latest version of this script, with additions\u00a0and adapted for newer versions is here. Over the holidays I was playing around with parallelism and updated my sys.dm_os_waiting_tasks script to add in the scheduler distribution of the waiting threads. Here it is for your use. Enjoy! (Note that &#8216;text&#8217; on one line does not [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script\/","og_site_name":"Paul S. Randal","article_published_time":"2015-01-05T19:12:59+00:00","article_modified_time":"2019-10-30T21:10:30+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script\/","name":"Updated sys.dm_os_waiting_tasks script - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2015-01-05T19:12:59+00:00","dateModified":"2019-10-30T21:10:30+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Updated sys.dm_os_waiting_tasks script"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?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\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4466","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=4466"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4466\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4466"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4466"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4466"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}