Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»»

Do you know your getdate()? Expand / Collapse
Author
Message
Posted Wednesday, May 12, 2010 10:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:55 AM
Points: 7,053, Visits: 6,214
If someone asked me about the accuracy in an interview, my response would be "I don't know. I've never needed GetDate() or related functions to be that precise. If and when I do need to know, I'd look it up in Books Online."

I'd probably be dinged in the interview, but at least I wouldn't be taking potshots and getting them wrong. @=)


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #920665
Posted Wednesday, May 12, 2010 10:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 24, 2013 10:37 AM
Points: 1, Visits: 39
Just a thought, but I also got a more accurate GETDATE return value than the original article.
(19430 ,3.52769) I think that a lot of this has to do with what else is running on the SQL box.
So the date accuracy will be dependent on the load of the server to a degree.

At least that is what it seems like.
Post #920666
Posted Wednesday, May 12, 2010 10:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 1:00 PM
Points: 247, Visits: 3,865
Hi there,

Not that it really matters, but if i use the time difference every time getdate() returns a differente value, i reach to a better result.

DECLARE @TimeStart DATETIME
DECLARE @Time DATETIME
DECLARE @TimeEnd DATETIME
DECLARE @I INT
DECLARE @Count INT
DECLARE @Difference INT
SET @I = 0
SET @Count = 0
SET @Difference = 0
SET @TimeStart = GETDATE()
SET @Time = @TimeStart
WHILE @I < 10000000
BEGIN
SET @TimeEnd = GETDATE()

IF @TimeEnd <> @Time
BEGIN
SET @Count = @Count + 1
SET @Difference = @Difference + DATEDIFF(millisecond, @Time, @TimeEnd)
SET @Time = @TimeEnd
END
SET @I = @I + 1
END
PRINT @Count
PRINT DATEDIFF(millisecond, @TimeStart, @TimeEnd) / CAST(@Count AS REAL)
PRINT @Difference / CAST(@Count AS REAL)

With this code i get the following result on my SQL2005 instance:

3478
3.33324
3

José Cruz
Post #920696
Posted Wednesday, May 12, 2010 11:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 20, 2014 9:59 AM
Points: 119, Visits: 400
Win Xp 32 bit SP3 (Under VMware Workstation)
SQL Server 2008 (10.0.2531)

Getdate(): 4291 values with precision 3.35027
SysDateTime(): 1903 values with precision 10.0641



Post #920698
Posted Wednesday, May 12, 2010 11:29 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
Chris Howarth-536003 (5/12/2010)
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


Chris, good link, and it basically describes the underlying problems that cause the ~16ms or 3.3 ms for getdate() and no better precision then 1 ms for sysdatatetime(). I guess some OS configurations expose the higher percision timer to SQL Server so this is why some people come with the 3.33ms for getdate(). It might be because of SP versions on the OS or SQL Sevrer and also the boot.ini switch. There is some indication in the article that Microsoft recently introduced some means of exposing a higher precision timers to the SQL Sevrer with the new service packs. That might explain why some people can get better results then the ~16ms I got in my tests.
Most precision timers under Windows are tick counters and are good at calculating the time difference - not the current time. The getdate() and sysdatetime() functions are only as precise as the underlying OS timer.


---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #920730
Posted Wednesday, May 12, 2010 11:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
thisisfutile (5/12/2010)
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>

:P


I think you might be actually pointing out to the reason of the differing results. Your server is running SP2 and workstation is running SP3.

The link Chris posted to the MS blog (see my reply above) indicates that MS introduced some changes in SP3 of 2005 so the server can use a higher precision timers. If the timer has better presision then 3.33 ms then the getdate will be limited by the datatype precision. If the server is using the lower precision timer then you will see the ~16ms getdate() precision.


---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #920744
Posted Wednesday, May 12, 2010 12:00 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
shane.vincent (5/12/2010)
Just a thought, but I also got a more accurate GETDATE return value than the original article.
(19430 ,3.52769) I think that a lot of this has to do with what else is running on the SQL box.
So the date accuracy will be dependent on the load of the server to a degree.

At least that is what it seems like.


Only the first number (how many times the value changed) is affected by the load because the overall duration of the query is going to be affected. The higher the load on the box the higher the first number is going to be. But the second number (the "precision" number) does not really change that much with the load - just a fraction of a percent.

Most people concentrated on the getdate() values but I think when you look at the sysdatetime() the difference between what the data type can store and the precision of what the function can return is much more dramatic. With getdate() we are talking of a difference of ~16ms (for many of us) vs ~3ms which is just about 5 times. When you look at the systdatetime() of about 1ms vs the 100ns we are talking 10000 times.


---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #920758
Posted Wednesday, May 12, 2010 12:09 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:16 PM
Points: 20,467, Visits: 14,101
Thanks for the nice article.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #920764
Posted Wednesday, May 12, 2010 2:44 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:08 PM
Points: 392, Visits: 389
Thank you for such a nice, informative article!
This is why this site is so useful - we can learn from others and not have to try and duplicate everything ourselves.

If we needed more resolution (e.g. microseconds) has anyone checked the date/time functions in the programming languages and used CLR to access those times?
Post #920867
Posted Wednesday, May 12, 2010 3:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:29 PM
Points: 35,977, Visits: 30,266
Joe Celko (5/12/2010)
I just wish that Microsoft has used the ISO/ANSI Standard CURRENT_TIMESTAMP and CURRENT_DATE instead of more dialect. Of course, they are going to have trouble with TIMESTAMP(n) thanks to the old Sybase Code Museum


I haven't checked the other versions of SQL Server but SELECT CURRENT_TIMESTAMP works just fine in 2k5.


--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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #920900
« Prev Topic | Next Topic »

Add to briefcase «««34567»»»

Permissions Expand / Collapse