InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 21:19:45 GMT20http://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspx[quote][b]Jonathan Kehayias (1/4/2012)[/b][hr]Yes, the root of the blocking could be from a previous statement in a transaction.[/quote]Thanks. I again lost hope to capture the query that caused blocking in SQL Server. However, with the Blocked Process Report, I can at least identify the transaction responsible.Thu, 05 Jan 2012 00:32:53 GMTpkrudyszhttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxYes, the root of the blocking could be from a previous statement in a transaction.Wed, 04 Jan 2012 08:22:37 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxJonathan,I've received the below for the blocked process: &lt;inputbuf&gt;Proc [Database Id = 14 Object Id = 1311948665] &lt;/inputbuf&gt; &lt;/process&gt;This is a stored procedure that doesn't touch the table that was being blocked. Could this indicate that: the blocked process report only shows the last executed query of a multi-query batch or transaction?How else can a table be blocked by a SP that has nothing to do with it?Wed, 04 Jan 2012 08:18:10 GMTpkrudyszhttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspx[quote][b]manoj.ks (8/10/2011)[/b][hr]how to read the data from blocked process threhold report? I have enabled blocked process threshold in one of my server. Can someone tell me how to do it?[/quote]Use SQL Server Profiler and select the 'Block Process Report' event. Once blocking occurs, the Profiler will show it.Wed, 04 Jan 2012 08:13:48 GMTpkrudyszhttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxhow to read the data from blocked process threhold report? I have enabled blocked process threshold in one of my server. Can someone tell me how to do it?Wed, 10 Aug 2011 06:26:34 GMTmanoj.kshttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxJonathan, What about turning the blocked process threshold off when you are done?Thu, 14 Oct 2010 10:34:57 GMTdoc_sewellhttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxJon, How about if I just want a normal blocked process report trace (without the XML). Would you have a template (or column settings and filter) you recommend?Thu, 14 Oct 2010 10:03:05 GMTdoc_sewellhttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxAnyone?Mon, 12 Apr 2010 07:49:52 GMTrafa.aborgeshttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxAny ideas?Sat, 10 Apr 2010 12:14:36 GMTrafa.aborgeshttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxThat's what I'm thinking. Since it's inside a stored procedure, it might being blocked by a previous transaction. The problem is, how can I get the exact statement that is causing the lock?Fri, 09 Apr 2010 15:49:53 GMTrafa.aborgeshttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxIf there are transactions involved locks may be retained on tables from previous statements. Even though the second spid might be selecting from table b it may still have a lock of some sort on table a if this is wrapped within a transaction.Fri, 09 Apr 2010 15:45:40 GMTdon.schaefferhttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxThanks for the reply!I already ran that script, but, as I said, the information returned is not reliable.Example: in my blocked process report, it shows that the table A is executing an update and it's blocked and in the blocking handle show that a select on table B is being executed. It doesn't make any sense to me, since table A is not related at all to table B.Is there another way to check exactly what is blocking?Fri, 09 Apr 2010 15:40:27 GMTrafa.aborgeshttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxThis script might get what you want:select WaitTime = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@waittime','varchar(20)' ), BlockingQuery = cast( textdata as xml ).value( '(/blocked-process-report/blocking-process/process/inputbuf)[1]','varchar(200)'), BlockingSpid = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/@spid','varchar(20)' ), BlockingLastBatchStarted = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/@lastbatchstarted','varchar(20)' ), BlockingSQLHandle = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/executionStack[1]/frame[1]/@sqlhandle','varchar(50)' ), BlockingStmtstart = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/executionStack[1]/frame[1]/@stmtstart','varchar(50)' ), BlockingStmtend = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/executionStack[1]/frame[1]/@stmtend','varchar(50)' ), BlockedQuery = cast( textdata as xml ).value( '(/blocked-process-report/blocked-process/process/inputbuf)[1]','varchar(200)'), BlockedSpid = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@spid','varchar(20)' ), BlockedLastBatchStarted = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@lastbatchstarted','varchar(20)' ), BlockedSQLHandle = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@sqlhandle','varchar(50)' ), BlockedStmtstart = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@stmtstart','varchar(50)' ), BlockedStmtend = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@stmtend','varchar(50)' )from dbo.Feb02 twhere eventclass = 137order by BlockedLastBatchStartedFri, 09 Apr 2010 14:05:03 GMTdon.schaefferhttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxHow reliable is the information generated in the blocked process report?I'm asking that because we let the report on for several days and when we parsed the information, we had some unusual locks, like a table being blocked by another that is not related to the blocking table.The only reliable information we had was the blocked process.Does anyone know something related to this?Thanks in advanceFri, 09 Apr 2010 13:44:26 GMTrafa.aborgeshttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxI'm monitoring the whole SQL Server environment with the blocked process report.I'm running this process in background as a job to get the informations.The problem is that it generated a lot of lines of blocked informations and I'm having issues to parse the information.Is there any script that I could get only the sqlhandle and the line of the object of the blocked and blocking process? That's all I need.Anyone can help me?ThanksWed, 31 Mar 2010 16:07:54 GMTrafa.aborgeshttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxYou would use [url=http://msdn.microsoft.com/en-us/library/ms176034.aspx]sp_trace_setstatus[/url] to remove the trace. You'd have to get the traceid from the sys.traces DMV and then you can remove the trace:[code="sql"]-- Find the TraceID for the session in sys.tracesselect * from sys.traces-- Stop and Remove TraceID 2 from the serverexec sp_trace_setstatus 2, 2[/code]Tue, 30 Mar 2010 10:01:30 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxWhich commands and in which order do I have to execute to stop/remove this trace?Tue, 30 Mar 2010 06:46:08 GMTWilfred van Dijkhttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxThanks Jonathan. This one came in handy today.Wed, 01 Apr 2009 12:20:13 GMTKenSimmonshttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxJonathan, thanks for looking at this. No, I haven't resolved it. Client_Id is an integer column; there's an index on it:CREATE NONCLUSTERED INDEX [Employees_Client_id] ON [dbo].[Employees] ( [Client_ID] ASC, [DivID] ASC, [EmpID] ASC, [Deleted] ASC)The query in question references both Client_Id and the bit column isadmin.select count(*) from employees where client_id=11343 and isadmin=1Since isadmin is not part of that index the query performs nested loop lookups of the clustered index. Here's the query plan. |--Compute Scalar(DEFINE: ([Expr1003]=CONVERT_IMPLICIT(int,[Expr1008],0))) |--Stream Aggregate(DEFINE: ([Expr1008]=Count(*))) |--Filter(WHERE: ([phoenix].[dbo].[Employees].[IsAdmin]=(1))) |--Nested Loops(Inner Join, OUTER REFERENCES: ([phoenix].[dbo].[Employees].[EmpID], [Expr1007]) WITH UNORDERED PREFETCH) |--Index Seek(OBJECT: ([phoenix].[dbo].[Employees].[Employees_Client_id]), SEEK: ([phoenix].[dbo].[Employees].[Client_ID]=(11343)) ORDERED FORWARD) |--Clustered Index Seek(OBJECT: ([phoenix].[dbo].[Employees].[PK_Employees]), SEEK: ([phoenix].[dbo].[Employees].[EmpID]=[phoenix].[dbo].[Employees].[EmpID]) LOOKUP ORDERED FORWARD)I could add isadmin as an included column in the index which might or might not resolve the issue, but more importantly I want to understand just how one select query can block another. I didn't think that could happen. What could be getting locked?Tue, 06 Jan 2009 14:44:03 GMTdon.schaefferhttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxDon,Have you resolved this, and if not, what is the table/index definition for employees? Is there an index on the clientid column and what is the datatype for that column? Your blocked on a page, so I am guessing that you get lock escalation as a scan is occuring on the clustered index to find the rows that have the respective clientid's and that a index addition to the clientid column would resolve the problem. Can you post the execution plan of one of those statements?Mon, 05 Jan 2009 12:34:15 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxSorry, I edited my post. My xml was getting eaten when I posted so I replace the close "&gt;" with right bracket "]"Fri, 05 Dec 2008 08:23:49 GMTdon.schaefferhttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxDon,Something happened to your report, but all that came through were the select statements. Can you repost the blocked process report?Fri, 05 Dec 2008 08:05:40 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxI don't know whether to always believe the blocked process report. At times I see lots of purported blocking like this, where one select statement is supposedly blocking another. As you can see, we're using the default isolation level, read committed. ( I altered the hostname and loginname fields, and had to replace the "&gt;" with "]" to get the xml to appear in this post for some reason).If nothing else, the blocked process report does not tell me how/why one query is blocking another.&lt;blocked-process-report monitorLoop="1649082"] &lt;blocked-process] &lt;process id="process38132e8" taskpriority="0" logused="0" waitresource="PAGE: 17:4:94458" waittime="87078" ownerId="13262486200" transactionname="SELECT" lasttranstarted="2008-12-03T13:23:16.110" XDES="0x4e10c8558" lockMode="S" schedulerid="2" kpid="5372" status="suspended" spid="98" sbid="0" ecid="0" priority="0" transcount="0" lastbatchstarted="2008-12-03T13:23:16.110" lastbatchcompleted="2008-12-03T13:23:16.110" clientapp=".Net SqlClient Data Provider" hostname="xxx" hostpid="7216" loginname="xxx" isolationlevel="read committed (2)" xactid="13262486200" currentdb="17" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"] &lt;executionStack] &lt;frame line="1" sqlhandle="0x020000003c2fd4171752b341f4fb2e78c4fd25a39d3268cb"/] &lt;frame line="1" sqlhandle="0x02000000272ffe37c5c986299be2ada0f87a7d86ca14ff81"/] &lt;/executionStack] &lt;inputbuf]select count(*) from employees where client_id=11343 and isadmin=1 &lt;/inputbuf] &lt;/process] &lt;/blocked-process] &lt;blocking-process] &lt;process status="suspended" waitresource="PAGE: 17:4:94458" waittime="100500" spid="161" sbid="0" ecid="0" priority="0" transcount="0" lastbatchstarted="2008-12-03T13:23:02.657" lastbatchcompleted="2008-12-03T13:23:02.657" clientapp=".Net SqlClient Data Provider" hostname="xxx" hostpid="7216" loginname="xxx" isolationlevel="read committed (2)" xactid="13262406360" currentdb="17" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"] &lt;executionStack] &lt;frame line="1" sqlhandle="0x0200000066da5526607b1b624239a36f488fec861b4ab401"/] &lt;frame line="1" sqlhandle="0x02000000b4e2201f1eda6c0a562330100b742a51c5d4d9f1"/] &lt;/executionStack] &lt;inputbuf]select count(*) from employees where client_id=34273 and isadmin=1 &lt;/inputbuf] &lt;/process] &lt;/blocking-process]&lt;/blocked-process-report]Fri, 05 Dec 2008 08:02:10 GMTdon.schaefferhttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxVery useful one ...Thu, 30 Oct 2008 09:03:01 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxThe blocked process monitor is actually piggy backing on the deadlock monitor background thread, which is always running, so impact is very minimal. This is covered in the BOL topic for the option:[url]http://msdn.microsoft.com/en-us/library/ms181150.aspx[/url]It also states that the reporting is done on a best effort basis, without guarantees for real-time or close to real time. I don't recommend that you run this, just to have it running. I only use this when I have a need to look for specific blocking, or I suspect blocking to be the cause of a performance degradation.Thu, 30 Oct 2008 07:37:39 GMTJonathan Kehayiashttp://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxThis looks like a good way to track blocking (much more proactive than the monitor &#119;indow...) but what kind of performance hit will the server take? I have some blocking issues in my production server that I need to resolve but I don't want to add to the problem by running an additional process. I may be answering my own question but I guess any blocking would put more strain on the server anyway so resolving that quickly would be more essential.Should this trace run all the time?What about cleaning up the trace file?Thanks - great article!Thu, 30 Oct 2008 07:27:32 GMTChristopher Yager-432479http://www.sqlservercentral.com/Forums/Topic594104-1365-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Blocking/64474/">Using the Blocked Process Report in SQL Server 2005/2008</A>[/B]Thu, 30 Oct 2008 00:12:54 GMTJonathan Kehayias