Partition elimination

In one line: partitioning by date promises that a query for one month reads one month. Elimination needs the bare partitioning column compared with a value of its own type, and there are three easy ways to lose it without the query looking any different.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/partition-elimination on GitHub.

What you’ll see

  • A query for one month that opens every partition. In the demo, 25 of 25.
  • Plans that look fine: they still seek, so the reads rise only a little, while the partition count shows the problem.
  • MAX(Id) on a partitioned table that’s far more expensive than on the same table unpartitioned.
  • Msg 7733 on ALTER TABLE ... SWITCH.

Why it happens

  • A function on the partitioning column. YEAR(SaleDate) = 2025 AND MONTH(SaleDate) = 3 has no values to eliminate by, so every partition is opened and every row tested: 24× the reads of the plain range.
  • A parameter type that outranks the column’s. Against a date column, date and varchar parameters were eliminated. datetime2(7) and datetimeoffset parameters opened all 25 partitions. Many ORMs and drivers send every date as datetime2.
  • MIN/MAX of a non-partitioning column on an aligned index. Each partition is ordered on its own, so there’s no single end to seek to. It took 3,918 reads against 3.

What to do

  • Filter the bare column with a half-open range: SaleDate >= '20250301' AND SaleDate < '20250401'. That opened one partition.
  • Send parameters typed as the column: date for a date column.
  • For MAX of another column, either accept the cost or keep the value elsewhere (a sequence or a small summary table).

What doesn’t help

  • A non-aligned index to fix MAX. It gives MAX its single seek back, and blocks partition switching (Msg 7733), which is usually why the table was partitioned. Decide which matters more.
  • Reading the plan shape alone. The wrong parameter type still seeks. Count the partitions opened.

Check your server

-- Indexes on partitioned tables that are not aligned with the table (they block SWITCH).
SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id)        AS table_name,
       i.name                          AS index_name,
       ds.type_desc                    AS index_storage,
       base_ds.name                    AS table_partition_scheme
FROM sys.indexes AS i
JOIN sys.data_spaces AS ds ON ds.data_space_id = i.data_space_id
JOIN sys.indexes AS base ON base.object_id = i.object_id AND base.index_id IN (0, 1)
JOIN sys.data_spaces AS base_ds ON base_ds.data_space_id = base.data_space_id
WHERE base_ds.type = 'PS'
  AND ds.type <> 'PS'
  AND i.index_id > 1;

See it happen

The partition-elimination demo asks for March 2025 eight ways against a 25-partition table and counts the partitions each query opens. It then shows MIN/MAX on aligned and non-aligned indexes and the SWITCH that fails.

Related: non-SARGable predicates · implicit conversions · lock escalation

Measured on SQL Server 2022 CU25 (16.0.4255.1), 8 schedulers. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights