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.

Execution-Plan-Missing-Index

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.

43 partitions accessed

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.

predicate-implicit-conversion-datatype

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.

4 Responses to The Case of DATETIME2 and Partition Elimination

  1. ALEX June 8, 2016 at 8:57 pm #

    that saved my day ! Thank You Kendra

Trackbacks/Pingbacks

  1. DATETIME2 Partition Functions and SSMS Scripting Weirdness – The Fly Fishing DBA - March 10, 2016

    […] 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. […]

  2. DATETIME2 Partition Elimination – Curated SQL - March 11, 2016

    […] Kendra Little shows that DATETIME2 implicit conversion can prevent partition elimination: […]

  3. DATETIME2 Partition Functions and SSMS Scripting Weirdness – Brendan Mason – Fly Fishing DBA (Staging) - November 21, 2016

    […] 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. […]

Leave a Reply