Finding and fixing slow queries

This lesson puts the module together into a method you can use on a real system:

1. Find the queries that cost the most in total, not the one someone complained about last.
2. Measure one of them: logical reads with STATISTICS IO, and time.
3. Read the plan: scans, lookups, sorts, estimate gaps, warnings.
4. Change one thing: an index, a rewrite, a statistics update.
5. Measure again, and check that nothing else got worse.

Start from a fresh Sales table. This time there is one very large customer: customer 1 is a trade account with 20,000 extra sales.

1DROP TABLE IF EXISTS dbo.Sales;2CREATE TABLE dbo.Sales (3    SaleId     int           NOT NULL CONSTRAINT PK_Sales PRIMARY KEY,4    SaleDate   date          NOT NULL,5    CustomerId int           NOT NULL,6    ProductId  int           NOT NULL,7    Quantity   int           NOT NULL,8    Amount     decimal(10,2) NOT NULL,9    Channel    varchar(10)   NOT NULL,10    Notes      char(100)     NULL11);12INSERT INTO dbo.Sales (SaleId, SaleDate, CustomerId, ProductId, Quantity, Amount, Channel)13SELECT value,14       DATEADD(day, value * 37 % 1096, '2022-01-01'),15       CASE WHEN value > 100000 THEN 1 ELSE value * 7919 % 5000 + 1 END,16       value % 12 + 1,17       value % 7 + 1,18       value * 31 % 9000 / 100.0 + 1,19       CASE value % 10 WHEN 0 THEN 'Phone' WHEN 1 THEN 'Shop' WHEN 2 THEN 'Shop' ELSE 'Web' END20FROM GENERATE_SERIES(1, 120000);21 22SELECT CASE WHEN CustomerId = 1 THEN 'Customer 1' ELSE 'Each other customer' END AS Who,23       MAX(n) AS SalesRows24FROM (SELECT CustomerId, COUNT(*) AS n FROM dbo.Sales GROUP BY CustomerId) AS c25GROUP BY CASE WHEN CustomerId = 1 THEN 'Customer 1' ELSE 'Each other customer' END;

Who                  SalesRows
-------------------  ---------
Customer 1               20020
Each other customer         20

Step 1: find the expensive queries

SQL Server keeps figures for every cached plan in sys.dm_exec_query_stats. This lists the ten statements that have done the most logical reads in total since their plans were cached (your results depend on what has run):

1SELECT TOP (10)2       qs.execution_count,3       qs.total_logical_reads,4       qs.total_logical_reads / qs.execution_count AS avg_reads,5       qs.total_elapsed_time / qs.execution_count / 1000 AS avg_ms,6       SUBSTRING(t.text, qs.statement_start_offset / 2 + 1,7                 (CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)8                       ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1) AS statement_text9FROM sys.dm_exec_query_stats AS qs10CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t11ORDER BY qs.total_logical_reads DESC;

Sort by total, not average: a query that does 2,000 reads and runs 50,000 times a day matters more than a 100,000-read report that runs once. The cache forgets plans when the server restarts or memory runs short, so for history use the Query Store: it records plans and run-time figures in the database itself, and is on by default for new databases in SQL Server 2022. In SSMS, open the database’s Query Store folder and choose Top Resource Consuming Queries.

Steps 2 to 5 on one query

The website shows each customer their five most recent purchases. Measure it:

1SET STATISTICS IO ON;2SELECT TOP (5) SaleDate, Amount3FROM dbo.Sales4WHERE CustomerId = 30005ORDER BY SaleDate DESC;6SET STATISTICS IO OFF;

SaleDate    Amount
----------  ------
2024-12-21   10.51
2024-08-15   80.51
2024-07-22   90.51
2024-06-28   10.51
2024-06-04   20.51

Table 'Sales'. Scan count 1, logical reads 2152, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Every page for five rows. The plan would show a Clustered Index Scan with a WHERE predicate, and a Top N Sort. While compiling it, the optimizer noticed that an index would have helped, and recorded a missing index suggestion:

1SELECT d.equality_columns, d.inequality_columns, d.included_columns,2       CAST(s.avg_user_impact AS int) AS est_improvement_pct3FROM sys.dm_db_missing_index_details AS d4JOIN sys.dm_db_missing_index_groups AS g ON g.index_handle = d.index_handle5JOIN sys.dm_db_missing_index_group_stats AS s ON s.group_handle = g.index_group_handle6WHERE d.database_id = DB_ID() AND d.object_id = OBJECT_ID(N'dbo.Sales');

equality_columns  inequality_columns  included_columns  est_improvement_pct
----------------  ------------------  ----------------  -------------------
[CustomerId]      NULL                NULL                               99

Treat suggestions as hints. This one asks for an index on CustomerId alone. That would find the customer’s 20 rows, but each would still need a key lookup for SaleDate and Amount, and then a sort. With SaleDate as the second key column, sorted descending, the rows are already in the order the query wants, and SQL Server can read the first five and stop:

1CREATE INDEX IX_Sales_Customer_Date ON dbo.Sales (CustomerId, SaleDate DESC)2    INCLUDE (Amount);3 4SET STATISTICS IO ON;5SELECT TOP (5) SaleDate, Amount6FROM dbo.Sales7WHERE CustomerId = 30008ORDER BY SaleDate DESC;9SET STATISTICS IO OFF;

SaleDate    Amount
----------  ------
2024-12-21   10.51
2024-08-15   80.51
2024-07-22   90.51
2024-06-28   10.51
2024-06-04   20.51

Table 'Sales'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

A few reads instead of a full scan, with no sort. Before keeping the index, ask what it costs: one more structure to maintain on every insert. For a query the website runs on every page view, that is an easy trade.

Parameter sniffing

Stored procedures (and parameterized queries from applications) are compiled once and their plan is reused. The optimizer builds that plan for the parameter values of the first call: it “sniffs” them. That is usually good, since compiling costs time. It goes wrong when values need very different plans. Customer 3000 has 20 sales; customer 1 has over 20,000.

1CREATE OR ALTER PROCEDURE dbo.CustomerSummary @CustomerId int2AS3SELECT COUNT(*) AS SalesRows, SUM(Quantity) AS Items, SUM(Amount) AS Spent4FROM dbo.Sales5WHERE CustomerId = @CustomerId;6GO7SET STATISTICS IO ON;8EXEC dbo.CustomerSummary @CustomerId = 3000;   -- compiled for 20 rows9EXEC dbo.CustomerSummary @CustomerId = 1;      -- reuses that plan10SET STATISTICS IO OFF;

SalesRows  Items    Spent
---------  -----  -------
       20     83  1000.20

Table 'Sales'. Scan count 1, logical reads 62, physical reads 3, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SalesRows  Items      Spent
---------  -----  ---------
    20020  80086  921000.00

Table 'Sales'. Scan count 1, logical reads 60127, physical reads 5, page server reads 0, read-ahead reads 22, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

The first call compiled a plan for 20 rows: seek IX_Sales_Customer_Date, then a key lookup per row, because Quantity is not in the index. The second call reused it for over 20,000 rows, so it did over 20,000 lookups. Now clear the procedure’s plan and call it in the other order:

1EXEC sp_recompile N'dbo.CustomerSummary';2GO3SET STATISTICS IO ON;4EXEC dbo.CustomerSummary @CustomerId = 1;      -- compiled for 20,000 rows5EXEC dbo.CustomerSummary @CustomerId = 3000;   -- reuses that plan6SET STATISTICS IO OFF;

Object 'dbo.CustomerSummary' was successfully marked for recompilation.

SalesRows  Items      Spent
---------  -----  ---------
    20020  80086  921000.00

Table 'Sales'. Scan count 1, logical reads 2152, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SalesRows  Items    Spent
---------  -----  -------
       20     83  1000.20

Table 'Sales'. Scan count 1, logical reads 2152, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Now the plan is a scan, fine for customer 1 but wasteful for the 4,999 ordinary customers. The same procedure is fast or slow depending on which call happened to compile it. That is why parameter sniffing problems seem to come and go, often after a restart or a statistics update clears the plan.

CREATE OR ALTER PROCEDURE dbo.CustomerSummary @CustomerId int AS
SELECT COUNT(*), SUM(Quantity), SUM(Amount)
FROM dbo.Sales
WHERE CustomerId = @CustomerId;

The fixes, best first:

• Make one plan good for every value. Here, adding Quantity to the index’s INCLUDE list removes the lookups, and the seek is then right for both customers.
• OPTION (RECOMPILE) on the statement: compile a fresh plan on every call, for its own values. It costs compile time on every run, so use it for queries that run rarely or vary hugely.
• OPTIMIZE FOR UNKNOWN, or OPTIMIZE FOR (@CustomerId = 3000): plan for an average or a chosen value instead of the first one.
• SQL Server 2022 also has Parameter Sensitive Plan optimization, which can keep several plans for one query when the statistics show very uneven data. It needs compatibility level 160 and only kicks in for strongly skewed columns.

1CREATE OR ALTER PROCEDURE dbo.CustomerSummary @CustomerId int2AS3SELECT COUNT(*) AS SalesRows, SUM(Quantity) AS Items, SUM(Amount) AS Spent4FROM dbo.Sales5WHERE CustomerId = @CustomerId6OPTION (RECOMPILE);7GO8SET STATISTICS IO ON;9EXEC dbo.CustomerSummary @CustomerId = 3000;10EXEC dbo.CustomerSummary @CustomerId = 1;11SET STATISTICS IO OFF;

SalesRows  Items    Spent
---------  -----  -------
       20     83  1000.20

Table 'Sales'. Scan count 1, logical reads 62, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SalesRows  Items      Spent
---------  -----  ---------
    20020  80086  921000.00

Table 'Sales'. Scan count 1, logical reads 2152, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

A tuning checklist

1CREATE INDEX IX_Sales_Channel_Date ON dbo.Sales (Channel, SaleDate)2    INCLUDE (Amount);3 4SET STATISTICS IO ON;5SELECT SaleId, Amount6FROM dbo.Sales7WHERE Channel = 'Phone' AND SaleDate = '2024-03-01';8SET STATISTICS IO OFF;

SaleId  Amount
------  ------
   910   13.10
  6390    1.90
 11870   80.70
 17350   69.50
 22830   58.30
 28310   47.10
 33790   35.90
 39270   24.70
 44750   13.50
 50230    2.30
 55710   81.10
 61190   69.90
 66670   58.70
 72150   47.50
 77630   36.30
 83110   25.10
 88590   13.90
 94070    2.70
 99550   81.50
105030   70.30
110510   59.10
115990   47.90

Table 'Sales'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.