Find Long Running Queries

[cc lang=”sql”]
SELECT TOP 10
ObjectName = OBJECT_NAME(qt.objectid)
,DiskReads = qs.total_physical_reads — The worst reads, disk reads
,MemoryReads = qs.total_logical_reads –Logical Reads are memory reads
,Executions = qs.execution_count
,AvgDuration = qs.total_elapsed_time / qs.execution_count
,CPUTime = qs.total_worker_time
,DiskWaitAndCPUTime = qs.total_elapsed_time
,MemoryWrites = qs.max_logical_writes
,DateCached = qs.creation_time
,DatabaseName = DB_Name(qt.dbid)
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id() — Filter by current database
ORDER BY qs.total_elapsed_time DESC
[/cc]

2 comments
Jagan 10 Mar 2016 at 10:10 am

This output return only Object name not particular query. Please help me to find out a query.

Anonymous 26 Sep 2011 at 10:57 am

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near ‘.’.

Featured Articles

 Site Author

  • Thanks for visiting!