System: Dell PowerEdge Server with 3 GB RAM, 2.4 GHz Celeron
Software: Microsoft SQL Server 2000 Enterprise running on Windows 2003
Server
Software: Microsoft SQL Server 2000 Enterprise running on Windows 2000
Server
If you run the code below, you'll notice something odd occuring. The
MilliSecond value does not change after a 1Millisecond delay. Is this a bug
or am I doing something wrong?
Any assistance will be greatly appreciated
----------------------------------------------------------------------------
-------
/*
Programmer : Des L. Davis
Date : July 4, 2004
Purpose : Testing Delayed Reaction
*/
CREATE PROCEDURE [sp_TestDelay] AS
DECLARE @DELAYPERIOD NCHAR(12)
SET @DELAYPERIOD = '00:00:00:001'
DECLARE @Retries INT
SET @Retries = 0
DECLARE @MAXTRIES INT
SET @MAXTRIES = 1000 -- Maximum number of tries before timing out
DECLARE @Modified DATETIME
WHILE ( ( @Retries <= @MAXTRIES ) ) BEGIN
SET @Modified = GetDate() -- Set Modification Date
PRINT @Retries
PRINT @Modified
PRINT 'Year = ' + CONVERT( NCHAR(4), DATEPART( YEAR, @Modified ) )
PRINT 'DayOfYear = ' + CONVERT( NCHAR(3), DATEPART( DAYOFYEAR ,
@Modified ) )
PRINT 'Hour = ' + CONVERT( NCHAR(2), DATEPART( HOUR, @Modified ) )
PRINT 'Minute = ' + CONVERT( NCHAR(2), DATEPART( MINUTE, @Modified ) )
PRINT 'Second = ' + CONVERT( NCHAR(2), DATEPART( SECOND, @Modified ) )
PRINT 'MilliSecond = ' + CONVERT( NCHAR(3), DATEPART( MILLISECOND,
@Modified ) )
SET @Retries = @Retries + 1 -- Increment loop counter and retry
WAITFOR DELAY @DELAYPERIOD
END
GO
----------------------------------------------------------------------------
-------
14  18809 
The SQL Server datetime datatype, and therefore WAITFOR DELAY, is only
accurate to 3.33 milliseconds and rounding may occur. You can see what is
happening with the following
DECLARE @DELAYPERIOD datetime
SET @DELAYPERIOD = '00:00:00:001'
SELECT @DELAYPERIOD
This is documented in the SQL Server 2000 Books Online:
<Excerpt href="tsqlref.c hm::/ts_da-db_9xut.htm">
Date and time data from January 1, 1753 through December 31, 9999, to an
accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds
or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007
seconds, as shown in the table.
Example Rounded example
01/01/98 23:59:59.999 1998-01-02 00:00:00.000
01/01/98 23:59:59.995, 1998-01-01 23:59:59.997
01/01/98 23:59:59.996,
01/01/98 23:59:59.997, or
01/01/98 23:59:59.998
01/01/98 23:59:59.992, 1998-01-01 23:59:59.993
01/01/98 23:59:59.993,
01/01/98 23:59:59.994
01/01/98 23:59:59.990 or 1998-01-01 23:59:59.990
01/01/98 23:59:59.991
</Excerpt>
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Des L. Davis" <de*******@coge co.ca> wrote in message
news:yq******** *************@r ead2.cgocable.n et... System: Dell PowerEdge Server with 3 GB RAM, 2.4 GHz Celeron
Software: Microsoft SQL Server 2000 Enterprise running on Windows 2003
Server
Software: Microsoft SQL Server 2000 Enterprise running on Windows 2000
Server
If you run the code below, you'll notice something odd occuring. The
MilliSecond value does not change after a 1Millisecond delay. Is this a
bug or am I doing something wrong?
Any assistance will be greatly appreciated
--------------------------------------------------------------------------
-- -------
/*
Programmer : Des L. Davis
Date : July 4, 2004
Purpose : Testing Delayed Reaction
*/
CREATE PROCEDURE [sp_TestDelay] AS
DECLARE @DELAYPERIOD NCHAR(12)
SET @DELAYPERIOD = '00:00:00:001'
DECLARE @Retries INT
SET @Retries = 0
DECLARE @MAXTRIES INT
SET @MAXTRIES = 1000 -- Maximum number of tries before timing out
DECLARE @Modified DATETIME
WHILE ( ( @Retries <= @MAXTRIES ) ) BEGIN
SET @Modified = GetDate() -- Set Modification Date
PRINT @Retries
PRINT @Modified
PRINT 'Year = ' + CONVERT( NCHAR(4), DATEPART( YEAR, @Modified ) )
PRINT 'DayOfYear = ' + CONVERT( NCHAR(3), DATEPART( DAYOFYEAR ,
@Modified ) )
PRINT 'Hour = ' + CONVERT( NCHAR(2), DATEPART( HOUR, @Modified ) )
PRINT 'Minute = ' + CONVERT( NCHAR(2), DATEPART( MINUTE,
@Modified ) ) PRINT 'Second = ' + CONVERT( NCHAR(2), DATEPART( SECOND, @Modified ) )
PRINT 'MilliSecond = ' + CONVERT( NCHAR(3), DATEPART( MILLISECOND,
@Modified ) )
SET @Retries = @Retries + 1 -- Increment loop counter and retry
WAITFOR DELAY @DELAYPERIOD
END
GO
--------------------------------------------------------------------------
-- -------
I also notice after running a few tests that WAITFOR seems to have a
resolution of 1/100th second.
Specifying a delay of '00:00:00:003' causes the same delay as if I'd
specified 1/100th second.
WAITFOR DELAY '00:00:00:013' causes a 2/100th second delay (:020 or .02), so
it apparently always rounds up.
The behavior seems easier to follow with less output.
alter PROCEDURE [sp_TestDelay] AS
DECLARE @DELAYPERIOD DATETIME
SET @DELAYPERIOD = '00:00:00:013'
DECLARE @Retries INT
SET @Retries = 0
WHILE ( @Retries <= 1000 ) BEGIN
PRINT DATEPART( MILLISECOND, getdate() )
SET @Retries = @Retries + 1 -- Increment loop counter and retry
WAITFOR DELAY @DELAYPERIOD
END
GO
exec sp_testdelay
577
597
617
637
657
.... etc.
"Dan Guzman" <da*******@nosp am-earthlink.net> wrote in message
news:rS******** *********@newsr ead2.news.pas.e arthlink.net...
The SQL Server datetime datatype, and therefore WAITFOR DELAY, is only
accurate to 3.33 milliseconds and rounding may occur. You can see what is
happening with the following
DECLARE @DELAYPERIOD datetime
SET @DELAYPERIOD = '00:00:00:001'
SELECT @DELAYPERIOD
This is documented in the SQL Server 2000 Books Online:
<Excerpt href="tsqlref.c hm::/ts_da-db_9xut.htm">
Date and time data from January 1, 1753 through December 31, 9999, to an
accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds
or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007
seconds, as shown in the table.
Example Rounded example
01/01/98 23:59:59.999 1998-01-02 00:00:00.000
01/01/98 23:59:59.995, 1998-01-01 23:59:59.997
01/01/98 23:59:59.996,
01/01/98 23:59:59.997, or
01/01/98 23:59:59.998
01/01/98 23:59:59.992, 1998-01-01 23:59:59.993
01/01/98 23:59:59.993,
01/01/98 23:59:59.994
01/01/98 23:59:59.990 or 1998-01-01 23:59:59.990
01/01/98 23:59:59.991
</Excerpt>
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Des L. Davis" <de*******@coge co.ca> wrote in message
news:yq******** *************@r ead2.cgocable.n et... System: Dell PowerEdge Server with 3 GB RAM, 2.4 GHz Celeron
Software: Microsoft SQL Server 2000 Enterprise running on Windows 2003
Server
Software: Microsoft SQL Server 2000 Enterprise running on Windows 2000
Server
If you run the code below, you'll notice something odd occuring. The
MilliSecond value does not change after a 1Millisecond delay. Is this a
bug or am I doing something wrong?
Any assistance will be greatly appreciated
--------------------------------------------------------------------------
-- -------
/*
Programmer : Des L. Davis
Date : July 4, 2004
Purpose : Testing Delayed Reaction
*/
CREATE PROCEDURE [sp_TestDelay] AS
DECLARE @DELAYPERIOD NCHAR(12)
SET @DELAYPERIOD = '00:00:00:001'
DECLARE @Retries INT
SET @Retries = 0
DECLARE @MAXTRIES INT
SET @MAXTRIES = 1000 -- Maximum number of tries before timing out
DECLARE @Modified DATETIME
WHILE ( ( @Retries <= @MAXTRIES ) ) BEGIN
SET @Modified = GetDate() -- Set Modification Date
PRINT @Retries
PRINT @Modified
PRINT 'Year = ' + CONVERT( NCHAR(4), DATEPART( YEAR, @Modified ) )
PRINT 'DayOfYear = ' + CONVERT( NCHAR(3), DATEPART( DAYOFYEAR ,
@Modified ) )
PRINT 'Hour = ' + CONVERT( NCHAR(2), DATEPART( HOUR, @Modified ) )
PRINT 'Minute = ' + CONVERT( NCHAR(2), DATEPART( MINUTE,
@Modified ) ) PRINT 'Second = ' + CONVERT( NCHAR(2), DATEPART( SECOND, @Modified ) )
PRINT 'MilliSecond = ' + CONVERT( NCHAR(3), DATEPART( MILLISECOND,
@Modified ) )
SET @Retries = @Retries + 1 -- Increment loop counter and retry
WAITFOR DELAY @DELAYPERIOD
END
GO
--------------------------------------------------------------------------
-- -------
Standard Windows time API calls are only accurate to about 10 ms and this is
apparently how SQL Server implements WAITFOR DELAY. When I run a Profiler
trace, I see statements that run quickly have a reported duration of either
0 , 13 or 16 milliseconds but nothing in between.
Below is my test script that shows the actual behavior of WAITFOR DELAY.
The bottom line is that SQL Server will wait for at least the specified time
but can wait longer due to the combination of datetime rounding and timer
interval resolution.
SET NOCOUNT ON
IF OBJECT_ID('temp db..#Metrics') IS NOT NULL DROP TABLE #Metrics
CREATE TABLE #Metrics
(
MillisecondDela y int NOT NULL,
MillisecondDela yTime datetime NOT NULL,
Retry int NOT NULL,
StartTime datetime NOT NULL,
EndTime datetime NOT NULL
)
DECLARE @MillisecondDel ay int
DECLARE @MillisecondDel ayTime datetime
DECLARE @Retries int
DECLARE @StartTime datetime
DECLARE @EndTime datetime
SET @MillisecondDel ay = 0
WHILE @MillisecondDel ay < 50
BEGIN
SET @MillisecondDel ay = @MillisecondDel ay + 1
SET @MillisecondDel ayTime = DATEADD(ms, @MillisecondDel ay,
'00:00:00:000')
SET @Retries = 0
WHILE @Retries <= 100
BEGIN
SET @Retries = @Retries + 1 -- Increment loop counter and retry
SET @StartTime = getdate()
WAITFOR DELAY @MillisecondDel ayTime
SET @EndTime = getdate()
INSERT INTO #Metrics VALUES(@Millise condDelay,
@MillisecondDel ayTime, @Retries, @StartTime, @EndTime)
END
END
SELECT
MillisecondDela yTime,
AVG(ActualMilli seconds) AS ActualAverageMi lliseconds
FROM (
SELECT
MillisecondDela yTime,
DATEDIFF(ms, StartTime, EndTime) AS ActualMilliseco nds
FROM #Metrics a
WHERE Retry > 1) AS Metrics
GROUP BY
MillisecondDela yTime
ORDER BY
MillisecondDela yTime
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Aaron W. West" <ta******@hotma il.NO.SPAM> wrote in message
news:E7******** ************@sp eakeasy.net... I also notice after running a few tests that WAITFOR seems to have a
resolution of 1/100th second.
Specifying a delay of '00:00:00:003' causes the same delay as if I'd
specified 1/100th second.
WAITFOR DELAY '00:00:00:013' causes a 2/100th second delay (:020 or .02),
so it apparently always rounds up.
The behavior seems easier to follow with less output.
alter PROCEDURE [sp_TestDelay] AS
DECLARE @DELAYPERIOD DATETIME
SET @DELAYPERIOD = '00:00:00:013'
DECLARE @Retries INT
SET @Retries = 0
WHILE ( @Retries <= 1000 ) BEGIN
PRINT DATEPART( MILLISECOND, getdate() )
SET @Retries = @Retries + 1 -- Increment loop counter and retry
WAITFOR DELAY @DELAYPERIOD
END
GO
exec sp_testdelay
577
597
617
637
657
... etc.
"Dan Guzman" <da*******@nosp am-earthlink.net> wrote in message
news:rS******** *********@newsr ead2.news.pas.e arthlink.net...
The SQL Server datetime datatype, and therefore WAITFOR DELAY, is only
accurate to 3.33 milliseconds and rounding may occur. You can see what is
happening with the following
DECLARE @DELAYPERIOD datetime
SET @DELAYPERIOD = '00:00:00:001'
SELECT @DELAYPERIOD
This is documented in the SQL Server 2000 Books Online:
<Excerpt href="tsqlref.c hm::/ts_da-db_9xut.htm">
Date and time data from January 1, 1753 through December 31, 9999, to an
accuracy of one three-hundredth of a second (equivalent to 3.33
milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or
..007 seconds, as shown in the table.
Example Rounded example
01/01/98 23:59:59.999 1998-01-02 00:00:00.000
01/01/98 23:59:59.995, 1998-01-01 23:59:59.997
01/01/98 23:59:59.996,
01/01/98 23:59:59.997, or
01/01/98 23:59:59.998
01/01/98 23:59:59.992, 1998-01-01 23:59:59.993
01/01/98 23:59:59.993,
01/01/98 23:59:59.994
01/01/98 23:59:59.990 or 1998-01-01 23:59:59.990
01/01/98 23:59:59.991
</Excerpt>
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Des L. Davis" <de*******@coge co.ca> wrote in message
news:yq******** *************@r ead2.cgocable.n et... System: Dell PowerEdge Server with 3 GB RAM, 2.4 GHz Celeron
Software: Microsoft SQL Server 2000 Enterprise running on Windows 2003
Server
Software: Microsoft SQL Server 2000 Enterprise running on Windows 2000
Server
If you run the code below, you'll notice something odd occuring. The
MilliSecond value does not change after a 1Millisecond delay. Is this a
bug or am I doing something wrong?
Any assistance will be greatly appreciated
--------------------------------------------------------------------------
-- -------
/*
Programmer : Des L. Davis
Date : July 4, 2004
Purpose : Testing Delayed Reaction
*/
CREATE PROCEDURE [sp_TestDelay] AS
DECLARE @DELAYPERIOD NCHAR(12)
SET @DELAYPERIOD = '00:00:00:001'
DECLARE @Retries INT
SET @Retries = 0
DECLARE @MAXTRIES INT
SET @MAXTRIES = 1000 -- Maximum number of tries before timing out
DECLARE @Modified DATETIME
WHILE ( ( @Retries <= @MAXTRIES ) ) BEGIN
SET @Modified = GetDate() -- Set Modification Date
PRINT @Retries
PRINT @Modified
PRINT 'Year = ' + CONVERT( NCHAR(4), DATEPART( YEAR, @Modified ) )
PRINT 'DayOfYear = ' + CONVERT( NCHAR(3), DATEPART( DAYOFYEAR ,
@Modified ) )
PRINT 'Hour = ' + CONVERT( NCHAR(2), DATEPART( HOUR, @Modified ) )
PRINT 'Minute = ' + CONVERT( NCHAR(2), DATEPART( MINUTE,
@Modified ) ) PRINT 'Second = ' + CONVERT( NCHAR(2), DATEPART( SECOND,
@Modified ) ) PRINT 'MilliSecond = ' + CONVERT( NCHAR(3), DATEPART( MILLISECOND,
@Modified ) )
SET @Retries = @Retries + 1 -- Increment loop counter and retry
WAITFOR DELAY @DELAYPERIOD
END
GO
--------------------------------------------------------------------------
-- -------
Guys,
I appreciate the assistance you've all provided. Any suggested work around?
"Dan Guzman" <da*******@nosp am-earthlink.net> wrote in message
news:Mh******** *********@newsr ead2.news.pas.e arthlink.net... Standard Windows time API calls are only accurate to about 10 ms and this
is apparently how SQL Server implements WAITFOR DELAY. When I run a Profiler
trace, I see statements that run quickly have a reported duration of
either 0 , 13 or 16 milliseconds but nothing in between.
Below is my test script that shows the actual behavior of WAITFOR DELAY.
The bottom line is that SQL Server will wait for at least the specified
time but can wait longer due to the combination of datetime rounding and timer
interval resolution.
SET NOCOUNT ON
IF OBJECT_ID('temp db..#Metrics') IS NOT NULL DROP TABLE #Metrics
CREATE TABLE #Metrics
(
MillisecondDela y int NOT NULL,
MillisecondDela yTime datetime NOT NULL,
Retry int NOT NULL,
StartTime datetime NOT NULL,
EndTime datetime NOT NULL
)
DECLARE @MillisecondDel ay int
DECLARE @MillisecondDel ayTime datetime
DECLARE @Retries int
DECLARE @StartTime datetime
DECLARE @EndTime datetime
SET @MillisecondDel ay = 0
WHILE @MillisecondDel ay < 50
BEGIN
SET @MillisecondDel ay = @MillisecondDel ay + 1
SET @MillisecondDel ayTime = DATEADD(ms, @MillisecondDel ay,
'00:00:00:000')
SET @Retries = 0
WHILE @Retries <= 100
BEGIN
SET @Retries = @Retries + 1 -- Increment loop counter and retry
SET @StartTime = getdate()
WAITFOR DELAY @MillisecondDel ayTime
SET @EndTime = getdate()
INSERT INTO #Metrics VALUES(@Millise condDelay,
@MillisecondDel ayTime, @Retries, @StartTime, @EndTime)
END
END
SELECT
MillisecondDela yTime,
AVG(ActualMilli seconds) AS ActualAverageMi lliseconds
FROM (
SELECT
MillisecondDela yTime,
DATEDIFF(ms, StartTime, EndTime) AS ActualMilliseco nds
FROM #Metrics a
WHERE Retry > 1) AS Metrics
GROUP BY
MillisecondDela yTime
ORDER BY
MillisecondDela yTime
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Aaron W. West" <ta******@hotma il.NO.SPAM> wrote in message
news:E7******** ************@sp eakeasy.net... I also notice after running a few tests that WAITFOR seems to have a
resolution of 1/100th second.
Specifying a delay of '00:00:00:003' causes the same delay as if I'd
specified 1/100th second.
WAITFOR DELAY '00:00:00:013' causes a 2/100th second delay (:020 or
..02), so it apparently always rounds up.
The behavior seems easier to follow with less output.
alter PROCEDURE [sp_TestDelay] AS
DECLARE @DELAYPERIOD DATETIME
SET @DELAYPERIOD = '00:00:00:013'
DECLARE @Retries INT
SET @Retries = 0
WHILE ( @Retries <= 1000 ) BEGIN
PRINT DATEPART( MILLISECOND, getdate() )
SET @Retries = @Retries + 1 -- Increment loop counter and retry
WAITFOR DELAY @DELAYPERIOD
END
GO
exec sp_testdelay
577
597
617
637
657
... etc.
"Dan Guzman" <da*******@nosp am-earthlink.net> wrote in message
news:rS******** *********@newsr ead2.news.pas.e arthlink.net...
The SQL Server datetime datatype, and therefore WAITFOR DELAY, is only
accurate to 3.33 milliseconds and rounding may occur. You can see what
is happening with the following
DECLARE @DELAYPERIOD datetime
SET @DELAYPERIOD = '00:00:00:001'
SELECT @DELAYPERIOD
This is documented in the SQL Server 2000 Books Online:
<Excerpt href="tsqlref.c hm::/ts_da-db_9xut.htm">
Date and time data from January 1, 1753 through December 31, 9999, to an
accuracy of one three-hundredth of a second (equivalent to 3.33
milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or
.007 seconds, as shown in the table.
Example Rounded example
01/01/98 23:59:59.999 1998-01-02 00:00:00.000
01/01/98 23:59:59.995, 1998-01-01 23:59:59.997
01/01/98 23:59:59.996,
01/01/98 23:59:59.997, or
01/01/98 23:59:59.998
01/01/98 23:59:59.992, 1998-01-01 23:59:59.993
01/01/98 23:59:59.993,
01/01/98 23:59:59.994
01/01/98 23:59:59.990 or 1998-01-01 23:59:59.990
01/01/98 23:59:59.991
</Excerpt>
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Des L. Davis" <de*******@coge co.ca> wrote in message
news:yq******** *************@r ead2.cgocable.n et... System: Dell PowerEdge Server with 3 GB RAM, 2.4 GHz Celeron
Software: Microsoft SQL Server 2000 Enterprise running on Windows 2003
Server
Software: Microsoft SQL Server 2000 Enterprise running on Windows 2000
Server
If you run the code below, you'll notice something odd occuring. The
MilliSecond value does not change after a 1Millisecond delay. Is this
a bug or am I doing something wrong?
Any assistance will be greatly appreciated
-------------------------------------------------------------------------- -- -------
/*
Programmer : Des L. Davis
Date : July 4, 2004
Purpose : Testing Delayed Reaction
*/
CREATE PROCEDURE [sp_TestDelay] AS
DECLARE @DELAYPERIOD NCHAR(12)
SET @DELAYPERIOD = '00:00:00:001'
DECLARE @Retries INT
SET @Retries = 0
DECLARE @MAXTRIES INT
SET @MAXTRIES = 1000 -- Maximum number of tries before timing out
DECLARE @Modified DATETIME
WHILE ( ( @Retries <= @MAXTRIES ) ) BEGIN
SET @Modified = GetDate() -- Set Modification Date
PRINT @Retries
PRINT @Modified
PRINT 'Year = ' + CONVERT( NCHAR(4), DATEPART( YEAR,
@Modified ) ) PRINT 'DayOfYear = ' + CONVERT( NCHAR(3), DATEPART( DAYOFYEAR ,
@Modified ) )
PRINT 'Hour = ' + CONVERT( NCHAR(2), DATEPART( HOUR,
@Modified ) ) PRINT 'Minute = ' + CONVERT( NCHAR(2), DATEPART( MINUTE,
@Modified ) ) PRINT 'Second = ' + CONVERT( NCHAR(2), DATEPART( SECOND,
@Modified ) ) PRINT 'MilliSecond = ' + CONVERT( NCHAR(3), DATEPART(
MILLISECOND, @Modified ) )
SET @Retries = @Retries + 1 -- Increment loop counter and retry
WAITFOR DELAY @DELAYPERIOD
END
GO
-------------------------------------------------------------------------- -- -------
Des L. Davis (de*******@coge co.ca) writes: I appreciate the assistance you've all provided. Any suggested work
around?
Maybe. If you tell us what the actual business problem you are trying
to solve, we might come up with something. Trying to run a waitfor in
only 1 ms, when there is a 10 ms accuracy sounds like a dead end to me.
OK, so you could write an extended stored procedure to do the waiting,
but if the Windows API only gives you 10 ms, you would have to roll your
own, which does not sound trivial to me.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Erland's extended proc idea is interesting but, in addition the complexity,
there may be some overhead involved. Perhaps you can describe the business
problem. Perhaps there are better methods besides SQL.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Des L. Davis" <de*******@coge co.ca> wrote in message
news:bH******** ***********@rea d1.cgocable.net ... Guys,
I appreciate the assistance you've all provided. Any suggested work
around?
"Dan Guzman" <da*******@nosp am-earthlink.net> wrote in message
news:Mh******** *********@newsr ead2.news.pas.e arthlink.net... Standard Windows time API calls are only accurate to about 10 ms and
this is apparently how SQL Server implements WAITFOR DELAY. When I run a
Profiler trace, I see statements that run quickly have a reported duration of either 0 , 13 or 16 milliseconds but nothing in between.
Below is my test script that shows the actual behavior of WAITFOR DELAY.
The bottom line is that SQL Server will wait for at least the specified
time but can wait longer due to the combination of datetime rounding and
timer interval resolution.
SET NOCOUNT ON
IF OBJECT_ID('temp db..#Metrics') IS NOT NULL DROP TABLE #Metrics
CREATE TABLE #Metrics
(
MillisecondDela y int NOT NULL,
MillisecondDela yTime datetime NOT NULL,
Retry int NOT NULL,
StartTime datetime NOT NULL,
EndTime datetime NOT NULL
)
DECLARE @MillisecondDel ay int
DECLARE @MillisecondDel ayTime datetime
DECLARE @Retries int
DECLARE @StartTime datetime
DECLARE @EndTime datetime
SET @MillisecondDel ay = 0
WHILE @MillisecondDel ay < 50
BEGIN
SET @MillisecondDel ay = @MillisecondDel ay + 1
SET @MillisecondDel ayTime = DATEADD(ms, @MillisecondDel ay,
'00:00:00:000')
SET @Retries = 0
WHILE @Retries <= 100
BEGIN
SET @Retries = @Retries + 1 -- Increment loop counter and
retry SET @StartTime = getdate()
WAITFOR DELAY @MillisecondDel ayTime
SET @EndTime = getdate()
INSERT INTO #Metrics VALUES(@Millise condDelay,
@MillisecondDel ayTime, @Retries, @StartTime, @EndTime)
END
END
SELECT
MillisecondDela yTime,
AVG(ActualMilli seconds) AS ActualAverageMi lliseconds
FROM (
SELECT
MillisecondDela yTime,
DATEDIFF(ms, StartTime, EndTime) AS ActualMilliseco nds
FROM #Metrics a
WHERE Retry > 1) AS Metrics
GROUP BY
MillisecondDela yTime
ORDER BY
MillisecondDela yTime
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Aaron W. West" <ta******@hotma il.NO.SPAM> wrote in message
news:E7******** ************@sp eakeasy.net... I also notice after running a few tests that WAITFOR seems to have a
resolution of 1/100th second.
Specifying a delay of '00:00:00:003' causes the same delay as if I'd
specified 1/100th second.
WAITFOR DELAY '00:00:00:013' causes a 2/100th second delay (:020 or .02), so it apparently always rounds up.
The behavior seems easier to follow with less output.
alter PROCEDURE [sp_TestDelay] AS
DECLARE @DELAYPERIOD DATETIME
SET @DELAYPERIOD = '00:00:00:013'
DECLARE @Retries INT
SET @Retries = 0
WHILE ( @Retries <= 1000 ) BEGIN
PRINT DATEPART( MILLISECOND, getdate() )
SET @Retries = @Retries + 1 -- Increment loop counter and retry
WAITFOR DELAY @DELAYPERIOD
END
GO
exec sp_testdelay
577
597
617
637
657
... etc.
"Dan Guzman" <da*******@nosp am-earthlink.net> wrote in message
news:rS******** *********@newsr ead2.news.pas.e arthlink.net...
The SQL Server datetime datatype, and therefore WAITFOR DELAY, is only
accurate to 3.33 milliseconds and rounding may occur. You can see
what is happening with the following
DECLARE @DELAYPERIOD datetime
SET @DELAYPERIOD = '00:00:00:001'
SELECT @DELAYPERIOD
This is documented in the SQL Server 2000 Books Online:
<Excerpt href="tsqlref.c hm::/ts_da-db_9xut.htm">
Date and time data from January 1, 1753 through December 31, 9999, to
an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003,
or .007 seconds, as shown in the table.
Example Rounded example
01/01/98 23:59:59.999 1998-01-02 00:00:00.000
01/01/98 23:59:59.995, 1998-01-01 23:59:59.997
01/01/98 23:59:59.996,
01/01/98 23:59:59.997, or
01/01/98 23:59:59.998
01/01/98 23:59:59.992, 1998-01-01 23:59:59.993
01/01/98 23:59:59.993,
01/01/98 23:59:59.994
01/01/98 23:59:59.990 or 1998-01-01 23:59:59.990
01/01/98 23:59:59.991
</Excerpt>
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Des L. Davis" <de*******@coge co.ca> wrote in message
news:yq******** *************@r ead2.cgocable.n et...
> System: Dell PowerEdge Server with 3 GB RAM, 2.4 GHz Celeron
> Software: Microsoft SQL Server 2000 Enterprise running on Windows
2003 > Server
> Software: Microsoft SQL Server 2000 Enterprise running on Windows
2000 > Server
>
> If you run the code below, you'll notice something odd occuring.
The > MilliSecond value does not change after a 1Millisecond delay. Is
this a bug
> or am I doing something wrong?
> Any assistance will be greatly appreciated
-------------------------------------------------------------------------- --
> -------
> /*
> Programmer : Des L. Davis
> Date : July 4, 2004
> Purpose : Testing Delayed Reaction
> */
>
> CREATE PROCEDURE [sp_TestDelay] AS
>
> DECLARE @DELAYPERIOD NCHAR(12)
> SET @DELAYPERIOD = '00:00:00:001'
> DECLARE @Retries INT
> SET @Retries = 0
> DECLARE @MAXTRIES INT
> SET @MAXTRIES = 1000 -- Maximum number of tries before timing out
> DECLARE @Modified DATETIME
>
> WHILE ( ( @Retries <= @MAXTRIES ) ) BEGIN
>
> SET @Modified = GetDate() -- Set Modification Date
>
> PRINT @Retries
> PRINT @Modified
> PRINT 'Year = ' + CONVERT( NCHAR(4), DATEPART( YEAR, @Modified ) ) > PRINT 'DayOfYear = ' + CONVERT( NCHAR(3), DATEPART( DAYOFYEAR ,
> @Modified ) )
> PRINT 'Hour = ' + CONVERT( NCHAR(2), DATEPART( HOUR, @Modified ) ) > PRINT 'Minute = ' + CONVERT( NCHAR(2), DATEPART( MINUTE,
@Modified ) )
> PRINT 'Second = ' + CONVERT( NCHAR(2), DATEPART( SECOND,
@Modified ) ) > PRINT 'MilliSecond = ' + CONVERT( NCHAR(3), DATEPART( MILLISECOND, > @Modified ) )
>
> SET @Retries = @Retries + 1 -- Increment loop counter and
retry >
> WAITFOR DELAY @DELAYPERIOD
>
> END
> GO
>
-------------------------------------------------------------------------- --
> -------
>
>
>
"Dan Guzman" <da*******@nosp am-earthlink.net> wrote in message
news:Mj******** *********@newsr ead2.news.pas.e arthlink.net... Erland's extended proc idea is interesting but, in addition the
complexity, there may be some overhead involved. Perhaps you can describe the
business problem. Perhaps there are better methods besides SQL.
Btw, I want to mention the only production use I've seen of this (WAITFOR)
on our servers is I think bad programming. (i.e. it solved the symptom, but
I'm not sure it solved the underlying problem.)
Here is the scenario:
I have several hundred thousand rows of data in a MS Access database that
must be imported into SQL Server. I cannot employ a simple import since the
underlying table schema in both databases are different. So I wrote a C#
import routine to do the dirty work for me. The C# application - which
we'll call ImportData.EXE - interacts with a SQL Server 2000 Enterprise
server database through stored procs only. One of the procs -
sp_SaveImported Data - is responsible for saving the imported data. As part
of it's insert routine sp_SaveImported Data generates a timebased PrimaryKey
(see the formula below) and tries to insert the data using that PrimaryKey.
If the insertion fails because of a duplicate PK (i.e. @@ERROR = 2627)
sp_SaveImported Data is instructed to wait 1 millisecond (using WAITFOR
DELAY '00:00:00:001') , generate a new PK, and retry the insert operation.
This happens in a loop which is aborted after 1000 attempts. Any errors are
written to a text file for later reconcilliation .
If it all works, the PK should look something like 200418677313430 5 which is
the result of the following concatenation
PK = dbo. PadLeft ( @Year, '0', 4 ) + dbo.PadLeft ( @DayOfYear, '0', 3 )
+ dbo. PadLeft ( @MilliSecond, '0', 3 ) + dbo.PadLeft ( @Hour, '0', 2 )
+ dbo.PadLeft ( @Minute, '0', 2 ) + dbo.PadLeft ( @Second, '0', 2 )
where PadLeft is a UDF
This allows me to insert a theoritical maximum of 1000 rows of data into the
SQL Server every second. Obviously this isn't occuring in practice. BTW
this is a non issue on an older system (PIII 667, 1GB) - everything works
flawlessly which is why the issue went un-noticed for several weeks.
The obvious solution here is to re-work how the keys are generated but I'm
attempting to better understand what are my real options.
"Dan Guzman" <da*******@nosp am-earthlink.net> wrote in message
news:Mj******** *********@newsr ead2.news.pas.e arthlink.net... Erland's extended proc idea is interesting but, in addition the complexity
, there may be some overhead involved. Perhaps you can describe the
business problem. Perhaps there are better methods besides SQL.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Des L. Davis" <de*******@coge co.ca> wrote in message
news:bH******** ***********@rea d1.cgocable.net ... Guys,
I appreciate the assistance you've all provided. Any suggested work around?
"Dan Guzman" <da*******@nosp am-earthlink.net> wrote in message
news:Mh******** *********@newsr ead2.news.pas.e arthlink.net... Standard Windows time API calls are only accurate to about 10 ms and this is apparently how SQL Server implements WAITFOR DELAY. When I run a Profiler trace, I see statements that run quickly have a reported duration of
either 0 , 13 or 16 milliseconds but nothing in between.
Below is my test script that shows the actual behavior of WAITFOR
DELAY. The bottom line is that SQL Server will wait for at least the
specified time but can wait longer due to the combination of datetime rounding and timer interval resolution.
SET NOCOUNT ON
IF OBJECT_ID('temp db..#Metrics') IS NOT NULL DROP TABLE #Metrics
CREATE TABLE #Metrics
(
MillisecondDela y int NOT NULL,
MillisecondDela yTime datetime NOT NULL,
Retry int NOT NULL,
StartTime datetime NOT NULL,
EndTime datetime NOT NULL
)
DECLARE @MillisecondDel ay int
DECLARE @MillisecondDel ayTime datetime
DECLARE @Retries int
DECLARE @StartTime datetime
DECLARE @EndTime datetime
SET @MillisecondDel ay = 0
WHILE @MillisecondDel ay < 50
BEGIN
SET @MillisecondDel ay = @MillisecondDel ay + 1
SET @MillisecondDel ayTime = DATEADD(ms, @MillisecondDel ay,
'00:00:00:000')
SET @Retries = 0
WHILE @Retries <= 100
BEGIN
SET @Retries = @Retries + 1 -- Increment loop counter and retry SET @StartTime = getdate()
WAITFOR DELAY @MillisecondDel ayTime
SET @EndTime = getdate()
INSERT INTO #Metrics VALUES(@Millise condDelay,
@MillisecondDel ayTime, @Retries, @StartTime, @EndTime)
END
END
SELECT
MillisecondDela yTime,
AVG(ActualMilli seconds) AS ActualAverageMi lliseconds
FROM (
SELECT
MillisecondDela yTime,
DATEDIFF(ms, StartTime, EndTime) AS ActualMilliseco nds
FROM #Metrics a
WHERE Retry > 1) AS Metrics
GROUP BY
MillisecondDela yTime
ORDER BY
MillisecondDela yTime
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Aaron W. West" <ta******@hotma il.NO.SPAM> wrote in message
news:E7******** ************@sp eakeasy.net...
> I also notice after running a few tests that WAITFOR seems to have a
> resolution of 1/100th second.
>
> Specifying a delay of '00:00:00:003' causes the same delay as if I'd
> specified 1/100th second.
>
> WAITFOR DELAY '00:00:00:013' causes a 2/100th second delay (:020 or
.02), so
> it apparently always rounds up.
>
> The behavior seems easier to follow with less output.
>
>
> alter PROCEDURE [sp_TestDelay] AS
> DECLARE @DELAYPERIOD DATETIME
> SET @DELAYPERIOD = '00:00:00:013'
> DECLARE @Retries INT
> SET @Retries = 0
> WHILE ( @Retries <= 1000 ) BEGIN
> PRINT DATEPART( MILLISECOND, getdate() )
> SET @Retries = @Retries + 1 -- Increment loop counter and
retry > WAITFOR DELAY @DELAYPERIOD
> END
> GO
>
> exec sp_testdelay
>
> 577
> 597
> 617
> 637
> 657
> ... etc.
>
>
> "Dan Guzman" <da*******@nosp am-earthlink.net> wrote in message
> news:rS******** *********@newsr ead2.news.pas.e arthlink.net...
> The SQL Server datetime datatype, and therefore WAITFOR DELAY, is
only > accurate to 3.33 milliseconds and rounding may occur. You can see
what is > happening with the following
>
> DECLARE @DELAYPERIOD datetime
> SET @DELAYPERIOD = '00:00:00:001'
> SELECT @DELAYPERIOD
>
> This is documented in the SQL Server 2000 Books Online:
>
> <Excerpt href="tsqlref.c hm::/ts_da-db_9xut.htm">
>
> Date and time data from January 1, 1753 through December 31, 9999,
to an > accuracy of one three-hundredth of a second (equivalent to 3.33
milliseconds
> or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007
> seconds, as shown in the table.
>
> Example Rounded example
> 01/01/98 23:59:59.999 1998-01-02 00:00:00.000
>
> 01/01/98 23:59:59.995, 1998-01-01 23:59:59.997
> 01/01/98 23:59:59.996,
> 01/01/98 23:59:59.997, or
> 01/01/98 23:59:59.998
>
> 01/01/98 23:59:59.992, 1998-01-01 23:59:59.993
> 01/01/98 23:59:59.993,
> 01/01/98 23:59:59.994
>
> 01/01/98 23:59:59.990 or 1998-01-01 23:59:59.990
> 01/01/98 23:59:59.991
>
> </Excerpt>
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Des L. Davis" <de*******@coge co.ca> wrote in message
> news:yq******** *************@r ead2.cgocable.n et...
> > System: Dell PowerEdge Server with 3 GB RAM, 2.4 GHz Celeron
> > Software: Microsoft SQL Server 2000 Enterprise running on Windows 2003 > > Server
> > Software: Microsoft SQL Server 2000 Enterprise running on Windows 2000 > > Server
> >
> > If you run the code below, you'll notice something odd occuring. The > > MilliSecond value does not change after a 1Millisecond delay. Is this a > bug
> > or am I doing something wrong?
> > Any assistance will be greatly appreciated
>
-------------------------------------------------------------------------- > --
> > -------
> > /*
> > Programmer : Des L. Davis
> > Date : July 4, 2004
> > Purpose : Testing Delayed Reaction
> > */
> >
> > CREATE PROCEDURE [sp_TestDelay] AS
> >
> > DECLARE @DELAYPERIOD NCHAR(12)
> > SET @DELAYPERIOD = '00:00:00:001'
> > DECLARE @Retries INT
> > SET @Retries = 0
> > DECLARE @MAXTRIES INT
> > SET @MAXTRIES = 1000 -- Maximum number of tries before timing
out > > DECLARE @Modified DATETIME
> >
> > WHILE ( ( @Retries <= @MAXTRIES ) ) BEGIN
> >
> > SET @Modified = GetDate() -- Set Modification Date
> >
> > PRINT @Retries
> > PRINT @Modified
> > PRINT 'Year = ' + CONVERT( NCHAR(4), DATEPART( YEAR,
@Modified ) ) > > PRINT 'DayOfYear = ' + CONVERT( NCHAR(3), DATEPART( DAYOFYEAR
, > > @Modified ) )
> > PRINT 'Hour = ' + CONVERT( NCHAR(2), DATEPART( HOUR,
@Modified ) ) > > PRINT 'Minute = ' + CONVERT( NCHAR(2), DATEPART( MINUTE,
> @Modified ) )
> > PRINT 'Second = ' + CONVERT( NCHAR(2), DATEPART( SECOND,
@Modified ) )
> > PRINT 'MilliSecond = ' + CONVERT( NCHAR(3), DATEPART(
MILLISECOND, > > @Modified ) )
> >
> > SET @Retries = @Retries + 1 -- Increment loop counter and
retry > >
> > WAITFOR DELAY @DELAYPERIOD
> >
> > END
> > GO
> >
>
-------------------------------------------------------------------------- > --
> > -------
> >
> >
> >
>
>
>
Des L. Davis (de*******@coge co.ca) writes: I have several hundred thousand rows of data in a MS Access database
that must be imported into SQL Server. I cannot employ a simple import
since the underlying table schema in both databases are different. So I
wrote a C# import routine to do the dirty work for me. The C#
application - which we'll call ImportData.EXE - interacts with a SQL
Server 2000 Enterprise server database through stored procs only. One
of the procs - sp_SaveImported Data - is responsible for saving the
imported data.
Standard comment: the sp_ prefix is reserved for system procedures and SQL
Server will first look for these in master.
If it all works, the PK should look something like 200418677313430 5
which is the result of the following concatenation
PK = dbo. PadLeft ( @Year, '0', 4 ) + dbo.PadLeft ( @DayOfYear, '0', 3 )
+ dbo. PadLeft ( @MilliSecond, '0', 3 ) + dbo.PadLeft ( @Hour, '0', 2 )
+ dbo.PadLeft ( @Minute, '0', 2 ) + dbo.PadLeft ( @Second, '0', 2 )
where PadLeft is a UDF
This allows me to insert a theoritical maximum of 1000 rows of data into
the SQL Server every second.
As you have learnt now, the theoretical maximum is 333 rows, and the actual
number probably even lower.
The obvious solution here is to re-work how the keys are generated but I'm
attempting to better understand what are my real options.
It wasn't clear to me why have chosen this scheme and not a plain IDENTITY
column.
If you for some reason want the keys to be scattered, you could use a
uniqueidentifie r column and assigned a value with newid().
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: NickName |
last post by:
Env: SQL Server 2000
It actually waited for 50 seconds instead of 5, tried
WAITFOR DELAY '000:00:005' and WAITFOR DELAY '000:00:5' respectively,
and got same behavior. Bug or ?
TIA
|
by: lucifer |
last post by:
hi
i need to insert delay in my program what function should i use
the old delay is not supported by the VC6
|
by: Doug Thews |
last post by:
I ran into an interesting re-pain delay after calling the Abort() method on
a thread, but it only happens the very first time I call it. Every time
afterward, there is no delay.
I've got a delegate inside the UI that I call to update the progress meter.
I use the Suspend() and Abort() methods based on button events.
I can watch the...
|
by: Maheshkumar.R |
last post by:
Hi groups,
How i can introduce some milliseconds delay in application. How i can do
achieve this...
let me clearly say...
(1) I'm displaying slices of medical images.
For framerate - for this i'm loading 2+ slices, 4+ slices in loop
to have cine impact..-> Ok working
(2) But if i want to reduce the speed...how i can introduce delay...
|
by: mfeingold |
last post by:
I am working on a system, which among other things includes a server and a
..net control sitting in an html page and connected to the server. I ran into
a couple of problems, you guys might have some insight about.
1) It takes 20 sec or so to open a tcp socket from the client to the server.
It just sits in the TcpClient.conect waiting for...
| | |
by: MLH |
last post by:
This was contributed by someone in this NG
last year...
Sub WaitFor(psngSeconds As Single)
' wait for specified number of seconds
' Copyright Trevor Best (tre...@besty.org.uk) <-OK, so I added
thisline.
Dim sngStart As Single
Dim sngET As Single
|
by: dmjpro |
last post by:
Have a look at my code snippet.
Process l_p = Runtime.getRuntime().exec("my command");
l_p.waitFor(); //here the program hangs out
Then i go for a site and found it's solution ..
Simply i access the input as well error stream before calling wait for ...
here my updated one goes ...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
| | |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |
