May 12, 2010 at 3:24 am
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.
May 12, 2010 at 6:49 am
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.
May 12, 2010 at 7:19 am
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]
May 12, 2010 at 7:24 am
May 12, 2010 at 7:25 am
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]
May 12, 2010 at 7:32 am
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
May 12, 2010 at 7:33 am
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.
May 12, 2010 at 7:34 am
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)
May 12, 2010 at 7:37 am
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.
May 12, 2010 at 7:40 am
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
May 12, 2010 at 7:44 am
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.
May 12, 2010 at 7:44 am
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
May 12, 2010 at 7:51 am
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>
😛
May 12, 2010 at 7:52 am
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]
May 12, 2010 at 8:00 am
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():
Chris
Viewing 15 posts - 16 through 30 (of 63 total)
You must be logged in to reply to this topic. Login to reply