{"id":8777,"date":"2016-02-25T15:54:33","date_gmt":"2016-02-25T15:54:33","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/7486\/"},"modified":"2016-07-28T10:57:54","modified_gmt":"2016-07-28T10:57:54","slug":"7486","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/7486\/","title":{"rendered":"How to find blocked process"},"content":{"rendered":"<p>Blocked processes are often a big problem to DBA&#8217;s. They are difficult to monitor. We receive desperate calls saying &#8220;Everything is slow!&#8221; and before we can do anything a second call &#8220;Oh, don&#8217;t worry, it&#8217;s everything ok again&#8221;.<\/p>\n<p>Most of times it happens because ill-behaved process that blocks other tasks for short periods. How to find this kind of problem?<\/p>\n<p>SQL Server has a server configuration called Blocked Process Threshold. We can set a value, in milliseconds, and SQL Server will generate a report every time a process is blocked  by this amount of time.<\/p>\n<p>To configure <strong>&#8216;Blocked Process Threshold&#8217;<\/strong> we can use the following code:<\/p>\n<pre class=\"csharpcode\">exec sp_configure 'Blocked Process Threshold',5\nreconfigure\ngo<\/pre>\n<\/p>\n<p>In this example one Blocked Process report will be generated every time one process is blocked for more the five seconds. You need to adapt this value to your servers.<\/p>\n<p>There are a few ways to capture blocked process report:<\/p>\n<ul>\n<li>SQL Profiler: SQL Profiler has an event called &#8216;Blocked Process Report&#8217; especially to capture this information.<\/li>\n<li>Extended Events: XE also has a &#8216;Blocked Process Report&#8217; event.<\/li>\n<li>Alerts: It&#8217;s possible to generate an alert over blocked process reports and generate an email as alert response.<\/li>\n<\/ul>\n<p>You can create an extended events session to capture the blocked process report:<\/p>\n<pre class=\"csharpcode\">-- Create the session\nCREATE EVENT SESSION [Blocked] ON SERVER\nADD EVENT sqlserver.blocked_process_report\nADD TARGET package0.event_file\n(SET filename=N'C:\\xel\\blocked.xel') -- You need to change the path or create this folder\nGO<\/pre>\n<pre class=\"csharpcode\">-- Start the session\nALTER EVENT SESSION [Blocked]\nON SERVER\nSTATE = start;\nGO<\/pre>\n<p>After creating and starting the session, we can query the reports captured by this session. We need to use the DMF (Dynamic Management Function) <strong>sys.fn_xe_file_target_read_file<\/strong> to read the session information, but the main information is returned as XML field, so we will need to extract the information from the XML using XML functions.<\/p>\n<p>The query to retrieve the report will be this:<\/p>\n<pre class=\"csharpcode\">select theNodes.event_data.value('(\/\/blocked-process\/process)[1]\/@spid','int') as blocking_process,\n         theNodes.event_data.value('(\/\/blocked-process\/process\/inputbuf)[1]','varchar(max)') as blocking_text\n         theNodes.event_data.value('(\/\/blocked-process\/process)[1]\/@clientapp','varchar(100)') as blocking_app,\n         theNodes.event_data.value('(\/\/blocked-process\/process)[1]\/@loginname','varchar(50)') as blocking_login,\n         theNodes.event_data.value('(\/\/blocked-process\/process)[1]\/@isolationlevel','varchar(50)') as blocking_isolation,\n         theNodes.event_data.value('(\/\/blocked-process\/process)[1]\/@hostname','varchar(50)') as blocking_host,\n         theNodes.event_data.value('(\/\/blocking-process\/process)[1]\/@spid','int') as blocked_process,\n         theNodes.event_data.value('(\/\/blocking-process\/process\/inputbuf)[1]','varchar(max)') as blocked_text,\n         theNodes.event_data.value('(\/\/blocking-process\/process)[1]\/@clientapp','varchar(100)') as blocked_app,\n         theNodes.event_data.value('(\/\/blocking-process\/process)[1]\/@loginname','varchar(50)') as blocked_login,\n         theNodes.event_data.value('(\/\/blocked-process\/process)[1]\/@isolationlevel','varchar(50)') as blocked_isolation,\n         theNodes.event_data.value('(\/\/blocking-process\/process)[1]\/@hostname','varchar(50)') as blocked_host\nfrom\n         (select convert(xml,event_data) event_data\n         from\n          sys.fn_xe_file_target_read_file('c:\\xel\\blocked*.xel', NULL, NULL, NULL)) theData\n cross apply theData.event_data.nodes('\/\/event') theNodes(event_data)<\/pre>\n<p>Notice that extended events hasn&#8217;t a solution to notify about the reports. You can achieve a blocked process notification solution using an alert.<\/p>\n<p>SQL Server has a performance counter called <strong>&#8216;Process Blocked&#8217;<\/strong> that counts the number of blocked process according to <strong>&#8216;Blocked Process Threshold&#8217;<\/strong> configuration. We can create an alert over this counter and configure the notification to send an e-mail to the operator.<\/p>\n<p>The script to create the alert will be this:<\/p>\n<pre class=\"csharpcode\">-- Create the alert\nEXEC msdb.dbo.sp_add_alert @name=N'Blocked Process Alert',\n               @enabled=1,\n               @category_name=N'[Uncategorized]',\n               @performance_condition=N'General Statistics|Processes blocked||&gt;|0'\nGO\n\n-- Add one e-mail notification to one operator\nEXEC msdb.dbo.sp_add_notification @alert_name = N'Blocked Process Alert',\n  @operator_name = N'Fulano' -- You need to configure the operator first\n, @notification_method = 1;\nGO<\/pre>\n<p>RedGate SQL Monitor also has an alert called &#8216;Blocked Process&#8217; with some differences:<\/p>\n<ul>\n<li>It doesn&#8217;t requires the <strong>&#8216;Blocked Process Threshold&#8217;<\/strong> to be configured on the server<\/li>\n<li>The information is retrieved by the server that holds SQL Monitor, from several different SQL Servers<\/li>\n<li>We can do one single configuration for the alert to check several servers<\/li>\n<li>It has a notification solution, so you can receive notifications by e-mail.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Blocked processes are often a big problem to DBA&#8217;s. They are difficult to monitor. We receive desperate calls saying &#8220;Everything is slow!&#8221; and before we can do anything a second call &#8220;Oh, don&#8217;t worry, it&#8217;s everything ok again&#8221;. Most of times it happens because ill-behaved process that blocks other tasks for short periods. How to&#8230;&hellip;<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-8777","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8777","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=8777"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8777\/revisions"}],"predecessor-version":[{"id":42538,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8777\/revisions\/42538"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=8777"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=8777"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=8777"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=8777"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}