Red Gate forums :: View topic - SQL monitor blocking
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Monitor 3
SQL Monitor 3 forum

SQL monitor blocking

Search in SQL Monitor 3 forum
Post new topic   Reply to topic
Jump to:  
Author Message
DBdave



Joined: 03 Oct 2013
Posts: 8

PostPosted: Wed Dec 11, 2013 3:36 pm    Post subject: SQL monitor blocking Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6677

PostPosted: Mon Dec 16, 2013 12:11 pm    Post subject: Reply with quote

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.
Quote:
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
Back to top
View user's profile Send private message
DBdave



Joined: 03 Oct 2013
Posts: 8

PostPosted: Fri Dec 27, 2013 9:38 am    Post subject: Re: Reply with quote

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.
Quote:
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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group