Why do some DBAs avoid DATETIME2?

  • 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

  • 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.

  • 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)
    Intro to Tally Tables and Functions

  • 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)
    Intro to Tally Tables and Functions

  • 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)
    Intro to Tally Tables and Functions

  • 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.

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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)
    Intro to Tally Tables and Functions

  • 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
  • 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)
    Intro to Tally Tables and Functions

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

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