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

Question

Post reply

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

Gamleur84

Default port

Points: 1475
More actions
October 26, 2016 at 7:07 pm

#314434

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

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

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

MadAdmin SSChampion Points: 11261 More actions · Answer 1

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

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

Gamleur84 Default port Points: 1475 More actions · Answer 2

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

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'

Gamleur84 Default port Points: 1475 More actions · Answer 4

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 ??

Gamleur84 Default port Points: 1475 More actions · Answer 5

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.

Gamleur84 Default port Points: 1475 More actions · Answer 6

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