Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using the Blocked Process Report in SQL Server 2005/2008


Using the Blocked Process Report in SQL Server 2005/2008

Author
Message
Jonathan Kehayias
Jonathan Kehayias
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2116 Visits: 1807
Comments posted to this topic are about the item Using the Blocked Process Report in SQL Server 2005/2008

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Christopher Yager-432479
Christopher Yager-432479
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 12
This looks like a good way to track blocking (much more proactive than the monitor window...) 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!
Jonathan Kehayias
Jonathan Kehayias
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2116 Visits: 1807
The 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:

http://msdn.microsoft.com/en-us/library/ms181150.aspx

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.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6567 Visits: 1407
Very useful one ...



don.schaeffer
don.schaeffer
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 1061
I 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 ">" 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.

<blocked-process-report monitorLoop="1649082"]
<blocked-process]
<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"]
<executionStack]
<frame line="1" sqlhandle="0x020000003c2fd4171752b341f4fb2e78c4fd25a39d3268cb"/]
<frame line="1" sqlhandle="0x02000000272ffe37c5c986299be2ada0f87a7d86ca14ff81"/]
</executionStack]
<inputbuf]
select count(*) from employees where client_id=11343 and isadmin=1 </inputbuf]
</process]
</blocked-process]
<blocking-process]
<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"]
<executionStack]
<frame line="1" sqlhandle="0x0200000066da5526607b1b624239a36f488fec861b4ab401"/]
<frame line="1" sqlhandle="0x02000000b4e2201f1eda6c0a562330100b742a51c5d4d9f1"/]
</executionStack]
<inputbuf]
select count(*) from employees where client_id=34273 and isadmin=1 </inputbuf]
</process]
</blocking-process]
</blocked-process-report]
Jonathan Kehayias
Jonathan Kehayias
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2116 Visits: 1807
Don,

Something happened to your report, but all that came through were the select statements. Can you repost the blocked process report?

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
don.schaeffer
don.schaeffer
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 1061
Sorry, I edited my post. My xml was getting eaten when I posted so I replace the close ">" with right bracket "]"
Jonathan Kehayias
Jonathan Kehayias
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2116 Visits: 1807
Don,

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?

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
don.schaeffer
don.schaeffer
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
Points: 101 Visits: 1061
Jonathan, 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=1

Since 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?
KenSimmons
KenSimmons
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1314 Visits: 2614
Thanks Jonathan. This one came in handy today.

Ken Simmons
http://twitter.com/KenSimmons
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search