Partition pruning, also called partition elimination, lets SQL Server avoid reading table partitions that cannot contain rows matching a query predicate. On large reporting tables, this can reduce I/O pressure and improve performance. section 10.14 section 10.7
A common mistake is partitioning a table and assuming every query benefits. Each partition is a separate B-tree; when SQL Server cannot eliminate partitions, a query can be slower than the equivalent query on a nonpartitioned table. Choose the partitioning key around the queries the table must serve. section 10.8
A reporting date range
A query filtering ImpressionsArchivePartitioned by EventTime from 2021-01-01 through 2021-12-31 returned the same rows as an unpartitioned table, but reduced logical reads from 46,979 to 15,732. The plan still used a clustered index scan, yet eliminated all but one partition. section 10.7
Listing 10.14 AND logic versus OR logic
SELECT * FROM dbo.ImpressionsArchivePartitioned WHERE CampaignID = 44538 AND EventTime >= '20210101' AND EventTime <= '20211231' ; SELECT * FROM dbo.ImpressionsArchivePartitioned WHERE CampaignID = 44538 OR EventTime >= '20210101' AND EventTime <= '20211231' ;
Compare the predicates: adding CampaignID with AND retains the EventTime partition filter, while using OR requires every partition to be read. section 10.8
Keep pruning available with these checks:
- Filter on the partitioning key. Filtering on that key together with another column using AND can eliminate partitions; using OR with another column can require scanning all partitions. section 10.8
- Match data types. A DATE parameter compared with a DATETIME partitioning column can force a conversion and prevent partition elimination; DATETIME parameters allow the comparison without that conversion. section 10.8
- Check plan reuse and simple parameterization. Parameterized filters can stop partition elimination; the demonstrated alternatives include OPTION(RECOMPILE) and adding AND 1<>2. section 10.8
- Measure rather than assume. Use I/O statistics and inspect the query plan to verify that irrelevant partitions were eliminated. section 10.7
Partitioning is most useful when very large tables support recurring reporting queries aligned with the partitioning key. Treat it as a workload design choice, not a blanket performance feature: verify the actual predicates, conversions, and plans before relying on its I/O savings. section 10.7 section 10.8
