SQL monitor blocking

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

SQL monitor blocking

Postby DBdave » Wed Dec 11, 2013 3:36 pm

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
DBdave
 
Posts: 8
Joined: Thu Oct 03, 2013 11:21 am

Postby Brian Donahue » Mon Dec 16, 2013 12:11 pm

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
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Re:

Postby DBdave » Fri Dec 27, 2013 9:38 am

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.
DBdave
 
Posts: 8
Joined: Thu Oct 03, 2013 11:21 am


Return to SQL Monitor 3

Who is online

Users browsing this forum: No registered users and 0 guests