Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Nanoseconds


Nanoseconds

Author
Message
Revenant
Revenant
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5771 Visits: 4706
I took me twenty minutes of reading BoL and head scratching... Thanks, Tom!
john.arnott
john.arnott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1466 Visits: 3059
Would method 1 be reliable even in the early morning hours? As SYSDATETIME() is non-deterministic, could it not use two slightly different values for the two calls?

I get the feeling that you, Tom, considered that and have an explanation of the definition of "deterministic."
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10700 Visits: 11991
john.arnott (11/9/2012)
Would method 1 be reliable even in the early morning hours? As SYSDATETIME() is non-deterministic, could it not use two slightly different values for the two calls?

I get the feeling that you, Tom, considered that and have an explanation of the definition of "deterministic."

Actually, you give me too much credit - I didn't even consider that for method 1 (not sure whether that was because I was counting it as unreliable anyway or because I didn't think of it - it's a while since I submitted the question).

I did consider it for method 4, because I think I read somewhere that CTEs generally work by text substitution, so that if the same CTE is referred to more than once in a query the two instances may deliver different values. Of course the optimizer may decide to common up textually equal expressions, provided that doing so doesn't screw up store management, but I don't know whether it would do that or not; and of course recomputing the content of a CTE could lead to some new variations of queries whose semantics could be altered according to whether intermediate results are spooled or not (so if it's an update query, new instances of the Harlequin Problem - of course according to some people where insert and delete queries have their semantics modified in a similar manner that would be the Harlequin Problem too) so it's sometimes essential to spool a CTE. Spooling every CTE would be a performance nightmare, so that presumably doesn't happen. All that leaves me non the wiser about whether this particular query will have a problem, so I decided to test method 4 fairly thoroughly. On my machine, with a reasonable amount of background work going on, I invoked this method 10000 times in a loop to write a row into a table with the time and the nanoseconds since midnight (the final select of method 4 was replace by
insert #pig select (1000000000 * CAST(secs.ss as bigint)+CAST(datediff(NS,secstart.start,t) as bigint)) , t 
from secs cross join dy cross join secstart cross join noww;


to do this - for some reason in my notes for the test all occurrences of now have two ws, presumably I wanted to avoid the reserved [???] word) and then counting the number of rows where the nsec column was the same as the nanosec count calculated by method 2 from the t column), and also counting the number of rows where it was different, delivered counts of 10000 for same, and 0 for different. This was repeated at several times of day and with several different background workloads added (AV and spyware scan, database backups, intense numeric stuff, massive automatic text edits, indexing files - [google desktop], network dowloads, varios combinations of these) with the same results.
What that tells me is that on my machine either the optimiser decides to calculate that CTE just once, or the time difference between the two times it calculates it is always less that 100NS, and I think the latter implies that the query (up to the point of having the output ready to write to the log) takes less time than the minimum interval (if there is such a thing in this verson of NT) that a thread is given when selected before it will be interupted. So for me method 4 is reliable. The scary thing is that I have no real proof that it is actually reliable on any other machine, or that it will be reliable in SQLK 2012 (I'm using 2008 R2), or that it will be reliable if I substitute some version of Windows 7 for Windows XP Pro. Despite that, I'm sure that it is indeed reliable, at least in SQL 2008 R2.
Of course this risk can be eliminated completely by having the query work off a variable previously initialised to sysdatetime(), and that applies equally to method 1 (for the few hours each day that it works), so these methods can be made safe (for method 1, safe for a few hours per day) by converting them into two statements each instead of 1. But of course I prefer method 2 to method 4, because it's simpler and even in its single statement form doesn't carry this risk.

edit: Why avoid "now" as a reserved word? It isn't a reserved word in T-SQL. I must have been thinking of some other language (maybe ISO SQL - it has more reserved words than T-SQL - but I don't think so).

Tom

SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21073 Visits: 18259
Koen Verbeeck (11/8/2012)
sanket kokane (11/7/2012)
I got it wrong ...

you should mention sql server Version in question .

Sysdatetime () not present in sql 2000 and 2005


So, for which versions do you think the question is meant? Obviously for 2008 and up. There's also a consensus that versions who are no longer supported by Microsoft do not count for Questions of the Day.


+1

Also, if you are testing on an old version no longer under support then at that point you should include version.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

seankyleprice
seankyleprice
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 70
I personally think the answer should be 'none of the above'. The accuracy of sysdatetime() depends on the computer hardware and version of Windows on which the instance of SQL Server is running. Queries 2 and 4 may give an accurate value for the number of nanoseconds since when your server believes midnight to have been but not 'reliably' when midnight actually was unless you are confident that the date time of your server is accurate to 100 nanoseconds.
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10700 Visits: 11991
seankyleprice (11/16/2012)
I personally think the answer should be 'none of the above'. The accuracy of sysdatetime() depends on the computer hardware and version of Windows on which the instance of SQL Server is running. Queries 2 and 4 may give an accurate value for the number of nanoseconds since when your server believes midnight to have been but not 'reliably' when midnight actually was unless you are confident that the date time of your server is accurate to 100 nanoseconds.

Excellent point.

We can of course take it that the time referred to as midnight in the context of how long has the computer been going since midnight as being the time when it thought midnight was, but that doesn't help because reliability is still affected by the clock drift between then and now.

Typical systems today, even those with very good hardware, despite usually having excellent clock management software that (when correctly configured, which may not be all that common) uses data from a reliable time server to determine how much drift there is in the hardware would not be able to run reliably with a drift rate between time server inputs of less that 37 microseconds per annum (that's a few decimal orders of magnitude better than typical, I think), so if "reliable to 100 nanoseconds" really means just that you are right and the correct answer is "none of the above". Even if "reliable to the nearest 100 nanoseconds" means "accurate count of the clock's nominally 100 ns ticks" a correction from the time server might have occurred during the interval and disturbed the tick count so that the result would be unreliable anyway.

When I wrote the question I meant "reliable within the capability of the system to determine the time". I should have included those words, or something like them, in the question.

Tom

David Conn
David Conn
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3034 Visits: 1116
I'm happy that we're starting to get questions with the more modern Data Types. Datetime should have been retired when Date, Time and DateTime2 were made available.

David
Dineshbabu
Dineshbabu
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1074 Visits: 569
Difficult one..

--
Dineshbabu
Desire to learn new things..
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search