SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Do you know your getdate()?


Do you know your getdate()?

Author
Message
Brandie Tarvin
Brandie Tarvin
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38843 Visits: 9283
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
shane.vincent
shane.vincent
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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.
José.Cruz
José.Cruz
Old Hand
Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)

Group: General Forum Members
Points: 320 Visits: 4051
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
rsaxer@cox.net
rsaxer@cox.net
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 638
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



JacekO
JacekO
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1591 Visits: 616
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.

JacekO
JacekO
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1591 Visits: 616
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>

Tongue


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.

JacekO
JacekO
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1591 Visits: 616
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.

SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68071 Visits: 18570
Thanks for the nice article.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Kim Claybaugh
Kim Claybaugh
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 509
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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219918 Visits: 42002
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search