July 12, 2016 at 4:17 am
Hello,
I have a long running SPID on a database. It's from a script of many SQLs all glued together in one bundle.
The activity monitor shows "Create Index" in the SQL command column.
When doing a SQL trace on that SPID I am getting only this (below) and not showing the full SQL part it's running now. I want to know which Index stage it's stuck on. There are so many in the script that it will take some time to drill down to that table/index.
Any thoughts/help please?
Thank you,
Vin
-- network protocol: TCP/IP
set quoted_identifier on
set arithabort on
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
July 12, 2016 at 5:04 am
Something like this?SELECT
c.session_id
,t.text
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
John
July 12, 2016 at 5:31 am
Thank you John for that.
But that shows the whole of the SQL script from start to end. Which I can get the same from the file itself.
I thought the trace is meant to show what part of the SQL it's running at the current moment. But instead it's only sowing the network protocol line.
regards
Vin
July 12, 2016 at 5:58 am
Vin
This might work. It should if you're running a statement(s) that doesn't already have a cached plan. I'm not sure what will happen if you're running one that does. (Credit to this site[/url] for the SUBSTRING part that pulls out the correct part of the query text.)
WITH SessionData AS (
SELECT
c.session_id
,t.text
,s.statement_start_offset
,s.statement_end_offset
,ROW_NUMBER() OVER (PARTITION BY c.session_id ORDER BY s.statement_start_offset DESC) AS RowNo
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
JOIN sys.dm_exec_query_stats s ON c.most_recent_sql_handle = s.sql_handle
)
SELECT
session_id
,SUBSTRING(
text
,(statement_start_offset/2)+1,
((
CASE statement_end_offset
WHEN -1 THEN DATALENGTH(text)
ELSE statement_end_offset
END
- statement_start_offset)/2)+1
)
FROM SessionData
WHERE RowNo = 1
John
July 12, 2016 at 8:23 am
John,
It kind of worked, although it came close to the part of the script which was being run or about completed.
Much better than showing the whole script.
By the time I pinpointed down to the actual table being inserted, the script completed.
So it's a beautiful script which can show closer to action SQL.
Credit to you and ref. link you posted.
many thanks,
Vin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply