What's blocking

Question

Post reply

What's blocking

Nikita-395727

SSC Eights!

Points: 898
More actions
April 22, 2008 at 10:26 am

#154294

Hi,
I have script that I use to monitor blcked/blocking SPIDS. I am trying to tie this information to actual blocked resources.
We have extensive blocking going on the database because of the application that queues the requests to the database and apprently create blockings. We have over 60000 tables in the database which makes it practically impossible to use lot of GUI tools.
Blocked processes moved pretty fast too. By the time I get SPID info and try to see locks, SPID is gone.
Does anyone have any script that ties the info for blocking/blocked SPIDS to the lock it is waiting on?
or any suggestion on how to get some useful info from monitoring processes using sp-who2 or other scripts to monitor blocking?
Thanks,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

Did you try the Profiler? Remember to add filter if you decide to use it.

lm_coelho SSC Enthusiast Points: 101 More actions · Answer 2

You can try use the sp_blocker_pss80 :

http://support.microsoft.com/kb/271509

Mike Nowill SSCrazy Points: 2699 More actions · Answer 3

Check out The SQL Performance Dashboards...

http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

Silvio Moreira Santana SSC Rookie Points: 37 More actions · Answer 4

Try this script:

SELECT p.spid

,convert(char(12), d.name) db_name

, program_name

, convert(char(12), l.name) login_name

, convert(char(12), hostname) hostname

, cmd

, p.status

, p.blocked

, login_time

, last_batch

, p.spid

FROM master..sysprocesses p

JOIN master..sysdatabases d ON p.dbid = d.dbid

JOIN master..syslogins l ON p.sid = l.sid

WHERE p.blocked = 0

AND EXISTS ( SELECT 1

FROM master..sysprocesses p2

WHERE p2.blocked = p.spid )

Ola Hallengren Hall of Fame Points: 3960 More actions · Answer 5

I think that you should have a look at the 'Blocked Process Report' events.

You can define that you would like to generate a 'Blocked Process Report' if a process is blocked for more than a number of seconds.

The threshold is set with a sp_configure option.

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

The 'Blocked Process Report' contains information about the blocked and blocking process.

http://msdn2.microsoft.com/en-us/library/ms191168.aspx

Then you could capture these events using SQL Trace or Event Notifications. If you're using Event Notifications you could have the events inserted into a table.

http://msdn2.microsoft.com/en-us/library/ms190427.aspx

Ola Hallengren

http://ola.hallengren.com

don.schaeffer Ten Centuries Points: 1165 More actions · Answer 6

Does anyone have a query to extract the contents of from the blocked process report? I have thousands of blocked process report rows; I need to analyze which queries are most often blocked/blocking. Interestingly, most of the blocking occurs during the full backups.