How To Slow Down A SQL Query?

Question

As strange as it sounds I need to slow down a SQL query. Currently I'm using Microsoft SQL Server 2008 R2 on an in-house development server with the AdventureWorks database. I'm in the process of testing some code and the queries that I'm running are too fast no matter what I try!

Basically I'm testing a cut-off feature and need a sufficiently long query to be able to cut it off before it completes.

Unfortunately as it is a local installation there isn't a single query or large enough table in the AdventureWorks database to actually give me good data to work with. I've tried

WAITFOR DELAY '01:00'

Which worked great to just test to make sure it was working, but now I need to test to see if I can cut the data stream off mid-read. The WAITFOR statement doesn't do me justice in that respect because I need it to actively be retrieving data back from the server. My first intuition was to use convoluted calculations to slow it down, however even having SQL server multiply all the numerical values in the query by themselves 37 times only slowed down the query by milliseconds. The second thing I tried was embedding the WAITFOR statement in a sub-query but it appears you can't do that. Finally, the only thing I haven't tried is to execute multiple stored procedures and WAITFOR in between them, but I don't think that would work for what I need.

I have to say, I'm impressed at how hard it is to make an absolutely terrible query when you're this close to the server.

Is there any way I can slow down a query easily?

Thank you!

Solution 1

Just do a load of cross joins.

SELECT T1.*
FROM SomeTable T1,  
     SomeTable T2,  
     SomeTable T3,  
     SomeTable T4

For a 1,000 row table that will generate 1,000 billion rows which should be plenty slow enough.

Solution 2

DECLARE @EndTime DATETIME;
SET @EndTime = DATEADD(s, 5, GETDATE()); -- Set your delay here

WHILE @EndTime > GETDATE()
    SELECT 'Test Result'; -- Add your desired query here

EDIT

Another option using recursion:

Create a UDF wrapper for GETDATE() so that a new date value will be calculated for each row in the result:

CREATE FUNCTION dbo.GetExactDate()
RETURNS DATETIME    
AS
BEGIN
    RETURN GETDATE();
END

and then use a cte

DECLARE @EndTime DATETIME;
SET @EndTime = DATEADD(s, 5, GETDATE()); -- Set your delay here

WITH cte AS (
    SELECT dbo.GetExactDate() Value
    UNION ALL
    SELECT dbo.GetExactDate()
    FROM cte
    WHERE Value < @EndTime
)
SELECT Value
FROM cte
OPTION (MAXRECURSION 0);

This has the advantage of returning the results in one query, not many (like my first solution) while still being able to set the amount of time for which you would like the query to keep returning results.

Solution 3

Tested on SQL Server 2016: (SQL View query takes always exactly two seconds to reply)

/* Call WAITFOR DELAY inside SQL View */
/* Usefull for example for async testing */
CREATE FUNCTION WaitForDelay()
RETURNS INT
AS
BEGIN
  RETURN (
    SELECT Value FROM OPENROWSET (
    'SQLOLEDB', 'Trusted_Connection=yes;  Integrated Security=SSPI; Server=localhost; Initial_Catalog=master;',
    'WAITFOR DELAY ''00:00:02'' SELECT 0 AS Value'
  ))
END

GO
CREATE VIEW Wait AS
SELECT dbo.WaitForDelay() AS Value

GO
SELECT * FROM Wait /* Takes sql view 2 seconds to respond */

Shows Management Studio executing simple view slow down intentionally to 2 seconds:

https://i.stack.imgur.com/fE60u.png

Comments

[Admin]

Just make a really bad query off-index or force a normally-good query to use LOOP JOINs when it should be using HASH/MERGE ;-)

[Oded]

A few self cross joins will slow things up nicely... And a large result set would cause IO to be the bottleneck.

[David W]

The truth is both of the solutions (yours and kevins) worked great. This one bottlenecked the I/O AND caused my program to throw a few exceptions as well (even better!). So it helped pull some bugs out of my code. It generated close to 2,000,000 rows in under 30 seconds which was GREAT haha. If I could award both of you points I would, but I ended up using this solution so I'll give you the check.

[Devs love ZenUML]

Simple and neat.