SQL monitor blocking

DBdave

SQL monitor doesn't show the lead blocker.

It catches blocks but only shows ones with a blocking process ID, it doesn't show the process that caused the start of the blocking.

If i run a big report with multiple table joins and data on our DB it will cause blocking but the query is not captured by SQL monitor only the queries which may have had a blocking knock affect caused by it.

Cheers

Dave

Brian Donahue

Hi Dave,

When I view a blocked process alert, I get the following in the "Processes" tab - it shows a blocked process and a blocking process.

Blocked process: SQLCMD
Process ID: 54
Database: DeadlockTests
Host: RGSERVER1
User: BDESA10\ESAUSER
Start time: 16 Dec 2013 11:03 AM
SQL fragment: (@1 int,@2 tinyint)UPDATE [TableA] set [id] = @1 WHERE [id]=@2


Blocking process: SQLCMD
Process ID: 53
Database: DeadlockTests
Host: RGSERVER1
User: BDESA10\ESAUSER
Start time: 16 Dec 2013 11:03 AM
SQL fragment: BEGIN TRAN
UPDATE TableA
SET id = 2
WHERE id = 1
WAITFOR delay '00:03:00'
ROLLBACK

DBdave

Brian Donahue wrote:

Hi Dave,

When I view a blocked process alert, I get the following in the "Processes" tab - it shows a blocked process and a blocking process.

Blocked process: SQLCMD
Process ID: 54
Database: DeadlockTests
Host: RGSERVER1
User: BDESA10\ESAUSER
Start time: 16 Dec 2013 11:03 AM
SQL fragment: (@1 int,@2 tinyint)UPDATE [TableA] set [id] = @1 WHERE [id]=@2


Blocking process: SQLCMD
Process ID: 53
Database: DeadlockTests
Host: RGSERVER1
User: BDESA10\ESAUSER
Start time: 16 Dec 2013 11:03 AM
SQL fragment: BEGIN TRAN
UPDATE TableA
SET id = 2
WHERE id = 1
WAITFOR delay '00:03:00'
ROLLBACK

Hi Brian, I get similar entries in my log for blocks but I've noticed that if a large select statement is run against the DB and has many table joins it can cause multiple blocks but the big query itself is not being shown as the cause of the blocking but rather an update or delete statement a step down in the chain.