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,
MatikMatik (marzec@.sauron.xo.pl) writes:
Quote:
Originally Posted by
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.
So you have an application that modifies the computer clock every
second, and now you are asking why:
Quote:
Originally Posted by
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.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||First, thanks Erland for the answer!
Quote:
Originally Posted by
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:
Quote:
Originally Posted by
Quote:
Originally Posted by
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.
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|||Matik (marzec@.sauron.xo.pl) writes:
Quote:
Originally Posted by
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.
Quote:
Originally Posted by
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.
Quote:
Originally Posted by
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.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||
Quote:
Originally Posted by
Quote:
Originally Posted by
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.
>
Quote:
Originally Posted by
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.
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|||Matik (marzec@.sauron.xo.pl) writes:
Quote:
Originally Posted by
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.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||
Quote:
Originally Posted by
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 :)
Quote:
Originally Posted by
If you are on SQL 2005, you could write a CLR function which retrieves
I'm on 2000 ... right now ...
Quote:
Originally Posted by
If you are on SQL 2000, you would have to write an extended stored
This is what I was thinking of...
Quote:
Originally Posted by
procedure, which may not be performant enough. (There is quite a cost
for the eontext switch.)
This is what I was afraid of :(
Quote:
Originally Posted by
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
No comments:
Post a Comment