Sargable queries and statistics

An index only helps if SQL Server can use it to seek. Whether it can depends on how you write the WHERE. A predicate that can use an index seek is called sargable, from “Search ARGument ABLE”. Start from a fresh table with three nonclustered indexes:

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       value * 7919 % 5000 + 1,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, 100000);21 22CREATE INDEX IX_Sales_SaleDate ON dbo.Sales (SaleDate);23CREATE INDEX IX_Sales_Channel  ON dbo.Sales (Channel);24CREATE INDEX IX_Sales_Amount   ON dbo.Sales (Amount);

Keep the column alone

Both queries below count the sales of June 2023. The first wraps SaleDate in functions; the second compares the bare column with a range:

1SET STATISTICS IO ON;2SELECT COUNT(*) AS SalesRows FROM dbo.Sales3WHERE YEAR(SaleDate) = 2023 AND MONTH(SaleDate) = 6;4 5SELECT COUNT(*) AS SalesRows FROM dbo.Sales6WHERE SaleDate >= '2023-06-01' AND SaleDate < '2023-07-01';7SET STATISTICS IO OFF;

SalesRows
---------
     2737

Table 'Sales'. Scan count 1, logical reads 163, physical reads 1, page server reads 0, read-ahead reads 161, 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
---------
     2737

Table 'Sales'. Scan count 1, logical reads 8, 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.

Same answer, very different work. The index is sorted by SaleDate, not by YEAR(SaleDate). To find the rows where a function of the column has some value, SQL Server must compute the function for every row, so it scans the whole index. With the bare column, it seeks to 2023-06-01 and stops at 2023-07-01.

The rule: do the arithmetic on the other side. Leave the indexed column alone and move the calculation to the constant or parameter:

SELECT COUNT(*) FROM dbo.Sales
WHERE YEAR(SaleDate) = 2023;

SELECT COUNT(*) FROM dbo.Sales
WHERE SaleDate >= '2023-01-01'
  AND SaleDate <  '2024-01-01';

LIKE and leading wildcards

LIKE 'Sh%' is a range: everything from “Sh” up to just before “Si”, so it can seek. LIKE '%op' can match anywhere in the sort order, so every row must be checked:

1SET STATISTICS IO ON;2SELECT COUNT(*) AS SalesRows FROM dbo.Sales WHERE Channel LIKE 'Sh%';3SELECT COUNT(*) AS SalesRows FROM dbo.Sales WHERE Channel LIKE '%op';4SET STATISTICS IO OFF;

SalesRows
---------
    20000

Table 'Sales'. Scan count 1, logical reads 48, 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
---------
    20000

Table 'Sales'. Scan count 1, logical reads 218, 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.

Implicit conversions

Channel is varchar. Compare it with an nvarchar value (with the N prefix) and the two types do not match. nvarchar has the higher data type precedence, so SQL Server converts the column, row by row:

1SET STATISTICS IO ON;2SELECT COUNT(*) AS SalesRows FROM dbo.Sales WHERE Channel = 'Phone';3SELECT COUNT(*) AS SalesRows FROM dbo.Sales WHERE Channel = N'Phone';4SET STATISTICS IO OFF;5GO6SET SHOWPLAN_TEXT ON;7GO8SELECT COUNT(*) AS SalesRows FROM dbo.Sales WHERE Channel = N'Phone';9GO10SET SHOWPLAN_TEXT OFF;

SalesRows
---------
    10000

Table 'Sales'. Scan count 1, logical reads 26, 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
---------
    10000

Table 'Sales'. Scan count 1, logical reads 218, 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.

StmtText
---------------------------------------------------------------------
SELECT COUNT(*) AS SalesRows FROM dbo.Sales WHERE Channel = N'Phone';

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1006],0)))
       |--Stream Aggregate(DEFINE:([Expr1006]=Count(*)))
            |--Index Scan(OBJECT:([BuildMart].[dbo].[Sales].[IX_Sales_Channel]),  WHERE:(CONVERT_IMPLICIT(nvarchar(10),[BuildMart].[dbo].[Sales].[Channel],0)=N'Phone'))

The plan shows CONVERT_IMPLICIT(nvarchar(10), [Channel], 0) inside a scan. This happens often with applications: .NET strings are sent as nvarchar by default, so a parameter compared with a varchar column can silently turn every seek into a scan. Match parameter types to column types.

Statistics: what the optimizer knows

To choose between a seek with lookups and a scan, the optimizer needs to know how many rows a predicate will return before it runs. It gets that from statistics: a summary of the values in a column, kept for every index and for other columns used in WHERE clauses.

The core of a statistic is a histogram of the first key column, with up to 200 steps. Channel has only three values, so its histogram has three steps:

1DBCC SHOW_STATISTICS (N'dbo.Sales', IX_Sales_Channel) WITH HISTOGRAM;

RANGE_HI_KEY  RANGE_ROWS  EQ_ROWS  DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
------------  ----------  -------  -------------------  --------------
Phone                  0    10000                    0               1
Shop                   0    20000                    0               1
Web                    0    70000                    0               1

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

• RANGE_HI_KEY: the top value of the step.
• EQ_ROWS: rows equal to that value.
• RANGE_ROWS and DISTINCT_RANGE_ROWS: rows (and distinct values) between this step and the one before.
• AVG_RANGE_ROWS: the estimate for one value inside the range.

For Channel = 'Phone' the optimizer reads 10,000 straight from the histogram: far too many for lookups. For a date column with 1,096 values, each histogram step covers a range of days, and the estimate for one day inside a step is that step’s average. sys.dm_db_stats_properties shows how big each statistic is and how much the table has changed since it was built:

1SELECT s.name AS StatName, p.rows, p.rows_sampled, p.steps, p.modification_counter2FROM sys.stats AS s3CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS p4WHERE s.object_id = OBJECT_ID(N'dbo.Sales')5ORDER BY s.stats_id;

StatName             rows  rows_sampled  steps  modification_counter
-----------------  ------  ------------  -----  --------------------
PK_Sales             NULL          NULL   NULL                  NULL
IX_Sales_SaleDate  100000        100000    200                     0
IX_Sales_Channel   100000        100000      3                     0
IX_Sales_Amount    100000        100000      3                     0

Statistics on an index are built with a full scan when the index is created. PK_Sales shows NULLs because its index was created on the empty table, before the INSERT; its statistics will be filled in the first time a query needs them. modification_counter counts the changed rows since then. When enough rows have changed, SQL Server updates the statistics automatically the next time a query needs them. On a table this size the threshold is about √(1,000 × rows): 10,000 changes for 100,000 rows.

Stale statistics, bad plans

Now load a big batch: 5,000 sales on one new day, 2025-01-15. That is below the threshold, so the statistics are not updated, and the histogram still ends at 2024-12-31.

1INSERT INTO dbo.Sales (SaleId, SaleDate, CustomerId, ProductId, Quantity, Amount, Channel)2SELECT 100000 + value, '2025-01-15', value % 5000 + 1, value % 12 + 1, 1, 9.99, 'Web'3FROM GENERATE_SERIES(1, 5000);4 5SET STATISTICS IO ON;6SELECT COUNT(*) AS SalesRows, SUM(Quantity) AS Items7FROM dbo.Sales8WHERE SaleDate = '2025-01-15';9SET STATISTICS IO OFF;

SalesRows  Items
---------  -----
     5000   5000

Table 'Sales'. Scan count 1, logical reads 15011, 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.

Because the statistics know nothing about that day, the optimizer guessed about 91 rows, the average for one day, and chose a seek on IX_Sales_SaleDate with a key lookup per row (Quantity is not in the index). The query found 5,000 rows, so it paid for 5,000 lookups: several times the cost of reading the whole table. Update the statistics and run it again:

1UPDATE STATISTICS dbo.Sales IX_Sales_SaleDate WITH FULLSCAN;2 3SET STATISTICS IO ON;4SELECT COUNT(*) AS SalesRows, SUM(Quantity) AS Items5FROM dbo.Sales6WHERE SaleDate = '2025-01-15';7SET STATISTICS IO OFF;

SalesRows  Items
---------  -----
     5000   5000

Table 'Sales'. Scan count 1, logical reads 1883, 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.

With the right estimate, the optimizer chose a scan, the cheaper plan for 5,000 rows. Updating statistics also makes the plans that depend on them compile again. Most systems update statistics in a nightly maintenance job as well as automatically, and tables that receive large loads benefit from an UPDATE STATISTICS straight after the load.

1SET STATISTICS IO ON;2SELECT COUNT(*) AS SalesRows3FROM dbo.Sales4WHERE DATEPART(year, SaleDate) = 2024 AND DATEPART(month, SaleDate) = 12;5 6SELECT COUNT(*) AS SalesRows7FROM dbo.Sales8WHERE SaleDate >= '2024-12-01' AND SaleDate < '2025-01-01';9SET STATISTICS IO OFF;

SalesRows
---------
     2827

Table 'Sales'. Scan count 1, logical reads 171, 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
---------
     2827

Table 'Sales'. Scan count 1, logical reads 7, 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.