Why do some DBAs avoid DATETIME2?

Question

Why do some DBAs avoid DATETIME2?

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

David Burrows - Thursday, June 8, 2017 1:57 AM
Interesting Jeff,
I did start using DATETIME2 but now have reverted to DATETIME.
I do use DATE extensively as a lot of my data is date only never any time.
I ran your test on my new SQL 2016 server, which not currently in use, so the test was the only thing running, results as follows

David,
Can you have a look at the execution plans?
On my machine it's Index Seek with Key Lookup for first 3 queries, and Clustered Index Scan for the 4th one.
What do you have on your server?

_____________
Code for TallyGenerator

David Burrows SSC Guru Points: 65142 More actions · Answer 2

Sergiy - Thursday, June 8, 2017 2:31 AM
David Burrows - Thursday, June 8, 2017 1:57 AM
Interesting Jeff,
I did start using DATETIME2 but now have reverted to DATETIME.
I do use DATE extensively as a lot of my data is date only never any time.
I ran your test on my new SQL 2016 server, which not currently in use, so the test was the only thing running, results as follows
David,
Can you have a look at the execution plans?
On my machine it's Index Seek with Key Lookup for first 3 queries, and Clustered Index Scan for the 4th one.
What do you have on your server?

All Clustered Index scans, the NC index is ignored
If I include OrderID and OrderStatusId to the NC index (as advised in the execution plans) then all the queries are NC Index Seeks
with a reduction in logical reads to 900, 900, 900 and1092 respectively.

Far away is close at hand in the images of elsewhere.
Anon.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

Sergiy - Thursday, June 8, 2017 2:31 AM
David Burrows - Thursday, June 8, 2017 1:57 AM
Interesting Jeff,
I did start using DATETIME2 but now have reverted to DATETIME.
I do use DATE extensively as a lot of my data is date only never any time.
I ran your test on my new SQL 2016 server, which not currently in use, so the test was the only thing running, results as follows
David,
Can you have a look at the execution plans?
On my machine it's Index Seek with Key Lookup for first 3 queries, and Clustered Index Scan for the 4th one.
What do you have on your server?

I get the same on 2008 and 2012. 3 seeks with RID lookups and one scan.

One of the things I haven't done yet is test for what happens when a covering index is used but covering indexes are frequently the exception rather than the rule. In this case, a covering index would duplicate the entire table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

David Burrows - Thursday, June 8, 2017 3:15 AM
Sergiy - Thursday, June 8, 2017 2:31 AM
David Burrows - Thursday, June 8, 2017 1:57 AM
Interesting Jeff,
I did start using DATETIME2 but now have reverted to DATETIME.
I do use DATE extensively as a lot of my data is date only never any time.
I ran your test on my new SQL 2016 server, which not currently in use, so the test was the only thing running, results as follows
David,
Can you have a look at the execution plans?
On my machine it's Index Seek with Key Lookup for first 3 queries, and Clustered Index Scan for the 4th one.
What do you have on your server?
All Clustered Index scans, the NC index is ignored
If I include OrderID and OrderStatusId to the NC index (as advised in the execution plans) then all the queries are NC Index Seeks
with a reduction in logical reads to 900, 900, 900 and1092 respectively.

Heh... now we know what they mean when they say "It just runs faster". It means they've finally fixed a lot of bad things. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

Sergiy - Thursday, June 8, 2017 1:53 AM
Jeff Moden - Wednesday, June 7, 2017 9:52 PM
I'm at a loss for words because the CONNECT item on the subject was "Closed as Won't Fix" and, according to comments on that CONNECT item, the problem hasn't been fixed even in 2014. No one made any comments about it for 2016 but, if I were a betting man, I'd say the problem still exists there, as well.
I recon the problem is not fixable with the current design of datetime2.
Reversed order of bytes makes it impossible for a computer to compare stored binary values as whole ones.
64 bit CPU can compare 2 DATETIME binaries in a single cycle, it's 3 Assembler commands.
But it does not work for DATETIME2.
You need to split the binaries into separate bytes (we need to lookup somewhere else for the number of bytes to be used) and then perform sequential comparison byte-by-byte, using 8 bit out of 64 on each cycle.
Now, when you search a value in an index you face a prospect of doing this for every entry in the statistics.
Statistics on the reversed binary strings do not provide any useful hints whatsoever.
Which means - need to perform an index scan just to estimate if it has to be scanned or seeked.
No wonder MS chose not to use statistics at all and assume SEEK for this kind of datatype every single time.
One could say "Somebody had a gigantic brain fart", but actually not.
Since MS charges Azure users for CPU cycles it makes a good sense for them to advocate usage of DATETIME2 instead DATETIME.
Clear profit. "It's just a business" (c).

Heh... that might also be the correct explanation for why FORMAT is 44 times slower than CONVERT. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65142 More actions · Answer 6

Jeff Moden - Thursday, June 8, 2017 8:00 AM
I get the same on 2008 and 2012. 3 seeks with RID lookups and one scan.
One of the things I haven't done yet is test for what happens when a covering index is used but covering indexes are frequently the exception rather than the rule. In this case, a covering index would duplicate the entire table.

As I stated, the engine preferred Clustered Index scan over NC seeks
I wonder if it knew about the logical read counts :w00t:

Far away is close at hand in the images of elsewhere.
Anon.

ScottPletcher SSC Guru Points: 101096 More actions · Answer 7

sgmunson wrote:

Give me a reason to use the new data type when I can't use date functions with it natively, or can't get DATEDIFF in particular to work with it at it's higher level of precision.

When a datetime is needed as the leading key in a table, such as for logging tables, datetime2(7) causes far fewer first-key-column value collisions than does datetime (yes, if you want to insure unique key values yourself, you still have to add $IDENTITY or some other unique value, but that's not likely needed with datetime2(7) vs just datetime).

SQL can often process a lot of rows in 3ms. Thus, I nearly always use datetime2 for such columns.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 8

And yet, I've not seen where that actually matters. And, it won't matter unless you're doing RBAR updates of the same row more than once every 3.3 milliseconds because the date and time is calculated only once per query.

--===== Create a temp table to experiment with.
   DROP TABLE IF EXISTS #MyHead;
 CREATE TABLE #MyHead
        (
         SomeInt INT
        ,SomeDate DATETIME2(7) DEFAULT sysdatetime()
        )
;
--===== Populate the table.  This took more than 14 seconds to execute
 INSERT INTO #MyHead WITH (TABLOCK)
        (SomeInt)
 SELECT SomeInt = t.N
   FROM dbo.fnTally(1,30000000)t
;
--===== How many different dates do we come up with?
     -- Just one.
 SELECT SomeDate,COUNT(*)
   FROM #MyHead
  GROUP BY SomeDate
  ORDER BY SomeDate
;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kaj SSCommitted Points: 1605 More actions · Answer 9

Jeff Moden wrote:

And yet, I've not seen where that actually matters. And, it won't matter unless you're doing RBAR updates of the same row more than once every 3.3 milliseconds because the date and time is calculated only once per query.

Unless, of course, you do not use defaults, but instead insert the datetime2 value from a user defined scalar function. 🙂

Granted that's much slower, but yet not quite RBAR, I think.

create function dbo.fn_GetCurTime()
 returns datetime2(7)
as
begin
    return sysdatetime()
end
go

 INSERT INTO #MyHead WITH (TABLOCK)
        (SomeInt, SomeDate)
 SELECT t.N, dbo.fn_GetCurTime() as SomeDate
   FROM dbo.fnTally(1,30000000)t

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 10

If you're calling a scalar function, it's RBAR.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)