Splitting a large table by date feels like a performance fix until the same report still scans every slice. Partition elimination happens when a predicate lets SQL Server rule out partitions before reading them. The actual plan can show whether that promise came true.
Start With the Question Partitioning Should Answer
A partitioned table places rows in separate partitions according to a function. That can simplify loading, archiving, and index maintenance. Query speed is a separate question. If a report needs the whole year, it can still read every partition. If it needs one week, the plan should show only the partitions whose boundaries overlap that week. Physical separation alone does not teach SQL Server which drawer to open.
I have seen teams add monthly partitions and then keep filtering on a calculated expression instead of the partition column. The table design changed; the predicate did not become usable. Before discussing the number of partitions, write down the exact report filter and the table's partitioning column and data type. Does the filter point at that column in a direct, comparable form?
Read the Partition Function First
Find the partition function and boundary values used by the table. A wrong assumption about RANGE LEFT or RANGE RIGHT can turn a midnight value into a surprise. The query below lists boundaries for a named function. Replace the name with the one on your system. It is read-only and gives a quick map before you interpret the plan.
SELECT pf.name AS function_name, pf.boundary_value_on_right,
prv.boundary_id, CONVERT(nvarchar(100), prv.value) AS boundary_value
FROM sys.partition_functions AS pf
JOIN sys.partition_range_values AS prv
ON prv.function_id = pf.function_id
WHERE pf.name = N'pf_SaleDate'
ORDER BY prv.boundary_id;The boundary list does not tell you how many partitions a query accessed. It tells you which ranges exist. Match the function to the table's index or heap before using it as evidence. A table can have several indexes, and a nonaligned index can change the access path. Keep the index and predicate in view together.
Ask the Function About a Test Value
The $PARTITION function maps one value to a partition number. Use a value at the start of a range, one inside it, and one at its end. The date type in the call should match the partition function's parameter type. This catches an off-by-one boundary assumption without scanning the fact table.
SELECT $PARTITION.pf_SaleDate(CONVERT(date, '20250101')) AS first_day,
$PARTITION.pf_SaleDate(CONVERT(date, '20250115')) AS middle_day,
$PARTITION.pf_SaleDate(CONVERT(date, '20250201')) AS next_boundary;I run that check before a partition switch or archive. It is faster to question one boundary value than to explain later why a month landed in the wrong partition. The function result proves the mapping for those values. It does not prove that a query predicate lets the optimizer eliminate other partitions.
Let the Actual Plan Prove Partition Elimination
Run the report query with its real parameter values and capture the actual execution plan. Find the access operator for the partitioned table. Its runtime properties include Actual Partition Count and the partitions accessed. One accessed partition for a one-month predicate is a good sign when boundaries match that month. Several accessed partitions can be correct when a date range crosses boundaries. Count what the query needed, not what you hoped it needed.
SET STATISTICS XML ON;
SELECT SUM(SalesAmount) AS TotalSales
FROM dbo.FactSales
WHERE SaleDate >= '20250101'
AND SaleDate < '20250201';
SET STATISTICS XML OFF;The returned plan XML includes runtime partition information when the access path supports it. SSMS displays the same details in operator properties. Compare that with a wider query and with the partition function test. A graphical icon labeled Index Seek is not enough. A seek can visit many partitions and still do substantial work within each one.
Expect one surprise with the half-open range above. On a RANGE RIGHT function with a boundary at February 1, my test plan reported two partitions accessed, not one. SQL Server parameterized the literals, so the plan could not rule out the partition that starts exactly at the end value. That extra partition returned no rows. The same query with OPTION (RECOMPILE) on a test run reported one partition.
Predicate Shapes That Block Partition Elimination
A function wrapped around the partition column, such as converting SaleDate to a string, can make elimination harder. A comparison between mismatched data types can introduce a conversion that changes the plan. An OR condition involving another column can require rows from partitions outside the apparent date range. Rewrite the date filter as a half-open range on the native column type, then compare the actual plan.
Not every function prevents elimination in every version and shape, so test the query you have rather than memorizing a slogan. If a stored procedure accepts text dates, convert the parameter once to the column's date type. Keep the column bare in the search condition. If the application needs several optional filters, compare a few representative parameter combinations instead of validating only the easiest one.
Check What Remains After Partition Elimination
Skipping partitions reduces the part of the table considered. It does not guarantee a cheap scan within the partitions left open. A single monthly partition can still contain more rows than the report needs. Index choice, statistics, and a residual predicate determine that remaining cost. Read actual rows, rows read, and I/O alongside partition count. The cost of a report is the work performed, not the number of colored boxes in a diagram.
I keep partition elimination as one item in a tuning record: function boundaries, filter values, actual partitions accessed, and the table access operator. After a deployment or compatibility change, repeat the check. A query can lose elimination because the predicate changed while the table stayed the same. The plan is the witness that does not care how proud we are of the partition design.
When the plan reports more partitions than expected, compare its predicate property with the original WHERE clause. An implicit conversion can be visible there even when the query text looks simple. Also check whether a parameter was compiled for a broad range and reused for a narrow one. Recompile a test query with representative values to separate plan reuse from an inherently non-eliminable predicate. The aim is to explain the extra partitions, not to add a hint before the cause is known. A report that intentionally includes an OR branch without a date restriction is asking for more drawers. Change the business condition or accept the read; a prettier partition function cannot change the logic.
Related reading on this blog: Aligned and Non-Aligned Indexes for Partitioning and Table Partitioning for Slow Performance.
Partitioning is not proof of a faster filter, it is a layout that the actual plan must learn to skip.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
