SQL monitor blocking

DBdaveDBdave Posts: 14 Bronze 1
edited December 27, 2013 3:38AM in SQL Monitor Previous Versions
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

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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
  • DBdaveDBdave Posts: 14 Bronze 1
    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.
Sign In or Register to comment.