How can we help you today? How can we help you today?

SQL monitor blocking

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
0

Comments

2 comments

Sort by
  • 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
    Brian Donahue
    0
  • DBdave
    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
    0

Add comment

Please sign in to leave a comment.