July 22, 2011 at 8:26 am
I'm using the following query to capture currently executing long-time queries on each database instance.
A filter can be optionally added to list only those queries with total_elapsed_time value greater than a certain threshold.
Any comments/suggestions on how it could be improved would be appreciated. I'm aware that sys.dm_exec_requests can be combined with sys.dm_exec_query_stats to extract information on queries stored in the cache, but I am only interested in currently executing queries for something I'm working on.
Thanks for any input!
SELECT
r.session_id
, r.start_time
, TotalElapsedTime_ms = r.total_elapsed_time
, r.[status]
, r.command
, DatabaseName = DB_Name(r.database_id)
, r.wait_type
, r.last_wait_type
, r.wait_resource
, r.cpu_time
, r.reads
, r.writes
, r.logical_reads
, t.[text] AS [executing batch]
, SUBSTRING(
t.[text], r.statement_start_offset / 2,
( CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH (t.[text])
ELSE r.statement_end_offset
END - r.statement_start_offset ) / 2
) AS [executing statement]
, p.query_plan
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) AS t
CROSS APPLY
sys.dm_exec_query_plan(r.plan_handle) AS p
ORDER BY
r.total_elapsed_time DESC;
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 22, 2011 at 10:48 am
I think it looks pretty good. In my query I use for simmilar purposes, I do a couple extra joins:
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
LEFT OUTER JOIN sys.dm_exec_sessions bs ON r.blocking_session_id = bs.session_id
I get the host_name and login_name for the executing user from the first dm_exec_sessions, and the outer joined dm_exec_sessions lets me know who if anyone is blocking the long running request.
July 22, 2011 at 10:49 am
Chris Harshman (7/22/2011)
I think it looks pretty good. In my query I use for simmilar purposes, I do a couple extra joins:
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
LEFT OUTER JOIN sys.dm_exec_sessions bs ON r.blocking_session_id = bs.session_id
I get the host_name and login_name for the executing user from the first dm_exec_sessions, and the outer joined dm_exec_sessions lets me know who if anyone is blocking the long running request.
Sounds good, thank you!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 22, 2011 at 12:26 pm
That's one way to do this, but I'd probably opt for a server-side trace with a duration filter and then import those into a table for analysis. A trace is continuous.
July 22, 2011 at 12:35 pm
It looks like I am not able to capture the sproc name in the [executing batch] column if the individual SQL statement is submitted as dynamic SQL.
All I get is the same SQL as in the [executing statement] column.
Any ideas how I can get to the sproc name when the SQL in question is submitted as dynamic SQL within a stored procedure?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 22, 2011 at 1:51 pm
Chuck Hottle (7/22/2011)
That's one way to do this, but I'd probably opt for a server-side trace with a duration filter and then import those into a table for analysis. A trace is continuous.
True, that's another way of doing it, thanks.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 22, 2011 at 4:59 pm
Marios Philippopoulos (7/22/2011)
It looks like I am not able to capture the sproc name in the [executing batch] column if the individual SQL statement is submitted as dynamic SQL.All I get is the same SQL as in the [executing statement] column.
Any ideas how I can get to the sproc name when the SQL in question is submitted as dynamic SQL within a stored procedure?
Has to be done with a trace and then rather complex string manipulation to figure it out. I've never seen anything out of the box that did this.
One option I never tried was to take the dynamic code. Turn that into a new proc, then check in the dependencies. That "could" <as in I've never fully tested this> work but that just seems like too much work for nothing.
July 23, 2011 at 6:55 am
Marios Philippopoulos (7/22/2011)
It looks like I am not able to capture the sproc name in the [executing batch] column if the individual SQL statement is submitted as dynamic SQL.All I get is the same SQL as in the [executing statement] column.
Any ideas how I can get to the sproc name when the SQL in question is submitted as dynamic SQL within a stored procedure?
what events are you profiling in the trace?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 25, 2011 at 7:49 am
Perry Whittle (7/23/2011)
Marios Philippopoulos (7/22/2011)
It looks like I am not able to capture the sproc name in the [executing batch] column if the individual SQL statement is submitted as dynamic SQL.All I get is the same SQL as in the [executing statement] column.
Any ideas how I can get to the sproc name when the SQL in question is submitted as dynamic SQL within a stored procedure?
what events are you profiling in the trace?
I think this question is for Ninja's_RGR'us?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 25, 2011 at 8:00 am
Marios Philippopoulos (7/25/2011)
Perry Whittle (7/23/2011)
Marios Philippopoulos (7/22/2011)
It looks like I am not able to capture the sproc name in the [executing batch] column if the individual SQL statement is submitted as dynamic SQL.All I get is the same SQL as in the [executing statement] column.
Any ideas how I can get to the sproc name when the SQL in question is submitted as dynamic SQL within a stored procedure?
what events are you profiling in the trace?
I think this question is for Ninja's_RGR'us?
Nope it's for you... it's your name in the quote.
July 25, 2011 at 8:02 am
Ninja's_RGR'us (7/25/2011)
Marios Philippopoulos (7/25/2011)
Perry Whittle (7/23/2011)
Marios Philippopoulos (7/22/2011)
It looks like I am not able to capture the sproc name in the [executing batch] column if the individual SQL statement is submitted as dynamic SQL.All I get is the same SQL as in the [executing statement] column.
Any ideas how I can get to the sproc name when the SQL in question is submitted as dynamic SQL within a stored procedure?
what events are you profiling in the trace?
I think this question is for Ninja's_RGR'us?
Nope it's for you... it's your name in the quote.
But I'm not using a trace; I'm using the DMV query in the OP.
You mentioned using a trace... 😉
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 25, 2011 at 8:09 am
Ok so he must be confused!
I'm just using the normal text column of the trace to then get the sp_name. Nothing special.
July 25, 2011 at 8:13 am
Ninja's_RGR'us (7/25/2011)
Ok so he must be confused!I'm just using the normal text column of the trace to then get the sp_name. Nothing special.
Makes sense. So you don't think there is a way to extract information on the parent object of dynamic sql using DMVs?
Using a trace is a good idea, but it can be difficult to use effectively when one is interested in the overall health monitoring of a SQL instance.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
July 25, 2011 at 8:15 am
Marios Philippopoulos (7/25/2011)
Ninja's_RGR'us (7/25/2011)
Ok so he must be confused!I'm just using the normal text column of the trace to then get the sp_name. Nothing special.
Makes sense. So you don't think there is a way to extract information on the parent object of dynamic sql using DMVs?
Using a trace is a good idea, but it can be difficult to use effectively when one is interested in the overall health monitoring of a SQL instance.
I'm sure there's a way but it's going to be the same PITA as a trace.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply