getdate() problem: where is the time taken from ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Matik - 25 Apr 2007 16:32 GMT

Hi,

I have a funny situation.
Within: MSSQL 2000 SP3, everything below described is running on same
PC.

there is a program running, which sends information to two other
programs.
This information is a timestamp of the program in datetime format,
which has it's own clock.
The clock is incremented each 5 seconds of the program, which
corespondes to aprox. one second of the real time.
It means, each on second of real time, the computer time is updated +5
seconds.

Now, two other applications, are getting this information at the same
moment.
FIRST of this applications, updates local time of the computer with
the time recieved.
SECOND application, writes a protocol to file, with timestamp read at
moment of writing from operating system.
Until now, all times are equal (the differences are not biger that
ms).

Now, the SECOND application, after writing a log into file (with
proper timestamp), calls SP in database.
It passes as input prm. the time recieved from very first program,
which is the same time as the current system time, which is the same
time the SECOND application writes to the log file.

This SP (besides other things) at the very beginning writes a log into
table, where two times are logged:
- getdate() to first column,
- timestamp recieved as input parameter.

Now the funny thing.
I would expect, the times are equal.
getdate() = '2007.04.25 10:00:00.000'
prm_recieved = '2007.04.25 10:00:00.000'

I would expect, that the time from getdate() will be shifted with
miliseconds (because of call etc).
getdate() = '2007.04.25 10:00:00.123'
prm_recieved = '2007.04.25 10:00:00.000'

I would even expect, that the time is shifted 5 seconds ahead:
getdate() = '2007.04.25 10:00:05.000'
prm_recieved = '2007.04.25 10:00:00.000'

or, 5 seconds and some miliseconds:
getdate() = '2007.04.25 10:00:05.123'
prm_recieved = '2007.04.25 10:00:00.000'

What I can not UNDERSTAND, why sometimes the time is equal, or
sometimes is ALMOST equal (within the diff of miliseconds), and why
sometimes the time is like this(!!!) :

getdate() = '2007.04.25 10:59:55.000'
prm_recieved = '2007.04.25 10:00:00.000'

It seams to me, the getdate is getting somehow the PERVIOUS local
system time, which was acctualy already upgraded ! Becasue all other
app's are having the proper value.

All other apps are writen in C++ and are very simple.
I was trying to set the SQLServer running property higher - with no
result.
I need to mention, there is SQLServer Agent running, and one procedure
with endless loop, with waitfor delay equal 2 seconds.
But non of them (changing the waitfor delay to other value, disabling
SQLAgent) fixes the problem.

Can somebody then tell me, where from is the time taken, or what is
the root problem of this issue?
Or what can it be?

Best regards,

Matik

Reply to this Message

Erland Sommarskog - 25 Apr 2007 22:57 GMT

> there is a program running, which sends information to two other
> programs.
[quoted text clipped - 4 lines]
> It means, each on second of real time, the computer time is updated +5
> seconds.

So you have an application that modifies the computer clock every
second, and now you are asking why:

> What I can not UNDERSTAND, why sometimes the time is equal, or
> sometimes is ALMOST equal (within the diff of miliseconds), and why
> sometimes the time is like this(!!!) :
>
> getdate() = '2007.04.25 10:59:55.000'
> prm_recieved = '2007.04.25 10:00:00.000'

getdate() does not always reflect you recently updated system time.

I guess the answer is that there is not really reason that Windows and
SQL Server would behave the way you may want it to in this very special
scenario.

One reason that getdate() apparently lags behind is that getdate() has
a resolution of 3.33 ms which after all is quite a long time in a computer.
Assuming that SQL Server reads the system clock every 3.33 ms, getdate()
could seemingly lag behind your manipulated time.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

Matik - 26 Apr 2007 11:00 GMT

First, thanks Erland for the answer!

> So you have an application that modifies the computer clock every
> second, and now you are asking why:

:) No ... of course, I'm not confused about the time changes :)

I'm expecting them ...
Confused for me, was this:

> > getdate() = '2007.04.25 10:59:55.000'
> > prm_recieved = '2007.04.25 10:00:00.000'
[quoted text clipped - 9 lines]
> Assuming that SQL Server reads the system clock every 3.33 ms, getdate()
> could seemingly lag behind your manipulated time.

Ok.
So ... that means for me as fallow:
The getdate() is not taking the current system time, only the buffered
SQLServer system time.
That means as well, that the time between changing system time,
writing into log (application) and calling procedure in DB, until this
position where the getdate() is called, MUST be shorter than a maximum
time of 3.33 ms.

Well, this is not I was thinking getdate() is doing:(
Is the current_timestamp function behaviour exactly in this way?
(probably yes, since in BOL says that this is the same as getdate())

Thank's Erland again for help.

Matik

Reply to this Message

Erland Sommarskog - 26 Apr 2007 12:02 GMT

> So ... that means for me as fallow:
> The getdate() is not taking the current system time, only the buffered
> SQLServer system time.

I like to stress that is my own speculation of how the internals work.

> That means as well, that the time between changing system time,
> writing into log (application) and calling procedure in DB, until this
> position where the getdate() is called, MUST be shorter than a maximum
> time of 3.33 ms.

If my theory is correct, yes, this appears to be a correct conclusion.

> Well, this is not I was thinking getdate() is doing:(
> Is the current_timestamp function behaviour exactly in this way?
> (probably yes, since in BOL says that this is the same as getdate())

I would expect that CURRENT_TIMESTAMP to be just a synonym fot getdate(). It
would be funny if two equivalent functions are implemented in different
ways.

I also like to point out that this kind of behaviour that could be different
in different versions of SQL Server, or even in different service packs.

Signature

Reply to this Message

Matik - 27 Apr 2007 14:59 GMT

> > The getdate() is not taking the current system time, only the buffered
> > SQLServer system time.
[quoted text clipped - 7 lines]
>
> If my theory is correct, yes, this appears to be a correct conclusion.

I was thinkig about one more thing ...
Is there any way, to force sql server to refresh it's time?
Let's say, that by the procedure call, I will force him, to refresh
it's time ... will it be possible somehow?

Matik

Reply to this Message

Erland Sommarskog - 27 Apr 2007 22:34 GMT

> I was thinkig about one more thing ...
> Is there any way, to force sql server to refresh it's time?
> Let's say, that by the procedure call, I will force him, to refresh
> it's time ... will it be possible somehow?

Since all this is about behaviour that is strictly internal to SQL Server,
the likelyhood that there is a interface, documented or undocumented,
to affect this behaviour is about nil. Who knows, maybe there is a trace
flag, but don't stay up all night looking for it.

If you are on SQL 2005, you could write a CLR function which retrieves
the system time from Windows, with the regular 100 ns precision.

If you are on SQL 2000, you would have to write an extended stored
procedure, which may not be performant enough. (There is quite a cost
for the eontext switch.)

But getdate() seems dead in the water when you are living in the fast lane
like you do.

Signature

Reply to this Message

Matik - 27 Apr 2007 23:04 GMT

> to affect this behaviour is about nil. Who knows, maybe there is a trace
> flag, but don't stay up all night looking for it.

I wont :) Have other things to do as well :)

> If you are on SQL 2005, you could write a CLR function which retrieves

I'm on 2000 ... right now ....

> If you are on SQL 2000, you would have to write an extended stored

This is what I was thinking of...

> procedure, which may not be performant enough. (There is quite a cost
> for the eontext switch.)

This is what I was afraid of :(

> But getdate() seems dead in the water when you are living in the fast lane
> like you do.

I just need to change aproach probably, and try to solve it in
different way.
Probably the solution is what I did right now ...
Just, a the beginning, I'm sending procedure to sleep (waitfor delay).
It is not nice, and slows everything down, but maybe ... will be fast
enougth...
Otherwise ... try to do smth. else.

Thanks Erland again for your help and patience.

Best regards

Matik

Reply to this Message