{"id":8904,"date":"2016-05-02T19:10:31","date_gmt":"2016-05-02T19:10:31","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/how-to-find-blocked-process\/"},"modified":"2016-06-23T14:52:08","modified_gmt":"2016-06-23T14:52:08","slug":"how-to-find-blocked-process","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/other\/how-to-find-blocked-process\/","title":{"rendered":"How to find blocked processes"},"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 can we find this kind of problem?<\/p>\n<p>SQL Server has a server configuration called <strong>Blocked Process Threshold<\/strong>. We can set a value, in milliseconds, and SQL Server will generate a report every time a process is blocked\u00a0 by this amount of time.<\/p>\n<p>To configure &#8216;Blocked Process Threshold&#8217; we can use the following code:<\/p>\n<pre class=\"lang:tsql decode:true  \"> EXEC sp_configure 'Blocked Process Threshold', 5\r\n RECONFIGURE\r\n go <\/pre>\n<p>\u00a0In 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 <strong>&#8216;Blocked Process Report&#8217;<\/strong> especially to capture this information.<\/li>\n<li>Extended Events: XE also has a <strong>&#8216;Blocked Process Report&#8217;<\/strong> 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<div>\n<pre class=\"lang:tsql decode:true \">--\u00a0Create\u00a0the\u00a0session\r\nCREATE\u00a0EVENT\u00a0SESSION\u00a0[Blocked]\u00a0ON\u00a0SERVER ADD\u00a0EVENT\u00a0sqlserver.blocked_process_report ADD\u00a0TARGET\u00a0package0.event_file\r\n(SET\u00a0filename=N'C:\\xel\\blocked.xel')\u00a0--\u00a0You\u00a0need\u00a0to\u00a0change\u00a0the\u00a0path\u00a0or\u00a0create\u00a0this\u00a0folder\r\nGO\r\n\u00a0\r\n--\u00a0Start\u00a0the\u00a0session\r\nALTER\u00a0EVENT\u00a0SESSION\u00a0[Blocked]\r\nON\u00a0SERVER STATE\u00a0=\u00a0start;\r\nGO<\/pre>\n<\/div>\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) <em>sys.fn_xe_file_target_read_file<\/em> 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<div>\n<pre class=\"lang:tsql decode:true \">select \r\ntheNodes.event_data.value('(\/\/blocked-process\/process)[1]\/@spid','int') as blocking_process,\r\ntheNodes.event_data.value('(\/\/blocked-process\/process\/inputbuf)[1]','varchar(max)')\r\n                as blocking_text,\r\ntheNodes.event_data.value('(\/\/blocked-process\/process)[1]\/@clientapp','varchar(100)')\r\n                as blocking_app,\r\ntheNodes.event_data.value('(\/\/blocked-process\/process)[1]\/@loginname','varchar(50)')\r\n                as blocking_login,\r\ntheNodes.event_data.value('(\/\/blocked-process\/process)1]\/@isolationlevel','varchar(50)')\r\n                as blocking_isolation,\r\ntheNodes.event_data.value('(\/\/blocked-process\/process)[1]\/@hostname','varchar(50)')\r\n                as blocking_host,\r\ntheNodes.event_data.value('(\/\/blocking-process\/process)[1]\/@spid','int') as blocked_process, theNodes.event_data.value('(\/\/blocking-process\/process\/inputbuf)[1]','varchar(max)')\r\n                as blocked_text,\r\ntheNodes.event_data.value('(\/\/blocking-process\/process)[1]\/@clientapp','varchar(100)')\r\n                as blocked_app,\r\ntheNodes.event_data.value('(\/\/blocking-process\/process)[1]\/@loginname','varchar(50)')\r\n                as blocked_login,\r\ntheNodes.event_data.value('(\/\/blocked-process\/process)[1]\/@isolationlevel','varchar(50)')\r\n                as blocked_isolation,\r\ntheNodes.event_data.value('(\/\/blocking-process\/process)[1]\/@hostname','varchar(50)')\r\n                as blocked_host\r\nfrom\r\n(select convert(xml,event_data) event_data from sys.fn_xe_file_target_read_file('c:\\xel\\blocked*.xel', NULL, NULL, NULL)) theData cross apply theData.event_data.nodes('\/\/event') theNodes(event_data)<\/pre>\n<\/div>\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>Process Blocked<\/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<div>\n<pre class=\"lang:tsql decode:true \">--\u00a0Create\u00a0the\u00a0alert EXEC\u00a0msdb.dbo.sp_add_alert\u00a0@name=N'Blocked\u00a0Process\u00a0Alert', @enabled=1, @category_name=N'[Uncategorized]', @performance_condition=N'General\u00a0Statistics|Processes\u00a0blocked||&gt;|0'\r\nGO<\/pre>\n<\/div>\n<div>\n<p><i>&#8212;\u00a0Add\u00a0one\u00a0e-mail\u00a0notification\u00a0to\u00a0one\u00a0operator<\/i><\/p>\n<\/div>\n<div>\n<pre class=\"lang:tsql decode:true\">EXEC\u00a0msdb.dbo.sp_add_notification\u00a0@alert_name\u00a0=\u00a0N'Blocked\u00a0Process\u00a0Alert', @operator_name\u00a0=\u00a0N'Fulano'\u00a0--\u00a0You\u00a0need\u00a0to\u00a0configure\u00a0the\u00a0operator\u00a0first ,\u00a0@notification_method\u00a0=\u00a01;\r\nGO<\/pre>\n<\/div>\n<p><a href=\"http:\/\/www.red-gate.com\/products\/dba\/sql-monitor\/?utm_source=simpletalk\">Redgate SQL Monitor<\/a> also has an alert called &#8216;Blocked Process&#8217; with some differences:<\/p>\n<ul>\n<li>It doesn&#8217;t requires the &#8216;Blocked Process Threshold&#8217; 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 can&#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-8904","post","type-post","status-publish","format-standard","hentry","category-other"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8904","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=8904"}],"version-history":[{"count":19,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8904\/revisions"}],"predecessor-version":[{"id":22759,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8904\/revisions\/22759"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=8904"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=8904"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=8904"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=8904"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}