Post reply

Inserts causing locking - HELP!

  • April 2, 2015 at 9:44 am

    #306448

    Hi,

    I have a production DB that all of a sudden it seems that any and every insert causes massive locks/blocks.

    If I kill the offending spid anther spids pops up with the block/lock

    HELP!

  • April 2, 2015 at 9:46 am

    #1788359

    What is the wait type you see for the blocking SPIDs?

    -- Gianluca Sartori

  • April 2, 2015 at 9:50 am

    #1788362

    LCK_M_IX

  • April 2, 2015 at 9:58 am

    #1788364

    So it looks like it's blocked by another SPID.

    If you don't have Adam Machanic sp_WhoIsActive, download it (http://sqlblog.com/files/folders/beta/entry42453.aspx) and run it with these parameters:

    EXEC sp_WhoIsActive @get_outer_command = 1, @get_plans = 1, @get_locks = 1, @find_block_leaders = 1

    You should be able to identify the blocking chain leader (blocking_session_id = NULL, blocked_session_count > 0).

    -- Gianluca Sartori

  • April 2, 2015 at 10:11 am

    #1788368

    thanks Gianluca

    but I close the spid and another lock pops right up

    what can I try next?

  • April 2, 2015 at 10:27 am

    #1788372

    Gianluca didn't suggest killing the spid, he suggested identifying it. Once you know what the lead blocker is running, you have a chance to fix the problem.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 3, 2015 at 6:18 am

    #1788484

    Rather than just killing the processes, understand what those processes are doing and why you're getting "massive" locks from them. And locking is a normal part of inserts, so it's not at all unusual to see. You're going to get locks on the table/cluster as well as any pertinent non-clustered indexes while an insert occurs.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 4, 2015 at 2:58 pm

    #1788774

    Further on the advices already posted, look at the overall "health" of the server, often it doesn't take much for issues such as the one you've described to surface simply because of an "unrelated" activity, seen this happening i.e. when simply an RDP session is opened on a tightly configured VM.

    😎

    Questions: are you using any monitoring tools? Do you have any kind of baseline/histogram? Does the server have other roles than being an SQL Server?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply