Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567

Do you know your getdate()? Expand / Collapse
Author
Message
Posted Friday, February 3, 2012 8:22 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:01 AM
Points: 740, Visits: 1,892
Interesting exercise, but keep in mind that for most of us this is academic since most server clocks are not thermally compensated and easily drift by a mS every couple of minutes (or when a fan kicks on).

So unless you have a real network clock (symmetricon etc), all those decimal digits are meaningless.


...

-- FORTRAN manual for Xerox Computers --
Post #1246547
Posted Friday, February 3, 2012 10:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:59 PM
Points: 24, Visits: 169
I, too, ran this code on one server and was getting 3 milliseconds. I was curious as to the duration of the test and the inner workings of the code so I wrote this to remove the code from the loop and check on a few things.

I found that the results are highly dependent on the hardware configurations.


DECLARE @TimeStart DATETIME
DECLARE @Time DATETIME
DECLARE @TimeEnd DATETIME
Declare @TotalTime int
DECLARE @Count INT
Declare @TotalCount int
Declare @HighCount int
Declare @LowCount int
Declare @HighTime int
Declare @LowTime int
Declare @IterTime int
Declare @OddTime int
DECLARE @I INT
Declare @TestStart datetime
Declare @TestEnd dateTime
SET @I = 0
Set @TotalTime = 0
Set @TotalCount = 0
Set @HighCount = 0
Set @LowCount = 99999999
Set @HighTime = 0
Set @LowTime = 99999999
Set @IterTime = 0
Set @OddTime = 0
Set @TestStart = GETDATE()

WHILE @I < 10000
BEGIN

SET @TimeStart = GETDATE()
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 = GETDATE()
Set @Count = @Count + 1
End

Set @IterTime = DATEDIFF(MILLISECOND, @TimeStart, @TimeEnd)
Set @TotalTime = @TotalTime + @IterTime
Set @TotalCount = @TotalCount + @Count
Set @I = @I + 1
If @Count < @LowCount
Set @LowCount = @Count
If @Count > @HighCount
Set @HighCount = @Count
If @IterTime < @LowTime
Set @LowTime = @IterTime
If @IterTime > @HighTime
Set @HighTime = @IterTime
-- Why 3? Because I ran the inner loop 100 times while building this and it was nearly always returning 3
--
If @IterTime <> 3
Set @OddTime = @OddTime + 1
End

Set @TestEnd = GETDATE()

Print 'Test Duration'

print DATEDIFF(MILLISECOND, @teststart, @testend) / 1000
print '---------------------------'
print 'Average Iterations'
print @TotalCount / @I
print 'High Iterations'
print @HighCount
print 'Low Iterations'
print @LowCount
print '-----------------------'
print 'Average Time'
print @TotalTime / @I
print 'High Time'
print @HighTime
print 'Low Time'
print @LowTime
print 'Odd Time Count'
print @OddTime



When I ran this on a machine with NT6.1 x64, 40 gigs of memory, and 16 processors I got these results:

Test Duration
37
---------------------------
Average Iteratations
1017
High Iterations
2301
Low Iterations
2
-----------------------
Average Time
3
High Time
306
Low Time
3
Odd Time Count
115

So 115 times out of 10,000 the result wasn't 3 milliseconds.

On a machine (a sandbox) NT 6.0 x86 with 4 gigs of memory and 1 processor I got these results:

Test Duration
182
---------------------------
Average Iteratations
4339
High Iterations
6284
Low Iterations
25
-----------------------
Average Time
17
High Time
483
Low Time
3
Odd Time Count
9999
Post #1246626
Posted Friday, February 3, 2012 1:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:59 PM
Points: 24, Visits: 169
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:

Duration Iterations StartTime EndTime
977 51 2012-02-03 14:37:30.7926123 2012-02-03 14:37:30.7935890
976 200 2012-02-03 14:37:30.7935890 2012-02-03 14:37:30.7945657
977 267 2012-02-03 14:37:30.7945657 2012-02-03 14:37:30.7955424
977 265 2012-02-03 14:37:30.7955424 2012-02-03 14:37:30.7965191
. . .

Duration (No column name)
976 2960
977 6911
1953 13
1954 12
2930 32
2931 2
3906 1
3907 15

... and 48 others with a longer duration up to 620204

The faster machine gave this:

Duration Iterations StartTime EndTime
1000 63 2012-02-03 14:38:58.9925551 2012-02-03 14:38:58.9935551
1000 347 2012-02-03 14:38:58.9935551 2012-02-03 14:38:58.9945551
1000 423 2012-02-03 14:38:58.9945551 2012-02-03 14:38:58.9955551
1000 449 2012-02-03 14:38:58.9955551 2012-02-03 14:38:58.9965551
. . .

All of the times ended in 5551.

Duration (No column name)
1000 9713
3000 152
4000 131
7000 1
300000 2
304000 1

Post #1246743
Posted Friday, February 3, 2012 3:03 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 14, 2014 1:19 PM
Points: 323, Visits: 1,456
Don't think I've seen this configuration yet:

SQL 2008 SP2 on an Itanium 64
Windows 2003 SP2

1st test-
132273 (wow)
8.02182

A test machine with nothing going on but a completely full data drive (shouldn't matter)

No 2nd test 'casue the 1st took almost 18 minutes to run
Post #1246789
Posted Saturday, February 4, 2012 7:14 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, July 19, 2014 8:54 PM
Points: 322, Visits: 1,300
My contribution to the body of data:

Dell Inspiron 9400 laptop
Intel Core2 T5600 CPU
4GB RAM
Windows 7 Enterprise SP1 64-bit

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
Jun 17 2011 00:54:03
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Query results:
5138
3.33982

CURRENT_TIMESTAMP seems to work in 2008 R2:

SELECT CURRENT_TIMESTAMP
-----------------------
2012-02-04 09:06:08.757

SELECT CURRENT_DATE
Incorrect syntax near the keyword 'CURRENT_DATE'.




Peter Maloof
Serving Data
Post #1246897
« Prev Topic | Next Topic »

Add to briefcase «««34567

Permissions Expand / Collapse