{"id":2262,"date":"2018-11-28T19:41:04","date_gmt":"2018-11-28T19:41:04","guid":{"rendered":"https:\/\/nenadnoveljic.com\/blog\/?p=2262"},"modified":"2018-11-28T19:41:04","modified_gmt":"2018-11-28T19:41:04","slug":"breakpointing-sql-execution-with-windbg","status":"publish","type":"post","link":"https:\/\/nenadnoveljic.com\/blog\/breakpointing-sql-execution-with-windbg\/","title":{"rendered":"Breakpointing SQL Execution with WinDbg"},"content":{"rendered":"

Unlike Oracle, SQL Server has just one server process whose threads handle all of the backround tasks and user requests. Consequently, if you attach to it with WinDbg and set a breakpoint, the program will suspend its execution the first time a thread comes to the breakpoint. But usually, you don’t won’t to stop when a random thread reaches the breakpoint. Instead, what you’re interested in is, to pause the execution only when some specific thread, namely the one which is executing a SQL of interest, reaches it. In this blog post I’ll be explaining how to do that. <\/p>\n

First of all, we need to identify the id of the OS thread which is executing our SQL. Fortunately, the layers participating in SQL execution scheduling are exposed through the dynamic views. Firstly, we can get the OS thread information from the dynamic view sys.dm_os_threads. Secondly, the worker thread, which is the OS thread’s logical representation, is externalized through the dynamic view sys.dm_os_workers. Finally, a SQL request gets assigned to a worker as a task and can be examined by querying sys.dm_exec_requests. Each of these layers contains pointers to the structures in the layer below.<\/p>\n

Having said that, it’s easy to follow the cascade described above to find out the OS thread id:<\/p>\n

select convert(VARBINARY(8),t.os_thread_id)\r\n  from sys.dm_exec_requests r join sys.dm_os_workers w \r\n    on r.task_address = w.task_address  \r\n  join sys.dm_os_threads t \r\n    on w.worker_address = t.worker_address\r\n  where session_id=67 ;\r\n  \r\n0x00000ACC<\/span><\/code><\/pre>\n
In our example, the SQL of interest is running in the session 67.<\/p>\n
Next, we’ll use this thread_id to identify the thread in WinDbg. We’ll do that by listing all the threads with the ~ command and then looking for our OS thread id:<\/p>\n
~\r\n...\r\n  84  Id: 1b2c.460 Suspend: 1 Teb: 00000033`3ffe9000 Unfrozen\r\n  85  Id: 1b2c.2654 Suspend: 1 Teb: 00000033`3ffeb000 Unfrozen\r\n  86<\/span>  Id: 1b2c.acc<\/span> Suspend: 1 Teb: 00000033`3ffed000 Unfrozen\r\n  87  Id: 1b2c.2af8 Suspend: 1 Teb: 00000033`3ffef000 Unfrozen\r\n  88  Id: 1b2c.2af0 Suspend: 1 Teb: 00000033`3fff1000 Unfrozen\r\n  89  Id: 1b2c.2950 Suspend: 1 Teb: 00000033`3fff3000 Unfrozen\r\n  90  Id: 1b2c.2398 Suspend: 1 Teb: 00000033`3fff5000 Unfrozen\r\n...<\/code><\/pre>\n
As you can see, our thread of interest is the thread 86<\/span>.<\/p>\n
Now we can set the breakpoint for this particular thread:<\/p>\n
~86<\/span> bm sqlmin!BTreeMgr::GetHPageIdWithKey<\/code><\/pre>\n
The program, indeed, stopped when the thread 86<\/span> hit the breakpoint:<\/p>\n
0:086<\/span>> g\r\nBreakpoint 1 hit\r\nsqlmin!BTreeMgr::GetHPageIdWithKey:\r\n00007ffe`d4b300a0 4c89442418<\/code><\/pre>\n
References:<\/b><\/p>\n
Joseph Pilov: Tasks, Workers, Threads, Scheduler, Sessions, Connections, Requests \u2013 what does it all mean?<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Debugging SQL Server with WinDbg Continue Reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[17],"tags":[],"class_list":["post-2262","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"yoast_head":"\nBreakpointing SQL Execution with WinDbg - All-round Database Topics<\/title>\n<meta name=\"description\" content=\"Debugging SQL Server with WinDbg\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/nenadnoveljic.com\/blog\/breakpointing-sql-execution-with-windbg\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Breakpointing SQL Execution with WinDbg - All-round Database Topics\" \/>\n<meta property=\"og:description\" content=\"Debugging SQL Server with WinDbg\" \/>\n<meta property=\"og:url\" content=\"https:\/\/nenadnoveljic.com\/blog\/breakpointing-sql-execution-with-windbg\/\" \/>\n<meta property=\"og:site_name\" content=\"All-round Database Topics\" \/>\n<meta property=\"article:published_time\" content=\"2018-11-28T19:41:04+00:00\" \/>\n<meta name=\"author\" content=\"Nenad Noveljic\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@NenadNoveljic\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nenad Noveljic\" \/>\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\":\"Article\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/breakpointing-sql-execution-with-windbg\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/breakpointing-sql-execution-with-windbg\\\/\"},\"author\":{\"name\":\"Nenad Noveljic\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"headline\":\"Breakpointing SQL Execution with WinDbg\",\"datePublished\":\"2018-11-28T19:41:04+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/breakpointing-sql-execution-with-windbg\\\/\"},\"wordCount\":323,\"commentCount\":0,\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/breakpointing-sql-execution-with-windbg\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/breakpointing-sql-execution-with-windbg\\\/\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/breakpointing-sql-execution-with-windbg\\\/\",\"name\":\"Breakpointing SQL Execution with WinDbg - All-round Database Topics\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\"},\"datePublished\":\"2018-11-28T19:41:04+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"description\":\"Debugging SQL Server with WinDbg\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/breakpointing-sql-execution-with-windbg\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/breakpointing-sql-execution-with-windbg\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/breakpointing-sql-execution-with-windbg\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Breakpointing SQL Execution with WinDbg\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\",\"name\":\"All-round Database Topics\",\"description\":\"Nenad Noveljic\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\",\"name\":\"Nenad Noveljic\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"caption\":\"Nenad Noveljic\"},\"sameAs\":[\"nenad-noveljic-9b746a6\",\"https:\\\/\\\/x.com\\\/NenadNoveljic\"],\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/author\\\/nenad\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Breakpointing SQL Execution with WinDbg - All-round Database Topics","description":"Debugging SQL Server with WinDbg","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:\/\/nenadnoveljic.com\/blog\/breakpointing-sql-execution-with-windbg\/","og_locale":"en_US","og_type":"article","og_title":"Breakpointing SQL Execution with WinDbg - All-round Database Topics","og_description":"Debugging SQL Server with WinDbg","og_url":"https:\/\/nenadnoveljic.com\/blog\/breakpointing-sql-execution-with-windbg\/","og_site_name":"All-round Database Topics","article_published_time":"2018-11-28T19:41:04+00:00","author":"Nenad Noveljic","twitter_card":"summary_large_image","twitter_creator":"@NenadNoveljic","twitter_misc":{"Written by":"Nenad Noveljic","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/nenadnoveljic.com\/blog\/breakpointing-sql-execution-with-windbg\/#article","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/breakpointing-sql-execution-with-windbg\/"},"author":{"name":"Nenad Noveljic","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"headline":"Breakpointing SQL Execution with WinDbg","datePublished":"2018-11-28T19:41:04+00:00","mainEntityOfPage":{"@id":"https:\/\/nenadnoveljic.com\/blog\/breakpointing-sql-execution-with-windbg\/"},"wordCount":323,"commentCount":0,"articleSection":["SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/nenadnoveljic.com\/blog\/breakpointing-sql-execution-with-windbg\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/nenadnoveljic.com\/blog\/breakpointing-sql-execution-with-windbg\/","url":"https:\/\/nenadnoveljic.com\/blog\/breakpointing-sql-execution-with-windbg\/","name":"Breakpointing SQL Execution with WinDbg - All-round Database Topics","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#website"},"datePublished":"2018-11-28T19:41:04+00:00","author":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"description":"Debugging SQL Server with WinDbg","breadcrumb":{"@id":"https:\/\/nenadnoveljic.com\/blog\/breakpointing-sql-execution-with-windbg\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/nenadnoveljic.com\/blog\/breakpointing-sql-execution-with-windbg\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/nenadnoveljic.com\/blog\/breakpointing-sql-execution-with-windbg\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/nenadnoveljic.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Breakpointing SQL Execution with WinDbg"}]},{"@type":"WebSite","@id":"https:\/\/nenadnoveljic.com\/blog\/#website","url":"https:\/\/nenadnoveljic.com\/blog\/","name":"All-round Database Topics","description":"Nenad Noveljic","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/nenadnoveljic.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa","name":"Nenad Noveljic","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","caption":"Nenad Noveljic"},"sameAs":["nenad-noveljic-9b746a6","https:\/\/x.com\/NenadNoveljic"],"url":"https:\/\/nenadnoveljic.com\/blog\/author\/nenad\/"}]}},"_links":{"self":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/2262","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/comments?post=2262"}],"version-history":[{"count":1,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/2262\/revisions"}],"predecessor-version":[{"id":2276,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/2262\/revisions\/2276"}],"wp:attachment":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/media?parent=2262"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/categories?post=2262"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/tags?post=2262"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}