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?
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.