How can I see SQL query that sp_execute is running

Question

Viewing 4 posts - 1 through 3 (of 3 total)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Jefy,

Please try the following post:

Welsh

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

jefydominic Mr or Mrs. 500 Points: 578 More actions · Answer 2

Welsh,

I have read that posting.

I have tried to get query text of execution completed process.

select text

from sys.dm_exec_sql_text(most_recent_sql_handle)

where session_id = 1732

but i am getting following error.

Msg 207, Level 16, State 1, Line 1

Invalid column name 'most_recent_sql_handle'.

Msg 207, Level 16, State 1, Line 1

Invalid column name 'session_id'.

how can i correct it?

Thank you

Jefy

jefydominic Mr or Mrs. 500 Points: 578 More actions · Answer 3

I have got one query from MSDN site to get CPU time and query text all process.

SELECT total_worker_time/execution_count AS [Avg CPU Time],

SUBSTRING(st.text, (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2) + 1) AS statement_text

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

Thank you

Jefy