Do you know your getdate()?

Question

Do you know your getdate()?

Viewing 15 posts - 31 through 45 (of 63 total)

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

Alastair Marshall Valued Member Points: 51 More actions · Answer 1

Of course, the returned values will vary here because this is not factoring in the execution time of the getdate() or systemgetdate() functions. They will each have a cost associated with each call, so the answer in this logic isn't so much just a question of the accuracy of the functions, but also the processing time for these calls. These will vary depending on the hardware of the machine that you are on and other activity on the system.

bluelightning1 SSC Rookie Points: 40 More actions · Answer 2

Thank you for the information we don't for the moment use the precision described in our day to day processing of data, but it is definitely something to keep in mind for the future.

Hobbes-230252 SSC Rookie Points: 42 More actions · Answer 3

... Soooo how do we notify the article author that he was wrong?

gitmo Old Hand Points: 379 More actions · Answer 4

I think the article is referring to the ACCURACY of the two functions, not to the precision.

Alastair Marshall Valued Member Points: 51 More actions · Answer 5

Alastair Marshall

Valued Member

Points: 51

May 12, 2010 at 8:10 am

#1166235

Of course I meant systemdatetime()!

noeld SSC Guru Points: 96590 More actions · Answer 6

I don't know what is all this fuzz about ... The results are going to be very server-dependent you don't know what other activities are happening at the same time you are running these loops. In my opinion these tests really represent nothing.

* Noel

Rich Mechaber SSChampion Points: 11130 More actions · Answer 7

Nope: Ran this on my 32-bit box and got:

8650

3.34139

Specs:

CPU: Xeon E5450 @ 3 GHz

OS: 2003 Server, Standard Ed., R2, SP2

Guess you'll have to re-apply for a job elsewhere now 😉

Let us know when you've figured out the rationale for this varying result.....

Rich

José.Cruz Right there with Babe Points: 722 More actions · Answer 8

Hi there,

Windows 7 Professional, 32-bit operatibg system.

3468

3.33333

- getdate()

3785

3.33316

-sysdatetime()

22562

1000.77

José Cruz

vincent.miele Old Hand Points: 319 More actions · Answer 9

Windows 2008

SQL Server Enterprise 64bit 2008 sp1

Getdate /2nd run

88047 /113305

4.8 /7.44

SysDateTime

127186 /137377

3476.36 /6143.44

Windows 2003

SQL Server Enterprise 2008 sp1

GetDate /2nd run

19830 /36528

3.367 /3.464

SysdateTime

127186 /8437

3476.36 /15636

Perhaps the measurements are affected by load on the server. I also notice that there is a CPU and network load on my client workstation from SSMS while the queries are running, could the client also be a factor?

shane.vincent Newbie Points: 5 More actions · Answer 10

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.

Brandie Tarvin SSC Guru Points: 173033 More actions · Answer 11

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/[/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.

José.Cruz Right there with Babe Points: 722 More actions · Answer 12

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 SSC Veteran Points: 209 More actions · Answer 13

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 SSCertifiable Points: 6610 More actions · Answer 14

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.

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

JacekO SSCertifiable Points: 6610 More actions · Answer 15

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>
😛

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.

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