{"id":4392,"date":"2015-03-08T19:06:00","date_gmt":"2015-03-08T18:06:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/"},"modified":"2015-03-08T19:06:00","modified_gmt":"2015-03-08T18:06:00","slug":"analyzing-easily-the-blocked-processes-report","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/","title":{"rendered":"Analyzing easily the blocked process report"},"content":{"rendered":"

Which DBA has not yet face a performance problem issued by several blocked processes? In reality, I\u2019m sure a very little number of them. Troubleshooting a blocked issue scenario is not always easy and may require to use some useful tools to simplify this hard task. A couple of months ago, I had to deal this scenario at one of my customer. During some specific periods in the business day, he noticed that its application slowed down and he asked to me how to solve this issue.<\/p>\n

Fortunately, SQL Server provides a useful feature to catch blocked processes. We have just to configure the \u201cblocked process threshold (s)\u201d server option. There are plenty of blogs that explain how to play with this parameter. So I let you perform your own investigation by using your favourite search engine.<\/p>\n

Having a blocked process report is useful but often in such situation, there are a lot of processes that sometimes blocked each other\u2019s and we have to find out among this can of worms the real responsible. So, my main concern was the following: how to extract information from the blocked process report and how to correlate all blocked processes together. After some investigation I found a useful script written by Michael J S Swart here<\/a>. Usually I prefer to write my own script but I didn’t had the time and I had to admit this script met perfectly my need. The original version provides the blocked hierarchy and the XML view of the issue. It\u2019s not so bad because we have all the information to troubleshoot our issue. However, my modification consists to change this XM view by adding useful information in tabular format to make the reading of the final result easier. Here the modified version of the script:<\/p>\n

use AdventureWorks2012;\ngo\n\nif exists (select 1 from sys.procedures where name = N'sp_blocked_process_report_viewer_dbi')\n\tdrop procedure [dbo].[sp_blocked_process_report_viewer_dbi];\ngo\n\nCREATE PROCEDURE [dbo].[sp_blocked_process_report_viewer_dbi]\n(\n       @Trace nvarchar(max),\n       @Type varchar(10) = 'FILE'\n)\n \nAS\n \nSET NOCOUNT ON\n \n-- Validate @Type\nIF (@Type NOT IN('FILE', 'TABLE', 'XMLFILE'))\n       RAISERROR ('The @Type parameter must be ''FILE'', ''TABLE'' or ''XMLFILE''', 11, 1)\n \nIF (@Trace LIKE '%.trc' AND @Type <> 'FILE')\n       RAISERROR ('Warning: You specified a .trc trace. You should also specify @Type = ''FILE''', 10, 1)\n \nIF (@Trace LIKE '%.xml' AND @Type <> 'XMLFILE')\n       RAISERROR ('Warning: You specified a .xml trace. You should also specify @Type = ''XMLFILE''', 10, 1)\n    \n \nCREATE TABLE #ReportsXML\n(\n       monitorloop nvarchar(100) NOT NULL,\n       endTime datetime NULL,\n       blocking_spid INT NOT NULL,\n       blocking_ecid INT NOT NULL,\n       blocking_bfinput NVARCHAR(MAX),\n       blocked_spid INT NOT NULL,\n       blocked_ecid INT NOT NULL,\n       blocked_bfinput NVARCHAR(MAX),\n       blocked_waitime BIGINT,\n       blocked_hierarchy_string as CAST(blocked_spid as varchar(20)) + '.' + CAST(blocked_ecid as varchar(20)) + '\/',\n       blocking_hierarchy_string as CAST(blocking_spid as varchar(20)) + '.' + CAST(blocking_ecid as varchar(20)) + '\/',\n       bpReportXml xml not null,\n       primary key clustered (monitorloop, blocked_spid, blocked_ecid),\n       unique nonclustered (monitorloop, blocking_spid, blocking_ecid, blocked_spid, blocked_ecid)\n)\n \nDECLARE @SQL NVARCHAR(max);\nDECLARE @TableSource nvarchar(max);\n \n-- define source for table\nIF (@Type = 'TABLE')\nBEGIN\n       -- everything input by users get quoted\n       SET @TableSource = ISNULL(QUOTENAME(PARSENAME(@Trace,4)) + N'.', '')\n             + ISNULL(QUOTENAME(PARSENAME(@Trace,3)) + N'.', '')\n             + ISNULL(QUOTENAME(PARSENAME(@Trace,2)) + N'.', '')\n             + QUOTENAME(PARSENAME(@Trace,1));\nEND\n \n-- define source for trc file\nIF (@Type = 'FILE')\nBEGIN\n       SET @TableSource = N'sys.fn_trace_gettable(N' + QUOTENAME(@Trace, '''') + ', -1)';\nEND\n \n-- load table or file\nIF (@Type IN('TABLE', 'FILE'))\nBEGIN\n       SET @SQL = N'    \n             INSERT #ReportsXML(blocked_ecid, blocked_spid, blocked_bfinput , blocking_ecid, blocking_spid,\n                                blocking_bfinput, blocked_waitime, monitorloop, bpReportXml,endTime)\n             SELECT\n                    blocked_ecid,\n                    blocked_spid,\n                    blocked_inputbuffer,\n                    blocking_ecid,\n                    blocking_spid,\n                    blocking_inputbuffer,\n                blocked_waitime,\n                    COALESCE(monitorloop, CONVERT(nvarchar(100), endTime, 120), ''unknown''),\n                    bpReportXml,\n                    EndTime\n             FROM ' + @TableSource + N'\n             CROSS APPLY (\n                    SELECT CAST(TextData as xml)\n                    ) AS bpReports(bpReportXml)\n             CROSS APPLY (\n                    SELECT\nmonitorloop = bpReportXml.value(''(\/\/@monitorLoop)[1]'', ''nvarchar(100)''),\nblocked_spid = bpReportXml.value(''(\/blocked-process-report\/blocked-process\/process\/@spid)[1]'', ''int''),\nblocked_ecid = bpReportXml.value(''(\/blocked-process-report\/blocked-process\/process\/@ecid)[1]'', ''int''),\n                           blocked_inputbuffer = bpReportXml.value(''(\/blocked-process-report\/blocked-process\/process\/inputbuf\/text())[1]'', ''nvarchar(max)''),\nblocking_spid = bpReportXml.value(''(\/blocked-process-report\/blocking-process\/process\/@spid)[1]'', ''int''),\nblocking_ecid = bpReportXml.value(''(\/blocked-process-report\/blocking-process\/process\/@ecid)[1]'', ''int''),\n                           blocking_inputbuffer = bpReportXml.value(''(\/blocked-process-report\/blocking-process\/process\/inputbuf\/text())[1]'', ''nvarchar(max)''),\nblocked_waitime = bpReportXml.value(''(\/blocked-process-report\/blocked-process\/process\/@waittime)[1]'', ''bigint'')\n                    ) AS bpShredded\n             WHERE EventClass = 137';\n          \n       EXEC (@SQL);\nEND\n \nIF (@Type = 'XMLFILE')\nBEGIN\n       CREATE TABLE #TraceXML(\n             id int identity primary key,\n             ReportXML xml NOT NULL  \n       )\n    \n       SET @SQL = N'\n             INSERT #TraceXML(ReportXML)\n             SELECT col FROM OPENROWSET (\n                           BULK ' + QUOTENAME(@Trace, '''') + N', SINGLE_BLOB\n                    ) as xmldata(col)';\n \n       EXEC (@SQL);\n    \n       CREATE PRIMARY XML INDEX PXML_TraceXML ON #TraceXML(ReportXML);\n \n       WITH XMLNAMESPACES\n       (\n             'http:\/\/tempuri.org\/TracePersistence.xsd' AS MY\n       ),\n       ShreddedWheat AS\n       (\n             SELECT\n                    bpShredded.blocked_ecid,\n                    bpShredded.blocked_spid,\n                    bpShredded.blocked_inputbuffer,\n                    bpShredded.blocked_waitime,\n                    bpShredded.blocking_ecid,\n                    bpShredded.blocking_spid,\n                    bpShredded.blocking_inputbuffer,\n                    bpShredded.monitorloop,\n                    bpReports.bpReportXml,\n                    bpReports.bpReportEndTime\n             FROM #TraceXML\n             CROSS APPLY\n                    ReportXML.nodes('\/MY:TraceData\/MY:Events\/MY:Event[@name=\"Blocked process report\"]')\n                    AS eventNodes(eventNode)\n             CROSS APPLY\n                    eventNode.nodes('.\/MY:Column[@name=\"EndTime\"]')\n                    AS endTimeNodes(endTimeNode)\n             CROSS APPLY\n                    eventNode.nodes('.\/MY:Column[@name=\"TextData\"]')\n                    AS bpNodes(bpNode)\n             CROSS APPLY(\n                    SELECT CAST(bpNode.value('(.\/text())[1]', 'nvarchar(max)') as xml),\n                           CAST(LEFT(endTimeNode.value('(.\/text())[1]', 'varchar(max)'), 19) as datetime)\n             ) AS bpReports(bpReportXml, bpReportEndTime)\n             CROSS APPLY(\n                    SELECT\n                           monitorloop = bpReportXml.value('(\/\/@monitorLoop)[1]', 'nvarchar(100)'),\n                           blocked_spid = bpReportXml.value('(\/blocked-process-report\/blocked-process\/process\/@spid)[1]', 'int'),\n                           blocked_ecid = bpReportXml.value('(\/blocked-process-report\/blocked-process\/process\/@ecid)[1]', 'int'),\n                           blocked_inputbuffer = bpReportXml.value('(\/blocked-process-report\/blocked-process\/process\/inputbuf\/text())[1]', 'nvarchar(max)'),\n                           blocking_spid = bpReportXml.value('(\/blocked-process-report\/blocking-process\/process\/@spid)[1]', 'int'),\n                           blocking_ecid = bpReportXml.value('(\/blocked-process-report\/blocking-process\/process\/@ecid)[1]', 'int'),\n                           blocking_inputbuffer = bpReportXml.value('(\/blocked-process-report\/blocking-process\/process\/inputbuf\/text())[1]', 'nvarchar(max)'),\n                           blocked_waitime = bpReportXml.value('(\/blocked-process-report\/blocked-process\/process\/@waittime)[1]', 'bigint')\n             ) AS bpShredded\n       )\n       INSERT #ReportsXML(blocked_ecid,blocked_spid,blocking_ecid,blocking_spid,\n             monitorloop,bpReportXml,endTime)\n       SELECT blocked_ecid,blocked_spid,blocking_ecid,blocking_spid,\n             COALESCE(monitorloop, CONVERT(nvarchar(100), bpReportEndTime, 120), 'unknown'),\n             bpReportXml,bpReportEndTime\n       FROM ShreddedWheat;\n    \n       DROP TABLE #TraceXML\n \nEND\n \n-- Organize and select blocked process reports\n;WITH Blockheads AS\n(\n       SELECT blocking_spid, blocking_ecid, monitorloop, blocking_hierarchy_string\n       FROM #ReportsXML\n       EXCEPT\n       SELECT blocked_spid, blocked_ecid, monitorloop, blocked_hierarchy_string\n       FROM #ReportsXML\n),\nHierarchy AS\n(\n       SELECT monitorloop, blocking_spid as spid, blocking_ecid as ecid,\n             cast('\/' + blocking_hierarchy_string as varchar(max)) as chain,\n             0 as level\n       FROM Blockheads\n    \n       UNION ALL\n    \n       SELECT irx.monitorloop, irx.blocked_spid, irx.blocked_ecid,\n             cast(h.chain + irx.blocked_hierarchy_string as varchar(max)),\n             h.level+1\n       FROM #ReportsXML irx\n       JOIN Hierarchy h\n             ON irx.monitorloop = h.monitorloop\n             AND irx.blocking_spid = h.spid\n             AND irx.blocking_ecid = h.ecid\n)\nSELECT\n       ISNULL(CONVERT(nvarchar(30), irx.endTime, 120),\n             'Lead') as traceTime,\n       SPACE(4 * h.level)\n             + CAST(h.spid as varchar(20))\n             + CASE h.ecid\n                    WHEN 0 THEN ''\n                    ELSE '(' + CAST(h.ecid as varchar(20)) + ')'\n             END AS blockingTree,\n       irx.blocked_waitime,\n       bdp.last_trans_started as blocked_last_trans_started,\n       bdp.wait_resource AS blocked_wait_resource,\n       bgp.wait_resource AS blocking_wait_resource,\n       bgp.[status] AS blocked_status,\n       bdp.[status] AS blocking_status,\n       bdp.lock_mode AS blocked_lock_mode,\n       bdp.isolation_level as blocked_isolation_level,\n       bgp.isolation_level as blocking_isolation_level,\n       bdp.app AS blocked_app,\n       DB_NAME(bdp.current_db) AS blocked_db,\n       '-----> blocked statement' AS blocked_section,\n       CAST('' + irx.blocked_bfinput + '' AS XML) AS blocked_input_buffer,\n       CASE\n             WHEN bdp.frame_blocked_process_xml IS NULL THEN CAST('' + irx.blocked_bfinput + '' AS XML)\n             ELSE bdp.frame_blocked_process_xml\n       END AS frame_blocked_process_xml,\n       DB_NAME(bgp.current_db) AS blocking_db,\n       bgp.app AS blocking_app,\n       'blocking statement ----->' AS blocking_section,\n       CAST('' + irx.blocking_bfinput + '' AS XML) AS blocking_input_buffer,\n       CASE\n             WHEN bgp.frame_blocking_process_xml IS NULL THEN CAST('' + irx.blocking_bfinput + '' AS XML)\n             ELSE bgp.frame_blocking_process_xml\n       END AS frame_blocking_process_xml,\n       irx.bpReportXml\nfrom Hierarchy h\nleft join #ReportsXML irx\n       on irx.monitorloop = h.monitorloop\n       and irx.blocked_spid = h.spid\n       and irx.blocked_ecid = h.ecid\nouter apply\n(\n       select\n             T.x.value('(.\/process\/@waitresource)[1]', 'nvarchar(256)') AS wait_resource,\n             T.x.value('(.\/process\/@lasttranstarted)[1]', 'datetime') as last_trans_started,\n             T.x.value('(.\/process\/@lockMode)[1]', 'nvarchar(60)') as lock_mode,\n             T.x.value('(.\/process\/@status)[1]', 'nvarchar(60)') as [status],\n             T.x.value('(.\/process\/@isolationlevel)[1]', 'nvarchar(60)') as isolation_level,\n             T.x.value('(.\/process\/@currentdb)[1]', 'int') as current_db,\n             T.x.value('(.\/process\/@clientapp)[1]', 'nvarchar(200)') as app,\n             cast(\n             (select SUBSTRING(txt.text,(ISNULL(T.x.value('.\/@stmtstart', 'int'), 0) \/ 2) + 1,\n                           ((CASE ISNULL(T.x.value('.\/@stmtend', 'int'), -1)\n                                  WHEN -1 THEN DATALENGTH(txt.text)\n                                  ELSE T.x.value('.\/@stmtend', 'int')\n                              END - ISNULL(T.x.value('.\/@stmtstart', 'int'), 0)) \/ 2) + 1) + CHAR(13) AS statement_txt\n                       from bpReportXml.nodes('\/\/blocked-process\/process\/executionStack\/frame') AS T(x)\n                       cross apply sys.dm_exec_sql_text(T.x.value('xs:hexBinary(substring((.\/@sqlhandle), 3))', 'varbinary(max)')) AS txt\n                       for XML path('')) as xml) AS frame_blocked_process_xml\n       from bpReportXml.nodes('\/\/blocked-process') AS T(x)\n) AS bdp\nouter apply\n(\n       select\n             T.x.value('(.\/process\/@waitresource)[1]', 'nvarchar(256)') AS wait_resource,\n             T.x.value('(.\/process\/@status)[1]', 'nvarchar(60)') as [status],\n             T.x.value('(.\/process\/@isolationlevel)[1]', 'nvarchar(60)') as isolation_level,\n             T.x.value('(.\/process\/@currentdb)[1]', 'int') as current_db,\n             T.x.value('(.\/process\/@clientapp)[1]', 'nvarchar(200)') as app,\n             cast(\n             (select SUBSTRING(txt.text,(ISNULL(T.x.value('.\/@stmtstart', 'int'), 0) \/ 2) + 1,\n                           ((CASE ISNULL(T.x.value('.\/@stmtend', 'int'), -1)\n                                  WHEN -1 THEN DATALENGTH(txt.text)\n                                  ELSE T.x.value('.\/@stmtend', 'int')\n                              END - ISNULL(T.x.value('.\/@stmtstart', 'int'), 0)) \/ 2) + 1) + CHAR(13) AS statement_txt\n                       from bpReportXml.nodes('\/\/blocking-process\/process\/executionStack\/frame') AS T(x)\n                       cross apply sys.dm_exec_sql_text(T.x.value('xs:hexBinary(substring((.\/@sqlhandle), 3))', 'varbinary(max)')) AS txt\n                       for XML path('')) as xml) AS frame_blocking_process_xml\n             from bpReportXml.nodes('\/\/blocking-process') AS T(x)\n) AS bgp\norder by h.monitorloop, h.chain\n \nDROP TABLE #ReportsXML<\/pre>\n
Unfortunately I can\u2019t show my customer context so I will show only a sample of my own test to explain how we can use this script. In fact, the generated result set is splitted into three main sections.<\/p>\n
First section: Hierarchy blocked tree, lock resources and transaction isolation level<\/h3>\n
\"blog_33_-_1_-_result_lock_section\"<\/p>\n
Let\u2019s begin by the first category. You can see here the hierarchy tree and the blocked interactions that exist between the different processes. The above picture shows the process id = 72 that is blocking the process id = 73. In turn, the process = 73 is blocking other sessions (with id = 75, 77). Furthermore, the process 74 is at the same level than the process id = 73 and it is blocked by the process id = 72. Finally the process id = 76 is blocked by the process id = 74. A real can of worms isn\u2019t it?<\/p>\n
Displaying the blocking hierarchy tree is very useful in this case. In addition, I added the transaction isolation level used by all processes, the status of the processes, the locks and the resources related to the issue. As a reminder, these information are already in the blocked processes report and my task consisted in extracting these information in tabular format. We will use all of them later in this blog post. For the moment, let\u2019s focus on the first hierarchy branch: 72 -> 73 -> 75 -> 77 and the resource that all concerned processes are hitting:<\/p>\n
KEY: 6:72057594045595648 (089241b7b846)<\/strong> that we can split in three main parts<\/p>\n
6<\/strong> : Database id = 6 => AdventureWorks2012<\/p>\n
72057594045595648<\/strong> : The container hobt id of the partition that give us the schema, table and index as follows:<\/p>\n
select<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 s.name as [schema_name],<\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 o.name as table_name,<\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i.name as index_name<\/span><\/span><\/span><\/div>\n
from sys.partitions as p<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
join sys.objects as o<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 on p.object_id = o.object_id<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
join sys.indexes as i<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 on i.object_id = p.object_id<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 and i.index_id = p.index_id<\/span><\/span><\/span><\/span><\/span><\/div>\n
join sys.schemas as s<\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 on s.schema_id = o.schema_id<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
where p.hobt_id = 72057594045595648<\/span><\/span><\/span><\/span><\/div>\n
\"blog_33_-_2_-_partition\"<\/p>\n
Person.Person.PK_Person_BusinessEntityID<\/strong> is a clustered index that includes the BusinessEntityID column.<\/p>\n
(089241b7b846) :<\/strong><\/p>\n
The lock resource value that identifies the index key in the table Person.Person locked by the process id = 72. We may use the undocumented function %%lockres%% to locate the correct row in the table as follows:<\/p>\n
select<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BusinessEntityID<\/span><\/div>\n
from Person.Person<\/span><\/span><\/span><\/div>\n
where %%lockres%% = ‘(089241b7b846)’<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\"blog_33_-_3_-_lockres\"<\/p>\n
At this point we know that the blocking process has started a transaction in repeatable read transaction isolation level and has not yet released the lock on the index key with value 14. This is why the session id = 73 is still pending because it attempts to access to the same resource by putting an S lock.<\/p>\n
Let\u2019s continue with the next sections of the result set:<\/p>\n
Second section: blocking and blocked input buffers and their related frames<\/h3>\n
This second part provides detailed information of blocked statement information including the concerned application and the concerned databases as well.<\/p>\n
\"blog_33_-_4_-_blocked_session_section\"<\/p>\n
Likewise, the last part provides the same kind of information but for the blocking statement(s):<\/p>\n
\"blog_33_-_5_-_blocking_session_section\"<\/p>\n
We will correlate the information of the above sections. For example, if we take a look directly at the blocking input buffer of the process id = 72 we will discover the responsible that is the following stored procedure:<\/p>\n
<blockingInputBuffer><\/span><\/span><\/span><\/div>\n
EXECTestUpdatePersonNameStyle@NameStyle<\/a><\/span><\/span><\/div>\n
&lt;\/blockingInputBuffer&gt;<\/span><\/span><\/span><\/span><\/span><\/div>\n
Next, the blocking frame identifies exactly the portion of code inside the stored procedure where the blocking issue has occurred:<\/p>\n
WAITFOR DELAY ’00:02:00′;<\/p>\n
Ok it seems that the stored procedure has started an explicit transaction with the repeatable read transaction isolation level and includes a WAITFOR DELAY command with a duration of 2 minutes. During this time, the different resources are still holding by the transaction because there is no transaction commit or transaction rollback and we are in repeatable read transaction isolation level. Let\u2019s take a look at the stored procedure code:<\/p>\n
ALTER PROCEDURE [dbo].[TestUpdatePersonNameStyle]<\/span><\/span><\/span><\/span><\/div>\n
(<\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @NameStyle BIT,<\/span><\/span><\/span><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @BusinessEntityID INT<\/span><\/span><\/div>\n
)<\/span><\/div>\n
AS<\/span><\/div>\n
\u00a0<\/span><\/div>\n
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0<\/span><\/div>\n
BEGIN TRAN<\/span><\/span><\/span><\/div>\n
\u00a0<\/span><\/div>\n
SELECT PhoneNumber<\/span><\/span><\/div>\n
FROM Person.PersonPhone<\/span><\/span><\/span><\/div>\n
WHERE BusinessEntityID = @BusinessEntityID;<\/span><\/span><\/span><\/span><\/div>\n
\u00a0<\/span><\/div>\n
UPDATE Person.Person <\/span><\/span><\/span><\/div>\n
SET NameStyle = @NameStyle <\/span><\/span><\/span><\/div>\n
WHERE BusinessEntityID = @BusinessEntityID + 100; <\/span><\/span><\/span><\/span><\/div>\n
\u00a0<\/span><\/div>\n
WAITFOR DELAY ’00:02:00′;<\/span><\/span><\/span><\/span><\/span><\/div>\n
\u00a0<\/span><\/div>\n
ROLLBACK TRAN;<\/span><\/span><\/span><\/span><\/div>\n
We can confirm that we found in the first section, the repeatable read transaction isolation level used by the blocking session. In reality, it seems that we have two different resources holding by the above transaction. The first (index key = 14) and the second (index key = 14 + 100).<\/p>\n
Now let\u2019s switch to the blocked statement part. A quick look at the input buffer tells us that the session id = 73 is trying to access the same resource than the UPDATE part of the blocking process. It confirms what we saw in the first section: the process id = 73 is in suspended state because it is trying to put a S lock on the concerned resource that is not compatible with an X lock from the UPDATE statement of the process id = 72.<\/p>\n
SELECT * FROM Person.Person WHERE BusinessEntityID = 114;<\/p>\n
I will do not the same demonstration for all the lines in the result set but let\u2019s finish by the process id = 74. Let\u2019s go back to the first section. We can see that session id = 74 is trying to put an X lock on the following resource:<\/p>\n
KEY: 6:72057594045726720 (58e9f9de4ab6)<\/strong><\/p>\n
Let\u2019s apply the same rule that earlier and we may easily find the corresponding index key on the table Person.PersonPhone<\/strong> this time.<\/p>\n
\"blog_33_-_6_-_lockres\"<\/p>\n
Now let\u2019s continue to the next sections and let\u2019s take a look at the blocking frame:<\/p>\n
WAITFOR DELAY ’00:02:00′;<\/p>\n
The same thing that the first case\u2026. Finally let\u2019s take a look at the blocking input buffer:<\/p>\n
<\/div>\n
BEGIN TRAN; <\/span><\/span><\/div>\n
IF EXISTS(SELECT 1 FROM Person.Person WHERE BusinessEntityID = 14) <\/span><\/span><\/span><\/span><\/span><\/span><\/div>\n
DELETE FROM Person.PersonPhone WHERE BusinessEntityID = 14; <\/span><\/span><\/span><\/span><\/div>\n
ROLLBACK TRAN;\u00a0\u00a0 <\/span><\/span><\/div>\n
<\/div>\n
This time, it concerns an explicit transaction but with a different transaction isolation level: read committed mode. You can correlate with the first section by yourself. The blocking point concerns only the second part of the above query as indicated by the blocked_lock column in the first section: The process id = 74 is trying to put an X lock on a resource that is still holding by the process id = 72 (SELECT statement in repeatable read transaction isolation level).<\/p>\n
The issue that I faced with my customer was pretty similar. In fact you have just to replace the WAITFOR DELAY command by a series of other pieces of code which deferred drastically the transaction commit time. In this case, having a precise idea of the blocking tree and the other information readable directly on a tabular format helped us to save a lot of time in order to resolve this issue.<\/p>\n
Happy troubleshooting!<\/p>\n
By David Barbarin<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
Which DBA has not yet face a performance problem issued by several blocked processes? In reality, I\u2019m sure a very little number of them. Troubleshooting a blocked issue scenario is not always easy and may require to use some useful tools to simplify this hard task. A couple of months ago, I had to deal […]<\/p>\n","protected":false},"author":26,"featured_media":4393,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[197],"tags":[542,67,51],"type_dbi":[],"class_list":["post-4392","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-application-integration-middleware","tag-blocked-processes","tag-performance","tag-sql-server"],"acf":[],"yoast_head":"\nAnalyzing easily the blocked process report - dbi Blog<\/title>\n<meta name=\"description\" content=\"Analyzing easily the blocked processes report\" \/>\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.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Analyzing easily the blocked process report\" \/>\n<meta property=\"og:description\" content=\"Analyzing easily the blocked processes report\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-03-08T18:06:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_33_-_1_-_result_lock_section.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1306\" \/>\n\t<meta property=\"og:image:height\" content=\"131\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Microsoft Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Microsoft Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"14 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/\"},\"author\":{\"name\":\"Microsoft Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\"},\"headline\":\"Analyzing easily the blocked process report\",\"datePublished\":\"2015-03-08T18:06:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/\"},\"wordCount\":1413,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_33_-_1_-_result_lock_section.jpg\",\"keywords\":[\"blocked processes\",\"Performance\",\"SQL Server\"],\"articleSection\":[\"Application integration & Middleware\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/\",\"name\":\"Analyzing easily the blocked process report - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_33_-_1_-_result_lock_section.jpg\",\"datePublished\":\"2015-03-08T18:06:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\"},\"description\":\"Analyzing easily the blocked processes report\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_33_-_1_-_result_lock_section.jpg\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_33_-_1_-_result_lock_section.jpg\",\"width\":1306,\"height\":131},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Analyzing easily the blocked process report\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\",\"name\":\"Microsoft Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"caption\":\"Microsoft Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Analyzing easily the blocked process report - dbi Blog","description":"Analyzing easily the blocked processes report","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.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/","og_locale":"en_US","og_type":"article","og_title":"Analyzing easily the blocked process report","og_description":"Analyzing easily the blocked processes report","og_url":"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/","og_site_name":"dbi Blog","article_published_time":"2015-03-08T18:06:00+00:00","og_image":[{"width":1306,"height":131,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_33_-_1_-_result_lock_section.jpg","type":"image\/jpeg"}],"author":"Microsoft Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Microsoft Team","Est. reading time":"14 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/"},"author":{"name":"Microsoft Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"headline":"Analyzing easily the blocked process report","datePublished":"2015-03-08T18:06:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/"},"wordCount":1413,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_33_-_1_-_result_lock_section.jpg","keywords":["blocked processes","Performance","SQL Server"],"articleSection":["Application integration & Middleware"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/","url":"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/","name":"Analyzing easily the blocked process report - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_33_-_1_-_result_lock_section.jpg","datePublished":"2015-03-08T18:06:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"description":"Analyzing easily the blocked processes report","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_33_-_1_-_result_lock_section.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/blog_33_-_1_-_result_lock_section.jpg","width":1306,"height":131},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/analyzing-easily-the-blocked-processes-report\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Analyzing easily the blocked process report"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4","name":"Microsoft Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","caption":"Microsoft Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4392","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/26"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=4392"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4392\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/4393"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4392"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4392"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4392"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=4392"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}