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 4:22 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, March 10, 2014 3:52 PM
Points: 1,380, Visits: 2,681
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
Post #920907
Posted Thursday, May 13, 2010 6:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 8:12 AM
Points: 48, Visits: 37
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
Post #921168
Posted Friday, May 14, 2010 10:06 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:00 AM
Points: 1,526, Visits: 1,834
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

Post #922169
Posted Friday, May 14, 2010 1:01 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
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.


---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #922269
Posted Sunday, May 16, 2010 10:53 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:15 AM
Points: 1,128, Visits: 758
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


Post #922709
Posted Tuesday, May 18, 2010 4:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:56 AM
Points: 7,073, Visits: 6,231
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 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 #923425
Posted Wednesday, May 26, 2010 10:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 01, 2010 4:19 AM
Points: 39, Visits: 150
Thnaks Know the precision for the time function GETDATE(), wonderful ideas for future precision using.
Post #928722
Posted Thursday, July 08, 2010 3:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:37 AM
Points: 2,404, Visits: 7,313
Very interesting.

3750
3.336
Microsoft SQL Server 2005 - 9.00.4028.00 (Intel X86)
Oct 20 2008 19:45:04
Copyright (c) 1988-2005 Microsoft Corporation
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
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)




Not a DBA, just trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #949121
Posted Friday, February 03, 2012 2:40 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 02, 2012 4:23 AM
Points: 61, Visits: 12
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
Post #1246307
Posted Friday, February 03, 2012 2:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 02, 2012 4:23 AM
Points: 61, Visits: 12
and the more precise datetime2 test gives:

18367
1034.88


John Birch
Post #1246310
« Prev Topic | Next Topic »

Add to briefcase «««34567»»

Permissions Expand / Collapse