In every active system, there are running queries during almost every given time frame and their duration affect the system’s performance significantly. The longer the duration of the queries is the worse the system’s performance become, mainly because waiting queries for resources held by running queries are delayed for longer periods of time and that directly affects the system performance.
In order to avoid damaging the system performance over time due to increasing amounts of data stored in the system’s databases and identify what to focus on in order to improve system performance it is necessary to monitor the long queries and examine performance tuning options according to needs. Identifying the longest running queries may help us better schedule system processes, improve partitioning and index maintenance of large tables.
The following query would show you the currently running queries in your system:
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
If you wish to analyze the longest running queries documented in the statistics table and so looking at historical data it is recommended to run the following query:
SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.last_execution_time AS LastExecutionTime,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / DATEDIFF(s, s.creation_time,GETDATE()), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC
GO
Monitoring the long queries is essential for identifying bottlenecks in your system, planning process scheduling and generally maintaining your system, therefore it is recommended to run the above queries as part of a maintenance routine and perform performance tuning as needed.