Do you know your getdate()?

  • P.S. I was too impatient: the very same Windows 2000 Professional SP4 machine, SQL Server 2000 Query Analyzer, after 7:44 minutes: 27,553 / 16.8266. The Access .ADP file return results after 12–22 sec.

    I stop trying to understand all these differences, Leendert.

  • From my XP workstation, running the first query only, if I connect to my local SQL 2008 install then I get the 3.3333 time. If I connect to our Server's SQL 2005 install, I get the 15.6xxx time.

    Just wanted to share my experience.

  • Wow.

    I did not expect to get so many posts before I even show up for work.

    Looking at all the replies, one potential explanation for some of the 'better' precision numbers for GETDATE() might be possible.

    64 bit OS.

    Looks like whoever run the tests on 64 bit OS got the 3.33 ms precision and whoever run it on 32 bit got ~16 ms presicion.

    If anyone of you got the 3.33 ms on a 32 bit box please reply with more specifics on the OS, CPU and SQL Server version. I don't think anything else could explain the difference. I did to run the tests on different configurations but I have to admit I did not have access to a 64 bit OS.

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • I got 3.33 ms on a 32bit OS: WIN XP SP3 / SQL Server 2008 SP1 on dual core machine (Intel T2500).

    Regards,

    Willem
    http://wschampheleer.wordpress.com[/url]

  • SQLZ (5/12/2010)


    I get 3.3 ms here using your own code.

    The worst part about it is you would have not given the job to someone who correctly responded with 3.3ms for GETDATE() precision:-P

    Well, life is tough.;-)

    But seriously, back then, when I was in the position to interview people I did not care as much what knowledge did they have but rather what could they do with it.

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • JacekO (5/12/2010)


    Wow.

    I did not expect to get so many posts before I even show up for work.

    Looking at all the replies, one potential explanation for some of the 'better' precision numbers for GETDATE() might be possible.

    64 bit OS.

    Looks like whoever run the tests on 64 bit OS got the 3.33 ms precision and whoever run it on 32 bit got ~16 ms presicion.

    If anyone of you got the 3.33 ms on a 32 bit box please reply with more specifics on the OS, CPU and SQL Server version. I don't think anything else could explain the difference. I did to run the tests on different configurations but I have to admit I did not have access to a 64 bit OS.

    Interesting article. Even more interesting is the differing values coming out of other peoples servers.

    For me:

    OS: Windows XP 32 bit, SP2

    CPU: 2 Processors (Core Duo)

    SQL: 2008 Dev, 10.0.2531

    GetDate(): 6507, 3.33333

    SYSDATETIME(): 1806, 15625.2

    OS: Windows Server 2003, 32-bit, SP2

    SQL: 2005 EE, 9.0.4053

    CPU: 2 processors

    GetDate(): 17814, 3.41192

    I can test on my 64-bit laptop tonight...

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • My results running SqlServer 2008 locally on Windows XP x86:

    First Query: 798/15.6266, 766/15.6266, 784/15.625

    Second Query: 1089/15625, 1055/15625, 1130/15625

    I exhibit the same precision regardless of method used.

  • I got 3.33 on all tests

    1) win xp, 32bit, SQL Server 2008 Developer Edition

    2) win xp, 32bit, sql server 2005 Express Edition

    3) SQL Server 2005 Developer Edition (64-bit)

    4) SQL Server 2005 Enterprise Edition (64-bit)

  • I have a new laptop (yes, laptop) running WIN7 64-bit with both SQL 2008 Enterprize and a SQLExpress install.

    Both the 2008 instance and the SQLEXPRESS install returned the getdate() function with 3.3333 precision.

    The point of the article was to know your getdate() function, and it's definitely opened my eyes on the variance that can be out there. I have never had to work with this level of precision in the past, but its good to know.

  • JacekO (5/12/2010)


    If anyone of you got the 3.33 ms on a 32 bit box please reply with more specifics on the OS, CPU and SQL Server version.

    My XP workstation is 32-bit with SQL Server 2008 Express (SP3) ... and I get 3.3333 on the first query

    My server is SBS 2003, 32-bit with SQL Server 2005 Workgroup (SP2) ... and I get 15.6xxx on the first query

    HTH

  • Here is mine

    SQLServer 2000 on XP SP3 running locally 3596 : 15.7664

    SQLServer 2005 on Server 2008 via Terminal Server 2815 : 3.33321

    SQLServer 2005 on Vista from Server 2008 via Terminal Server 108619 : 4.07807

    All SQLServers are developer versions.

    Should not matter but I point out that I terminal servered in to Server 2008 from the top XP machine and ran the query. Then in that terminal sesion connected to the Vista SQLServer and ran again for the third result.

  • Turns out that by repeatedly running GETDATE() as described in the article, all you're doing is measuring the clock ticks that the machine you're running on is capable of.

    On some machines I tested (64 bit or otherwise) it's 16ms and on other machines it is 3ms, or possibly less - but of course, GETDATE() cannot resolve any ticks below 3ms.

    So, GETDATE() (or rather, the datetime datatype) has a precision of 3.33ms - but whether you can illustrate this using GETDATE() is dependant on the spec of the machine you're on.

    Check out this article, specifically the Sidebar near the bottom. http://msdn.microsoft.com/en-us/library/aa175784(SQL.80).aspx

  • thisisfutile (5/12/2010)


    JacekO (5/12/2010)


    If anyone of you got the 3.33 ms on a 32 bit box please reply with more specifics on the OS, CPU and SQL Server version.

    My XP workstation is 32-bit with SQL Server 2008 Express (SP3) ... and I get 3.3333 on the first query

    My server is SBS 2003, 32-bit with SQL Server 2005 Workgroup (SP2) ... and I get 15.6xxx on the first query

    HTH

    Expanding on my previous respons...

    If I run the first query directly on the server, it still reports 15.62xx. After seeing 3.33 on my workstation and it's local SQL install, I sort of expected to see 3.33 when running the query directly on the server. </naivety>

    😛

  • Now I am really puzzled.

    Well, the whole thing has to do with the OS timer. When I bumped into the 16 ms precision of getdate() over a year ago I did some research on the internet. I could not find anything specific to SQL Server but some articles pointed out that it is possible to alter the frequency of the system timer by running multimedia software.

    In order to test the 16 ms precision I even created a .NET app that run a code similar to the SQL query (same logic) and I never could brake the 16 ms barrier.

    I was never successful in altering the system timer, but based on the results of all your queries it looks like it is possible.

    Now, the $150000 question is: what is that thing that makes the difference?

    ---------------------------------------------
    [font="Verdana"]Nothing is impossible.
    It is just a matter of time and money.[/font]

  • This makes an interesting read, particularly regarding SQL 2005 SP3's attempts to lower granularity to 1ms, but the article doesn't specifically mention GETDATE():

    http://blogs.msdn.com/psssql/archive/2009/05/29/how-it-works-sql-server-timings-and-timer-output-gettickcount-timegettime-queryperformancecounter-rdtsc.aspx

    Chris

Viewing 15 posts - 16 through 30 (of 64 total)

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