{"id":5017,"date":"2019-10-30T14:08:26","date_gmt":"2019-10-30T21:08:26","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=5017"},"modified":"2019-10-30T14:08:34","modified_gmt":"2019-10-30T21:08:34","slug":"updated-sys-dm_os_waiting_tasks-script-to-correctly-parse-nodeid","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script-to-correctly-parse-nodeid\/","title":{"rendered":"Updated sys.dm_os_waiting_tasks script to correctly parse nodeId"},"content":{"rendered":"<p style=\"text-align: justify;\">I realized that I&#8217;d fixed an issue with my <em>sys.dm_os_waiting_tasks<\/em> script to correctly parse out the parallelism <em>nodeId<\/em> from the <em>resource_description<\/em>\u00a0column (as newer versions include more info after the <em>nodeId=X<\/em> info) but I never blogged the update. Here it is for your use(and all other references have been updated to point to this post).<\/p>\n<p style=\"text-align: justify;\">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 onward\r\n------------------------------------------------------------------------------\r\n  Written by Paul S. Randal, SQLskills.com\r\n\r\n  (c) 2019, 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            SUBSTRING ( -- earlier versions don't have anything after the nodeID...\r\n                &#x5B;owt].&#x5B;resource_description],\r\n                CHARINDEX (N'nodeId=', &#x5B;owt].&#x5B;resource_description]) + 7,\r\n                CHARINDEX (N' tid=', &#x5B;owt].&#x5B;resource_description] + ' tid=') -\r\n                    CHARINDEX (N'nodeId=', &#x5B;owt].&#x5B;resource_description]) - 7\r\n            )\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>I realized that I&#8217;d fixed an issue with my sys.dm_os_waiting_tasks script to correctly parse out the parallelism nodeId from the resource_description\u00a0column (as newer versions include more info after the nodeId=X info) but I never blogged the update. Here it is for your use(and all other references have been updated to point to this post). Enjoy! [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38,101],"tags":[],"class_list":["post-5017","post","type-post","status-publish","format-standard","hentry","category-example-scripts","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 correctly parse nodeId - 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-to-correctly-parse-nodeid\/\" \/>\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 correctly parse nodeId - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"I realized that I&#8217;d fixed an issue with my sys.dm_os_waiting_tasks script to correctly parse out the parallelism nodeId from the resource_description\u00a0column (as newer versions include more info after the nodeId=X info) but I never blogged the update. Here it is for your use(and all other references have been updated to point to this post). Enjoy! [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script-to-correctly-parse-nodeid\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2019-10-30T21:08:26+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-10-30T21:08:34+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-to-correctly-parse-nodeid\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script-to-correctly-parse-nodeid\/\",\"name\":\"Updated sys.dm_os_waiting_tasks script to correctly parse nodeId - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2019-10-30T21:08:26+00:00\",\"dateModified\":\"2019-10-30T21:08:34+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-to-correctly-parse-nodeid\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script-to-correctly-parse-nodeid\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script-to-correctly-parse-nodeid\/#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 correctly parse nodeId\"}]},{\"@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 correctly parse nodeId - 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-to-correctly-parse-nodeid\/","og_locale":"en_US","og_type":"article","og_title":"Updated sys.dm_os_waiting_tasks script to correctly parse nodeId - Paul S. Randal","og_description":"I realized that I&#8217;d fixed an issue with my sys.dm_os_waiting_tasks script to correctly parse out the parallelism nodeId from the resource_description\u00a0column (as newer versions include more info after the nodeId=X info) but I never blogged the update. Here it is for your use(and all other references have been updated to point to this post). Enjoy! [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script-to-correctly-parse-nodeid\/","og_site_name":"Paul S. Randal","article_published_time":"2019-10-30T21:08:26+00:00","article_modified_time":"2019-10-30T21:08:34+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-to-correctly-parse-nodeid\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script-to-correctly-parse-nodeid\/","name":"Updated sys.dm_os_waiting_tasks script to correctly parse nodeId - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2019-10-30T21:08:26+00:00","dateModified":"2019-10-30T21:08:34+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-to-correctly-parse-nodeid\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script-to-correctly-parse-nodeid\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script-to-correctly-parse-nodeid\/#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 correctly parse nodeId"}]},{"@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\/5017","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=5017"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/5017\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=5017"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=5017"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=5017"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}