Query Stuck causing Locks, but I see SP:StmtCompleted in SQL Profiler

  • Hi,

    I am experiencing an lock issue.

    The head blocker query is the following:

    INSERT INTO DIMENSIONHIERARCHYLEVEL (DIMENSIONHIERARCHY,DIMENSIONATTRIBUTE,LEVEL_,TEMPORARYDIMENSIONHIERARCHYLEVEL,RECVERSION,PARTITION,RECID) VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7)

    Pretty simple huh ?

    In my troubleshooting, I ran SQL Profile and executed the function in the system. I can see the following eventclass

    SP:StmtCompleted

    RPC:Completed

    My understanding is that StmtCompleted indicates that a Transact-SQL statement has completed. However, I can not see the record in my table. Not seing the record makes sense because the query seems to be stuck (i don't know why), but I dont understand why i would have StmtCompleted for that query.

    What am I missing ?

    Thank you

  • Is the proc running inside a transaction issued from the application?

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • Thanks for your answer,

    I am not a developer so I have not checked the code yet.

    Do you mean if the query is being part of a transaction (ttsbegin -ttscommit) ?

    Are you thinking that the faulty transaction is not the one I see in SQL ?

    Thank you

  • from the database side you can see if there's still a lock on the object:

    SELECT s.host_name, s.login_name, s.status,

    DB_NAME(l.resource_database_id) AS database_name,

    OBJECT_SCHEMA_NAME(l.resource_associated_entity_id, l.resource_database_id) AS schema_name,

    OBJECT_NAME(l.resource_associated_entity_id, l.resource_database_id) AS object_name,

    l.request_mode, l.request_type, l.request_status, l.request_session_id, l.request_owner_type

    FROM sys.dm_tran_locks l

    INNER JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id

    WHERE l.resource_type = 'OBJECT'

  • Yes, I can see the lock.

    ObjectName : DIMENSIONHIERARCHY

    Request_mode: IS

    Request_owner_type: Transaction

    Session ID :71

    ObjectName : DIMENSIONHIERARCHY

    Request_mode: IX

    Request_owner_type: Transaction

    Session ID : 66

    ObjectName : DIMENSIONHIERARCHYLEVEL

    Request_mode: IX

    Request_owner_type: Transaction

    Session ID : 66

    Head Blocker = Session 66

    Session 71 is blocked by 66

    DBCC INPUTBUFFER (66)

    INSERT INTO DIMENSIONHIERARCHYLEVEL (DIMENSIONHIERARCHY,DIMENSIONATTRIBUTE,LEVEL_,TEMPORARYDIMENSIONHIERARCHYLEVEL,RECVERSION,PARTITION,RECID) VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7)

    I dont understand why the INSERT does not go through ??

  • The status of session 66 is SLEEPING and the cmd is AWAITING COMMAND.

    I think is is an issue in the code, I think SQL is waiting for the application to do something, a commit for example.

  • Hey guys

    I found the solution. I had to enable Snapshot Isolation on my database

    ALTER DATABASE MyDatabase

    SET ALLOW_SNAPSHOT_ISOLATION ON

    Thank you for all the answers

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

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