Do you know your getdate()?

Question

Do you know your getdate()?

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

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

Leendert van Staalduinen SSC Eights! Points: 877 More actions · Answer 1

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.

thisisfutile Hall of Fame Points: 3668 More actions · Answer 2

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.

JacekO SSCertifiable Points: 6610 More actions · Answer 3

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]

wschampheleer SSCertifiable Points: 5504 More actions · Answer 4

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]

JacekO SSCertifiable Points: 6610 More actions · Answer 5

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]

WayneS SSC Guru Points: 95460 More actions · Answer 6

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

seth.orell SSC Veteran Points: 259 More actions · Answer 7

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.

adrian.buzila Ten Centuries Points: 1116 More actions · Answer 8

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)

SqlAsSecondLanguage Mr or Mrs. 500 Points: 529 More actions · Answer 9

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.

thisisfutile Hall of Fame Points: 3668 More actions · Answer 10

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

bp346576868686 SSC Rookie Points: 42 More actions · Answer 11

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.

SQLZ SSChampion Points: 12872 More actions · Answer 12

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

Karl
source control for SQL Server

thisisfutile Hall of Fame Points: 3668 More actions · Answer 13

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>

😛

JacekO SSCertifiable Points: 6610 More actions · Answer 14

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]

Chris Howarth-536003 SSCommitted Points: 1775 More actions · Answer 15

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