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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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