SYMPTOMS
eDiscovery processing or review speeds that are not optimal may indicate a SQL Server performance problem.
In general, long running queries have been found in the plan cache. These may be for ETL, reports, or other queries where an extended run time is expected or a search for all users whose names are LIKE '%%' is being performed. Either way, these queries bear investigating. In some cases, this is the total clock time that the query took to execute and in others this is the total CPU time that the query took to execute. Queries with a high max_elapsed_time take a lot of time to run – they could be slow single threaded queries. Queries with a high max_worker_time (CPU time) may be highly parallel queries.
CAUSE
Not applicable.
RESOLUTION
Investigate what is going on in SQL Server.
The following query will identify the individual queries that are taking a long time to run.
SELECT st.text,
qp.query_plan,
qs.*
FROM (
SELECT TOP 50 *
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.max_worker_time > 300
OR qs.max_elapsed_time > 300
1.SELECT st.text,
2.qp.query_plan,
3.qs.*
4.FROM (
5.SELECT TOP 50 *
6.FROM sys.dm_exec_query_stats
7.ORDER BY total_worker_time DESC
8.) AS qs
9.CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
10.CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
11.WHERE qs.max_worker_time > 300
12.OR qs.max_elapsed_time > 300
Source: http://www.brentozar.com/blitzcache/long-running-queries/
APPLIES TO
3.7 Service Pack 1; 3.7 Service Pack 2; 3.8; 3.9
Comments
0 comments
Article is closed for comments.