Re: Interesting observation: new SQL time machine! :)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message news:Xns9CD17E55995Yazorman@xxxxxxxxxxxx
Actually, it might be more to it than just the difference in types. Some
time back I ran some performance tests, and my test procedures looked like
this:

DECLARE @start datetime2(3)
SELECT @start = sysdatetime()

INSERT #Str_JOIN (wordno, guid)
SELECT u.wordno, u.guid
FROM usrdictwords u
JOIN @tbl AS t ON u.word = t.str

SELECT @tookms = datediff(ms, @start, sysdatetime());

And in some test batches I registered heaps of executions was registred
as -1 milliseconds. For some tests 85 of 100 executions had this value!
Other test batches registered no -1 at all.

This is something that easily can happen on modern multi-core machines.
These blog posts from Bob Dorr at CSS gives more details:
http://blogs.msdn.com/psssql/archive/2008/12/16/how-it-works-sql-server-no-longer-uses-rdtsc-for-timings-in-sql-2008-and-sql-2005-service-pack-3-sp3.aspx
http://blogs.msdn.com/psssql/archive/2009/05/29/how-it-works-sql-server-timings-and-timer-output-gettickcount-timegettime-queryperformancecounter-rdtsc.aspx


The articles you link to state that GETDATE doesn't rely on the RDTSC, so not sure that's the issue here. Bob doesn't specifically mention SYSDATETIME, but presumably this function relies on the system time as well. Because you're using datetime2 with 3 digits of precision to store SYSDATETIME (which is supposed to return 7 digits of precision) you're implicitly rounding, so we're back to the rounding issue...

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption" (http://www.apress.com/book/view/1430224649)
----------------

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


.

Relevant Pages

  • CmdExec job problem
    ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.programming)
  • Re: Applying tranaction log to old data file
    ... "Erland Sommarskog" wrote in message ... Should I restore old backup, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: SQL 2000 Assertion recbase.cpp:1378
    ... PSS sounds like the right thing to do: ... "Erland Sommarskog" wrote in message ... >> SQL Server Assertion: File: ... > Erland Sommarskog, SQL Server MVP, sommar@algonet.se> ...
    (microsoft.public.sqlserver.clients)
  • Re: Cannot fetch a row from OLE DB provider "BULK" for linked serv
    ... I understand this is more than a mouthful right now, but SQL Server MVP ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.programming)
  • Re: Return Key (identity) on INSERT operation (SQL/ASP)
    ... hopefully he can expedite processing your full subscription. ... SQL Server MVP ... Columnist, SQL Server Professional ... Please reply to the newsgroup. ...
    (microsoft.public.inetserver.asp.general)