DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to construct a delay loop for simulation?

The following Microsoft SQL Server T-SQL scripts can be used to create delay between statements for simulation purposes or slow down batch execution.

------------

-- T-SQL High CPU Load Simulation - SQL Server 100% CPU Usage

------------

-- Run the following script in number-of-CPUs connections

DECLARE @float1 float, @float2 float

WHILE (1=1)

BEGIN

   SET @float1 = SQRT(SQUARE(datepart(ms,getdate())))

   WAITFOR DELAY '00:00:00'

   SET @float2 = SQRT(SQUARE(datepart(ms,getdate())))

END

------------

-- T-SQL delay funcion averaging 10 seconds - T-SQL waitfor

 

DECLARE  @Sec    INT,

         @delay INT

 

SET @delay = 10

 

PRINT convert(varchar, getdate(), 109)   

 

WHILE (2 > 1)

  BEGIN

    SELECT @Sec = datepart(ss,getdate())

    IF (@Sec%@delay = 0)

      BREAK

  END

 

PRINT convert(varchar, getdate(), 109)   

/* Messages

 

Feb 27 2009 11:49:04:607AM

Feb 27 2009 11:49:10:000AM

*/

------------

-- SQL Server waitfor delay usage to slow down T-SQL program

 

-- 1 minute waitfor delay

BEGIN

    SELECT GETDATE()

    WAITFOR DELAY '00:01';

    SELECT GETDATE()

END;

GO

-- 2019-02-27 15:16:27.217

-- 2019-02-27 15:17:27.217
------------

 

-- 11 sec waitfor delay

BEGIN

    SELECT GETDATE()

    WAITFOR DELAY '00:0:11';

    SELECT GETDATE()

END;

GO

-- 2019-02-27 15:21:33.590

-- 2019-02-27 15:21:44.590

------------

Related articles:

SQL SERVER Delay Function WAITFOR clause Delay Execution of Commands

http://www.sqlusa.com/bestpractices2005/hugeupdate/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE