Find the Most Resource Intensive Queries in SQL Server

To find resource intensive queries, read sys.dm_exec_query_stats and rank the cached statements by reads, CPU or time. The view keeps running totals for every plan in the cache. One query on top of it gives you a ranked list of what your server spends its effort on.

What Resource Intensive Means

Resource intensive queries use three resources. They use CPU to do the work and memory to hold pages. They read data pages, which are the logical reads you see in statistics output. Time is the fourth number people watch, but time depends on waiting too. Reads and CPU describe the work itself, so rank by those first.

The cost of a query has two sides, and mixing them up wastes tuning effort. The cost of one run is the average. The cost to the server is the average times the number of runs. A heavy report that runs three times a day can top the list. So can a light lookup that runs ten thousand times. The demo below shows the same pattern.

Build a Small Workload

The first script creates a database named ResourceQueryDemo. It holds an orders table with 60,000 rows and an index on the customer. A procedure totals the orders for one customer. CREATE OR ALTER needs SQL Server 2016 SP1 or later.

IF DB_ID(N'ResourceQueryDemo') IS NULL CREATE DATABASE ResourceQueryDemo;
GO
USE ResourceQueryDemo;
GO
DROP TABLE IF EXISTS dbo.Orders;
CREATE TABLE dbo.Orders (
    OrderID    int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CustomerID int            NOT NULL,
    OrderDate  date           NOT NULL,
    Total      decimal(10,2)  NOT NULL,
    Note       nvarchar(200)  NOT NULL
);
INSERT INTO dbo.Orders (CustomerID, OrderDate, Total, Note)
SELECT TOP (60000) n % 500 + 1, DATEADD(DAY, n % 365, '2026-01-01'), (n % 90) + 10.5,
       CASE WHEN n % 40 = 0 THEN N'gift wrap requested' ELSE N'standard delivery' END
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS nums;
CREATE INDEX IX_Orders_CustomerID ON dbo.Orders (CustomerID);
GO
CREATE OR ALTER PROCEDURE dbo.GetCustomerTotal @CustomerID int, @CustomerTotal decimal(12,2) OUTPUT
AS
SELECT @CustomerTotal = SUM(Total) FROM dbo.Orders WHERE CustomerID = @CustomerID;

The next script runs the workload. It first clears the plan cache of this database only, so the data load doesn’t crowd the results. Then it calls the procedure for 50 customers and runs a scan with a leading wildcard three times. The GO 3 line repeats the previous batch three times.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
DECLARE @i int = 1, @total decimal(12,2);
WHILE @i <= 50
BEGIN
    EXEC dbo.GetCustomerTotal @CustomerID = @i, @CustomerTotal = @total OUTPUT;
    SET @i += 1;
END;
GO
SELECT COUNT(*) AS GiftOrders, SUM(Total) AS GiftTotal FROM dbo.Orders WHERE Note LIKE N'%gift%';
GO 3

Read the Cache

Now the ranking. The script below copies the cached statistics for the current database into a temporary table. Then you can sort them several ways without reading the DMV again. A cached plan carries its database in the plan attributes, not in the statement text. That’s why the query reads sys.dm_exec_plan_attributes.

The statement text needs one more step. The DMV stores the whole batch or procedure, plus the start and end of the running statement as byte offsets. The query divides them by two and cuts out that one statement. An end offset of -1 means the statement runs to the end of the batch.

SELECT ISNULL(OBJECT_NAME(t.objectid, t.dbid), N'ad hoc') AS Source,
       s.execution_count                                          AS ExecCount,
       s.total_logical_reads                                      AS TotalReads,
       s.total_logical_reads / s.execution_count                  AS AvgReads,
       CONVERT(decimal(9,1), s.total_worker_time / 1000.0)        AS TotalCpuMs,
       CONVERT(decimal(9,1), s.total_worker_time / 1000.0 / s.execution_count) AS AvgCpuMs,
       st.StatementText
INTO #Heavy
FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_plan_attributes(s.plan_handle) AS pa
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
CROSS APPLY (SELECT LEFT(LTRIM(SUBSTRING(t.text, s.statement_start_offset / 2 + 1,
             (CASE s.statement_end_offset WHEN -1 THEN DATALENGTH(t.text) ELSE s.statement_end_offset END - s.statement_start_offset) / 2 + 1)), 60)) AS st(StatementText)
WHERE pa.attribute = N'dbid' AND CONVERT(int, pa.value) = DB_ID()
  AND t.text NOT LIKE N'%dm_exec_query_stats%';

Rank the copy two ways. The first query sorts by total reads, which shows what the server pays overall. The second sorts by average reads, which shows the heaviest single call.

SELECT TOP (2) Source, ExecCount, TotalReads, AvgReads, StatementText FROM #Heavy ORDER BY TotalReads DESC;

SELECT TOP (2) Source, ExecCount, TotalReads, AvgReads, StatementText FROM #Heavy ORDER BY AvgReads DESC;

The picture shows the first list, sorted by total reads. SSMS shortens the statement text with three dots. The table below shows the second list, sorted by average reads.

The two lists disagree, and both are right. Ranked by total reads, the procedure statement comes first. It ran 50 times and read 12,878 pages in all. That’s about eight and a half times the pages the scan read. Ranked by average reads, the scan comes first at 502 reads a run, against 257 for each procedure call.

The Source column answers a common question. Yes, the query returns the statement from inside a stored procedure, and it names the procedure. The text column shows only the statement that ran, not the whole procedure body. CPU follows the same pattern, so sort the copy by TotalCpuMs to rank by processor time. The CPU values change from run to run, because they measure real time.

Fix the Top Entry and Measure Again

The scan reads more per run, but it ran three times. The procedure ran fifty, so it is the better target. Its query totals one customer’s orders, and the index it uses doesn’t hold the order total. SQL Server finds the rows through the index, then goes back to the table for each total. An index that includes the total removes that trip.

The script adds the index, clears this database’s plan cache so the old counters disappear, and repeats the fifty calls. It then reads sys.dm_exec_procedure_stats, which keeps the same totals for whole procedures.

CREATE INDEX IX_Orders_CustomerID_Total ON dbo.Orders (CustomerID) INCLUDE (Total);
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
DECLARE @i int = 1, @total decimal(12,2);
WHILE @i <= 50
BEGIN
    EXEC dbo.GetCustomerTotal @CustomerID = @i, @CustomerTotal = @total OUTPUT;
    SET @i += 1;
END;
GO
SELECT OBJECT_NAME(object_id) AS ProcedureName, execution_count AS ExecCount, total_logical_reads AS TotalReads, total_logical_reads / execution_count AS AvgReads
FROM sys.dm_exec_procedure_stats WHERE database_id = DB_ID();

The same fifty calls now read 118 pages in total, 2 for each call. That’s the loop closed: find the cost, change one thing, and read the same counter again. The first number was 12,878.

What These Numbers Can’t Tell You

The DMV only knows what is in the plan cache. A restart empties it, and so does memory pressure, a recompile or a manual clear. A query that was never cached, such as a statement with OPTION (RECOMPILE), leaves no totals behind. On a server with a large cache, run it with care during business hours. The query itself uses resources.

You could argue that Query Store is the better tool, and for history it is. It keeps its data across restarts and across plan changes. The DMV is still the fastest first look, because it needs no setup and answers in seconds. Use it to find the suspect, then use Query Store to check whether the suspect is new.

The view needs permission to read server state. That’s VIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later. A login without it gets an error, not an empty list.

What to Remember

To find resource intensive queries, rank by total cost first, then look at the average. The heaviest single query isn’t always the most expensive one, because frequency counts. Copy the DMV into a temporary table so you can sort it several ways. Read the plan attributes to limit the list to one database.

After every change, read the same counter again. When you finish with the demo, run the cleanup script.

USE master;
GO
ALTER DATABASE ResourceQueryDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE ResourceQueryDemo;

The heaviest query is not always the costliest, it is the one whose runs add up.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.