Handy script for use when looking at blocking

Published On: 2009-03-09By:

A while back I was looking at a clients database and we were looking at why the ASPState database was having blocking issues.  So I through this script together to show not only the blocked processes, but also the blocking processes, but also include the name of the stored procedure as well as the statement within the stored procedure which was causing the blocking.

select (select name from sys.dm_exec_sql_text(sql_handle) a join ASPState.sys.all_objects b on a.objectid = b.object_id),
(select substring(text, stmt_start/2,
((case when stmt_end = -1 then
(len(convert(nvarchar(max), text)) * 2)
else
stmt_end
end) - stmt_start) / 2) from sys.dm_exec_sql_text(sql_handle)),
*
from sys.sysprocesses
where (blocked  0 or spid in (select blocked from sys.sysprocesses))
and dbid = db_id('ASPState')

You’ll see it is a pretty basic query, but it gets the job done and gives you relevant information about the procedures. Because I’m joining to the sys.all_objects catalog view I have to specify the ASPState database, so if you want to use this you may need to change the ASPState database name to your database name you are looking at (look in the first subquery in the first line of code).

Denny


Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via