The Case of DATETIME2 and Partition Elimination

Data types are hard.

I’ve been working on some demo code for table partitioning. I have a table partitioned by a column named FakeBirthDateStamp, which is a DATETIME2(0) column.

The table is a partitioned heap. At this point in the demo, I hadn’t built any indexes. I wanted to show that partition elimination could occur on a partitioned table, even without a clustered index.

So I ran this simple COUNT() query:

SET STATISTICS TIME, IO ON;
GO

SELECT COUNT(*)
FROM dbo.FirstNameByBirthDate_pt
WHERE FakeBirthDateStamp >= '1989-01-01' AND 
    FakeBirthDateStamp < '1990-01-01';
GO

It took much longer than I expected, and did 367K logical reads.

Table 'FirstNameByBirthDate_pt'. Scan count 43, logical reads 367752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 15797 ms,  elapsed time = 4431 ms.

Here’s what the actual execution plan looks like. I can see that a nonclustered index would be a smaller thing to scan to do a count, but we can’t tell by this view if it was able to get partition elimination or not. I’m suspicious that it didn’t because of that high logical read count.

Right clicking the table scan operator and looking at the properties, I can confirm that it accessed all 43 partitions– not just the partition for 1989.

That isn’t what I meant to demo.

It’s the opposite!

Why Didn’t Partition Elimination Work?

I found the answer when I hovered over the tooltip for the table scan and looked more closely at the properties of my predicate.

SQL Server is implicitly converting my date value to DATETIME2(7). That is a larger, more precise value than the data type I have in the table–FakeBirthDateStamp is DATETIME2(0).

That data type mismatch is preventing partition elimination!

The Fix: Explicit Conversion

Rewriting the query this way yields different results:

SET STATISTICS IO, TIME ON;
GO

SELECT COUNT(*)
FROM dbo.FirstNameByBirthDate_pt
WHERE FakeBirthDateStamp >= CAST('1989-01-01' AS DATETIME2(0)) AND 
    FakeBirthDateStamp < CAST('1990-01-01' AS DATETIME2(0));
GO

I’ve told SQL Server to use a smaller data type for these dates which matches the column data type. SQL Server doesn’t have to do an implicit conversion anymore, and can eliminate partitions.

My performance immediately improves. I’m down to 52K logical reads (from 367K), and my CPU time and duration are waaaay better.

Table 'FirstNameByBirthDate_pt'. Scan count 6, logical reads 52354, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2578 ms,  elapsed time = 750 ms.

My execution plan no longer asks for an index, and digging into the properties I can confirm that partition elimination occurred.

Like Partitioning? Read More About This Issue

Paul White wrote a great post that digs even deeper into type conversion and partition elimination. Read it here.