Do you know your getdate()?

Question

Do you know your getdate()?

Viewing 15 posts - 46 through 60 (of 63 total)

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

JacekO SSCertifiable Points: 6610 More actions · Answer 1

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.

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

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[/url]
Learn Extended Events

Kim Claybaugh SSC-Addicted Points: 433 More actions · Answer 3

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 SSC Guru Points: 1004311 More actions · Answer 4

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 5

Jeff Moden (5/12/2010)
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.

I works in 2000 as well. Anyone have 7.0 or 6.5 to test?

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

martyn.bates Valued Member Points: 56 More actions · Answer 6

Like others I've found that SYSDATETIME is not as accurate as GETDATE.

My results:

H/W Xeon quad-core 2.83GHz, 2GB ram, SSD raid

S/W Win2k3 R2 x64, SQL 2008 developer (not R2)

GETDATE : count 19329, time 3.33385 elapsed 1:04

CURRENT_TIMESTAMP: count 19345, time 3.33404 elapsed 1:04

SYSDATETIME: count 4175, time 15.6525, elapsed 1.05

😉

SqlOnMyMind SSCertifiable Points: 5062 More actions · Answer 7

When I first read through the code, my first thought was that load on the system would impact the calculations. So I did a quick and dirty test, taking advantage of a big, ugly query I had. I added a timer to the whole getdate loop and ran with no other queries running. Then I started up 5 independent queries and ran the getdate loop. The load definitely impacted the calculation. (Note: this was run on SQL 2005 server.)

-- test impact of load on getdate() calculation.

-- (normal load on system)

16718

3.34807

loop getdate 55973 Milliseconds duration

-- start up 5 big, ugly queries

49369

5.01339

loop getdate 247506 Milliseconds duration

JacekO SSCertifiable Points: 6610 More actions · Answer 8

I would like to thank everybody who took the time to read the article and all the respondents who provided their input on this forum. I don’t think I can answer all the posts individually but I will try to answer the subjects brought up the most frequently.

The Interview – first of all it was just a ‘teaser’ to get you into reading the article. If you carefully read the hypothetical answer presented in the article, then you would realize that this answer includes a logical deduction that was flawed. The flaw lays in the fact that the answer ties the getdate() and sysdatetime() precision to the underlying datatype. The article tries to explain that the precision of such functions is dependent on OS timers available to the SQL Server. Many of you pointed out that I was wrong because they got 3.33 ms on getdate() on their system. Well, good for you. You got a SQL Server version (including SP) and a OS version combination that allows the getdate() to be more precise or accurate (some argued about the term I should have used). But there are plenty of responses with the ~16 ms precisions on getdate() or sysdatetime() to prove that I did not made this whole thing up. And no one got even remotely close to the 100 ns on the sysdatetime() function…

I was not really surprised that there were people who got 3.33 ms on the getdate(). I was kind of disappointed that I could not find any of the systems around here to achive this kind of precision. Well, let’s blame it on the corporate standardization – the pool of available systems was not diversified enough. What really surprised me, was that people were reporting worst performance of sysdatettime() vs getdate() on the same system.

OK, let’s look at the load vs precision aspect. Again – this is related to the fact that Windows is not a real time OS. So, if you really starve the processor by taxing it at 100% non stop for an extended period of time it, will ‘skip’ or ‘slowdown’ some of the OS functions and the timers will not get updated every time they should. The duration of the queries will vary depending on the load but I think if you keep the load reasonable, the 20% or 90% load on the CPU will not affect the precision of the time functions much.

Someone mentioned that if they needed to know the answer to the ‘interview’ question, the Books Online would be the place to get an answer. Well, I don’t think this particular question does have an answer in the Books Online and if all the answers to all SQL Server questions could be found in Books Online then we would not need such a great place as SQLServerCentral.com. Would we?

PS.

If you are not sick and tired of this subject…

Anyone who posted your results could you please provide your SQL version including SP. And maybe OS version, including SP as well.

Thanks. I am still trying to figure out if there is a logic to this behavior.

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

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 9

i run the query in my server with 2000 as OS and sql server 2005 sp2, the result is

5781

3.33333

and in my local system it with windows XP and sql server 2005 sp2 the results is like

7196

3.43524

Brandie Tarvin SSC Guru Points: 173045 More actions · Answer 10

Jeff Moden (5/12/2010)
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.
I haven't checked the other versions of SQL Server but SELECT CURRENT_TIMESTAMP works just fine in 2k5.

Works fine in 2k8 (eval copy) as well.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]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.

changbluesky SSC Eights! Points: 807 More actions · Answer 11

Thnaks:-) Know the precision for the time function GETDATE(), wonderful ideas for future precision using.

Cadavre SSC-Forever Points: 41690 More actions · Answer 12

Very interesting.

3750

3.336

Microsoft SQL Server 2005 - 9.00.4028.00 (Intel X86)

Oct 20 2008 19:45:04

Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

Big load going on with my 2008 server, so results are probably whacky.

--GETDATE

700

15.6286

--SYSDATETIME

963

15625

Microsoft SQL Server 2008 (RTM) - 10.0.1763.0 (X64)

Sep 18 2008 20:59:12

Developer Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

john-645829 SSC Enthusiast Points: 117 More actions · Answer 13

Using Windows 7 Enterprise, 64-bit OS

Intel Core i7 CPU Q720 @1.60 GHz 1.60 GHz

4GB memory

and SQL Server 2008

using the GetDate() code...

I get

4179

3.60995

and when I run the same code in a stored procedure, I get

4126

3.41638

john-645829 SSC Enthusiast Points: 117 More actions · Answer 14

and the more precise datetime2 test gives:

18367

1034.88

John Birch

jay-h SSCoach Points: 18816 More actions · Answer 15

Interesting exercise, but keep in mind that for most of us this is academic since most server clocks are not thermally compensated and easily drift by a mS every couple of minutes (or when a fan kicks on).

So unless you have a real network clock (symmetricon etc), all those decimal digits are meaningless.

...

-- FORTRAN manual for Xerox Computers --