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.
5 Comments. Leave new
[…] Kendra Little’s blog post on DATETIME2 and Partition Elimination today reminded me of an interesting behavior in SQL Server Management Studio that I encountered recently. After putting together a partitioned database on my workstation, scripting it out, and deploying it to a test server, I started seeing problems with partition switching because partitions that should have been empty were ending up with data in them. […]
[…] Kendra Little shows that DATETIME2 implicit conversion can prevent partition elimination: […]
that saved my day ! Thank You Kendra
[…] Kendra Little’s blog post on DATETIME2 and Partition Elimination today reminded me of an interesting behavior in SQL Server Management Studio that I encountered recently. After putting together a partitioned database on my workstation, scripting it out, and deploying it to a test server, I started seeing problems with partition switching because partitions that should have been empty were ending up with data in them. […]
[…] of your partition key in your database. An example of this sensitivity is covered in this great post by Kendra […]