Well I tried running sysdatetime on these 2 servers and wondered about the results.
So I modified my query to dump the results into a table so I could see better. The results surprised me.
DECLARE @TimeStart DATETIME2
DECLARE @TimeEnd DATETIME2
DECLARE @Count INT
Declare @Duration int
DECLARE @I INT
Declare @results table(Duration int, Iterations int, StartTime datetime2, EndTime datetime2)
SET @I = 0
Set @Duration = 0
WHILE @I < 10000
BEGIN
SET @TimeStart = sysdatetime()
SET @TimeEnd = @TimeStart
Set @Count = 0
-- Find the amount of time it takes to get a different value
-- To get strictly time, comment out the Set @Count line
WHILE @TimeStart = @TimeEnd
BEGIN
SET @TimeEnd = sysdatetime()
Set @Count = @Count + 1
End
Set @Duration = DATEDIFF(MICROSECOND, @TimeStart, @TimeEnd)
Set @I = @I + 1
Insert into @results(Duration, Iterations, StartTime, EndTime)
values (@Duration, @Count, @TimeStart, @TimeEnd)
End
select Duration
, Iterations
, StartTime
, EndTime
from @results
Select AVG(duration)
From @results
select Duration
, count(Duration)
from @results
group by Duration
The slower machine gave this:
DurationIterationsStartTimeEndTime
977512012-02-03 14:37:30.79261232012-02-03 14:37:30.7935890
9762002012-02-03 14:37:30.79358902012-02-03 14:37:30.7945657
9772672012-02-03 14:37:30.79456572012-02-03 14:37:30.7955424
9772652012-02-03 14:37:30.79554242012-02-03 14:37:30.7965191
. . .
Duration(No column name)
9762960
9776911
195313
195412
293032
29312
39061
390715
... and 48 others with a longer duration up to 620204
The faster machine gave this:
DurationIterationsStartTimeEndTime
1000632012-02-03 14:38:58.99255512012-02-03 14:38:58.9935551
10003472012-02-03 14:38:58.99355512012-02-03 14:38:58.9945551
10004232012-02-03 14:38:58.99455512012-02-03 14:38:58.9955551
10004492012-02-03 14:38:58.99555512012-02-03 14:38:58.9965551
. . .
All of the times ended in 5551.
Duration(No column name)
10009713
3000152
4000131
70001
3000002
3040001