October 1, 2015 at 4:00 pm
I just ran into an issue with cascading locks due to a SPID on one of my production servers. When researching the lock, I noticed that there was no sql text. SP_Who 2, nor the following query captured anything,
SELECT sqltext.TEXT
, sqlplan.query_plan
, req.session_id
, req.status
, req.command
, req.cpu_time
, req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
cross apply sys.dm_exec_query_plan(plan_handle) as sqlplan
WHERE req.session_id<>@@spid;
I spoke to the user causing the lock and he ran into a visual basic error when this occurred and didn't close out that window. So my guess is that it's due to an uncommitted transaction. However, shouldn't I still see something if that was the case?
October 2, 2015 at 1:26 am
Did you do a dbcc inputbuffer(spidnumbergoeshere)? Or a DBCC OPENTRAN? Sorry if I'm stating the obvious.
October 2, 2015 at 3:54 am
joshdbguy (10/1/2015)
I just ran into an issue with cascading locks due to a SPID on one of my production servers. When researching the lock, I noticed that there was no sql text. SP_Who 2, nor the following query captured anything,
SELECT sqltext.TEXT, sqlplan.query_plan
, req.session_id
, req.status
, req.command
, req.cpu_time
, req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
cross apply sys.dm_exec_query_plan(plan_handle) as sqlplan
WHERE req.session_id<>@@spid;
I spoke to the user causing the lock and he ran into a visual basic error when this occurred and didn't close out that window. So my guess is that it's due to an uncommitted transaction. However, shouldn't I still see something if that was the case?
Quick thought, to see the spids you may want to change cross apply to outer apply
😎
SELECT sqltext.TEXT
, sqlplan.query_plan
, req.session_id
, req.status
, req.command
, req.cpu_time
, req.total_elapsed_time
FROM sys.dm_exec_requests req
OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
OUTER apply sys.dm_exec_query_plan(plan_handle) as sqlplan
WHERE req.session_id<>@@spid
AND req.status NOT IN (N'background',N'sleeping');
October 2, 2015 at 4:21 am
I would have expected to see something in the text column yes. But, maybe because it errored out there was an issue with what was stored in cache.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 2, 2015 at 4:59 pm
Thanks all, I'll add another script for outer apply. I know the request exists so I expected to see text with the cross. Interesting issue that I've never seen before.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply