{"id":4614,"date":"2016-05-03T08:34:43","date_gmt":"2016-05-03T15:34:43","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4614"},"modified":"2019-10-30T14:10:36","modified_gmt":"2019-10-30T21:10:36","slug":"updated-sys-dm_os_waiting_tasks-script-2","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script-2\/","title":{"rendered":"Updated sys.dm_os_waiting_tasks script to add query DOP"},"content":{"rendered":"<p><strong>Edit 10\/30\/19: the latest version of this script, with additions\u00a0and adapted for newer versions\u00a0is\u00a0<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>A question came up in class today about easily seeing the degree of parallelism for parallel query plans, so I&#8217;ve updated my waiting tasks script to pull in the\u00a0<em>dop<\/em> field from\u00a0<em>sys.dm_exec_query_memory_grants<\/em>. I&#8217;ve also added in a URL field that points into the new waits library, and shortened some of the column names.<\/p>\n<p>Here it is for your use.<\/p>\n<p>Enjoy!<\/p>\n<p>(Note that \u2018text\u2019 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) 2016, 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] AS &#x5B;SPID],\r\n    &#x5B;owt].&#x5B;exec_context_id] AS &#x5B;Thread],\r\n    &#x5B;ot].&#x5B;scheduler_id] AS &#x5B;Scheduler],\r\n    &#x5B;owt].&#x5B;wait_duration_ms] AS &#x5B;wait_ms],\r\n    &#x5B;owt].&#x5B;wait_type],\r\n    &#x5B;owt].&#x5B;blocking_session_id] AS &#x5B;Blocking SPID],\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;eqmg].&#x5B;dop] AS &#x5B;DOP],\r\n    &#x5B;er].&#x5B;database_id] AS &#x5B;DBID],\r\n    CAST ('https:\/\/www.sqlskills.com\/help\/waits\/' + &#x5B;owt].&#x5B;wait_type] as XML) AS &#x5B;Help\/Info URL],\r\n    &#x5B;eqp].&#x5B;query_plan],\r\n    &#x5B;est].text\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\nFULL JOIN sys.dm_exec_query_memory_grants &#x5B;eqmg] ON\r\n    &#x5B;owt].&#x5B;session_id] = &#x5B;eqmg].&#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\u00a0is\u00a0here. A question came up in class today about easily seeing the degree of parallelism for parallel query plans, so I&#8217;ve updated my waiting tasks script to pull in the\u00a0dop field from\u00a0sys.dm_exec_query_memory_grants. I&#8217;ve also added in a URL field that points into [&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-4614","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 to add query DOP - 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-2\/\" \/>\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 to add query DOP - 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\u00a0is\u00a0here. A question came up in class today about easily seeing the degree of parallelism for parallel query plans, so I&#8217;ve updated my waiting tasks script to pull in the\u00a0dop field from\u00a0sys.dm_exec_query_memory_grants. I&#8217;ve also added in a URL field that points into [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script-2\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2016-05-03T15:34:43+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-10-30T21:10:36+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-2\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script-2\/\",\"name\":\"Updated sys.dm_os_waiting_tasks script to add query DOP - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2016-05-03T15:34:43+00:00\",\"dateModified\":\"2019-10-30T21:10:36+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-2\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script-2\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script-2\/#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 to add query DOP\"}]},{\"@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 to add query DOP - 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-2\/","og_locale":"en_US","og_type":"article","og_title":"Updated sys.dm_os_waiting_tasks script to add query DOP - Paul S. Randal","og_description":"Edit 10\/30\/19: the latest version of this script, with additions\u00a0and adapted for newer versions\u00a0is\u00a0here. A question came up in class today about easily seeing the degree of parallelism for parallel query plans, so I&#8217;ve updated my waiting tasks script to pull in the\u00a0dop field from\u00a0sys.dm_exec_query_memory_grants. I&#8217;ve also added in a URL field that points into [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script-2\/","og_site_name":"Paul S. Randal","article_published_time":"2016-05-03T15:34:43+00:00","article_modified_time":"2019-10-30T21:10:36+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-2\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script-2\/","name":"Updated sys.dm_os_waiting_tasks script to add query DOP - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2016-05-03T15:34:43+00:00","dateModified":"2019-10-30T21:10:36+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-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script-2\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script-2\/#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 to add query DOP"}]},{"@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\/4614","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=4614"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4614\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4614"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4614"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4614"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}