Steve Fenton

SQL Server: Find expensive queries by running one

I have found that Activity Monitor can have a habit of bugging out on you when you try to get recent expensive queries. If you also get this, you may want to try the following query instead.

You can adjust the query purposefully by changing the ordering, based on what you are looking for. There are examples for total worker time and average worker time – but you may be looking for queries that are reading/writing too much.

There are also other stats available, just look inside the QUERYSTATS alias in SQL Management Studio for some auto-completion.

SELECT TOP 10
    SUBSTRING(
        SQLTEXT.TEXT,
        (QUERYSTATS.statement_start_offset / 2) + 1,
        ((CASE QUERYSTATS.statement_end_offset WHEN -1 THEN DATALENGTH(SQLTEXT.TEXT) ELSE QUERYSTATS.statement_end_offset END - QUERYSTATS.statement_start_offset) / 2) + 1
    ) AS sql_query,
    QUERYSTATS.execution_count,

    QUERYSTATS.total_worker_time / QUERYSTATS.execution_count AS avg_worker_time,
    QUERYSTATS.total_worker_time,
    QUERYSTATS.last_worker_time,

    QUERYSTATS.total_logical_reads,
    QUERYSTATS.last_logical_reads,
    QUERYSTATS.total_logical_writes,
    QUERYSTATS.last_logical_writes,
    QUERYSTATS.total_physical_reads,
    QUERYSTATS.last_physical_reads,

    QUERYSTATS.total_elapsed_time / QUERYSTATS.execution_count AS avg_elapsed_time,
    QUERYSTATS.total_elapsed_time / 1000000 AS total_elapsed_time_in_S,
    QUERYSTATS.last_elapsed_time / 1000000 AS last_elapsed_time_in_S,
    QUERYSTATS.last_execution_time,
    QUERYPLAN.query_plan
FROM
    sys.dm_exec_query_stats QUERYSTATS
CROSS APPLY
    sys.dm_exec_sql_text(QUERYSTATS.sql_handle) SQLTEXT
CROSS APPLY
    sys.dm_exec_query_plan(QUERYSTATS.plan_handle) QUERYPLAN
WHERE
    last_execution_time > GETUTCDATE() - 1 -- Must have run in the past day, for example
ORDER BY
    -- Order by Total Worker Time
    --QUERYSTATS.total_worker_time DESC
    -- Order by Average Worker Time
    --QUERYSTATS.total_worker_time / QUERYSTATS.execution_count DESC
    -- My favourite: runs often, takes quite some time...
    QUERYSTATS.execution_count * (QUERYSTATS.total_worker_time / QUERYSTATS.execution_count) DESC

The ordering I have left “commented-in” is my personal favourite. Your mileage may vary.