For a developer or a DBA, long running queries in sql server always become an issue. It eats server resources and drastically brings down server performance.So it’s your duty to find those long running queries and fix issues behind those queries. First we have to find what are those queries.For that we can use “sys.dm_exec_query_stats” DMV.
If you are new to DMV, DMVs are basically system views inside sql server which gives information about activates takes place inside.it can be used to monitor health, diagnose problems, tune performance.
This is the query which I used to find long running queries inside a server. In this query I have taken few details to consideration.
[Total Duration (s)]=Total time to execute the query in seconds.
[% CPU]= CPU used time percentage from total execution time.
[% Waiting]= Waited time percentage from total execution time.
[Execution Count]=No of times the query executed.
[Average Duration (s)]=Average time per execution in seconds.
[Individual Query]=Query Text
[Parent Query]=Parent of the query (Text)
[Database Name]= Name of the database where the query ran.
[Execution Plan]= XML represent of execution plan of the query.
SELECT TOP 5
CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))
AS [Total Duration (s)]
, CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time
AS DECIMAL(28, 2)) AS [% CPU]
, CAST((qs.total_elapsed_time – qs.total_worker_time)* 100.0 /
qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
, qs.execution_count AS [Execution Count]
, CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count
AS DECIMAL(28, 2)) AS [Average Duration (s)]
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
END – qs.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qp.dbid) AS [Database Name]
, qp.query_plan AS [Execution Plan]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.total_elapsed_time > 0 and qp.dbid=DB_ID()
ORDER BY qs.total_elapsed_time DESC
Run this query in your server and find which takes more time to execute .