Long Running Queries

sp_BlitzCache™ Result: Long Running Queries

Long running queries have been found in the plan cache. These may be ETL, reports, or other queries that should run long. Or it could be that someone is searching for all users whose names are LIKE '%%'. 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 just 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.

How to Fix the Problem

Investigate what’s going on in SQL Server. The following query will identify the individual queries that are taking a long time to run. From there, it’s up to you.

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

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