Blocked Process Report

When a lock with mutual exclusion exists on the same object, the query needs to wait a long time, can we receive a reminder from SQL Server? The answer is yes, it's very simple, because SQL Server gives you the functionality called Blocked Process report. Unfortunately, this feature is not enabled by default. So this article I want to explain to you the approximate usage of the blocked Process report, which you can use to do further troubleshooting when SQL Server locks up .

Blocked Process report itself is a simple event that you can trace through SQL Server Profiler or SQL traces. This event is called Blocked Processreport, and you can find it in the Errors and Warnings Event Group:

but that event is only highlighted (tracked) when you specify the blocked process threshold threshold through the sp_configure option at the SQL Server instance level enabled blocked When the Process report function. The parameter only receives a number from 0 to 86400, and the query must wait until the number of seconds that the lock is persisted before SQL Server generates the Blocked Process report event. By default, the value of the configuration option is 0, which is not triggered by this event. The following code sets the threshold value to 10 seconds:

1 ' blocked process threshold ' Ten 2 RECONFIGURE 3 GO

blocked Process Report , I passed a update Statement to create a new transaction inside the ADVENTUREWORKS2008R2 database:

1 BEGIN TRANSACTION 2 3 UPDATE Person.person 4 SET = ' Mr ' 5 WHERE = 1

After this statement is executed, the query now needs to obtain an exclusive lock (Exclusive Lock (X)) on a record with a column BusinessEntityID value equal to 1. In the 2nd session, I now try to read the same record. During the read, SQL Server attempts to acquire a shared lock (shared lock), which results in a blocking scenario:

1 SELECT *  from Person.person 2 WHERE = 1 3 GO

When you start SQL Server Profiler and configure the Blocked Process report event, after 10 seconds, you will see this being reported:

as you can see, the Blocked Process report itself is an XML data, so it's easy to analyze it further if you're familiar with XML and XQuery.

1 <Blocked-process-report>2  <blocked-process>3   <ProcessID= "Process35ab1c8"taskpriority= "0"logused= "0"waitresource= "key:11:72057594045333504 (8194443284a0)"waittime= "16986"ownerID= "49004"Transactionname= "Select"lasttranstarted= "2015-06-23t14:09:38.900"Xdes= "0x5f02138"Lockmode= "S"Schedulerid= "1"Kpid= "8512"Status= "Suspended"spid= " on"Sbid= "0"ecid= "0" Priority= "0"Trancount= "0"lastbatchstarted= "2015-06-23t14:09:38.900"lastbatchcompleted= "2015-06-23t14:09:10.877"lastattention= "2015-06-23t14:09:10.877"ClientApp= "Microsoft SQL Server Management Studio-Query"hostname= "Wxgfzcxxzx81-18"Hostpid= "4492"LoginName= "sa"IsolationLevel= "Read Committed (2)"Xactid= "49004"CurrentDb= "One"LockTimeout= "4294967295"Clientoption1= "671090784"Clientoption2= "390200">4    <Executionstack>5     <Frame Line= "1"Stmtstart= "+"Sqlhandle= "0x020000006063873a3a5f7e72ad0b55e66df822bf70e6f14c"/>6     <Frame Line= "1"Sqlhandle= "0x0200000066bba411d9c6966611de8194e81441d7836a9554"/>7    </Executionstack>8    <Inputbuf>9 SELECT * from Person.personTen WHERE BusinessEntityID = 1 One    </Inputbuf> A   </Process> -  </blocked-process> -  <blocking-process> the   <ProcessStatus= "Sleeping"spid= "The "Sbid= "0"ecid= "0" Priority= "0"Trancount= "3"lastbatchstarted= "2015-06-23t14:09:36.050"lastbatchcompleted= "2015-06-23t14:09:36.050"ClientApp= "Microsoft SQL Server Management Studio-Query"hostname= "Wxgfzcxxzx81-18"Hostpid= "4492"LoginName= "sa"IsolationLevel= "Read Committed (2)"Xactid= "47048"CurrentDb= "One"LockTimeout= "4294967295"Clientoption1= "671090784"Clientoption2= "390200"> -    <Executionstack/> -    <Inputbuf> - BEGIN TRANSACTION +  - UPDATE Person.person +SET Title =&apos;Mr&apos; AWHERE BusinessEntityID = 1</Inputbuf> at   </Process> -  </blocking-process> - </Blocked-process-report>

There are 2 key nodes in the XML data –<blocked-process> and <blocking-process>. The 1th –<blocked-process> describes a blocked session. Here is the session that executes the SELECT statement query against adventureworks2008r2 . The most important thing here is the XML attribute of Waitresource, which contains the lock resources waiting on the session, exceeding the blocked process threshold configuration options .

2nd <blocking-process> Describes the session that is currently holding a mutex on the resource on which the other session needs to acquire the lock. The most important thing here is the XML element of <inputbuf> , which shows the mutex required by the SQL statement. With this information on hand, it is easy to troubleshoot further, that is, why the blocking threshold is exceeded, and what to do next (the session here is an isolated transaction (orphaned transaction), killing the other session).

When you use the Blocked Process Report, the most important thing you need to remember is that SQL server values generate the corresponding XML reports for you and will not resolve locks/deadlocks for you. That is, after the Blocked Process Report is generated, the session of the SELECT statement continues to run--sql server and this session is not killed--sql server only reports that a session has exceeded Blocked Process threshold-all that remains is for you to deal with.

Blocked Process Report