Force SQL Server to respond slowly in order to test client application response to timeout

Question

Is it possible to somehow force Microsoft SQL Server to slow down certain or all queries so that we can test the client application on how it acts when the SQL queries run for extended time? For example, by seriously limiting its I/O or CPU to 1%.

Unfortunately it is not possible to modify the data in the database to add more rows.

I tried Forcing a query timeout in SQL Server but unfortunately the client application issues SELECT WITH (NOLOCK) so it ignores any locks we create.

Whenever I want to mess with SQL in the performance department I just cross join a decent sized table against itself a bunch of times. You end up with a resultset of R^(N+1) where R is the number of rows in the table and N is the number of times you cross join. Take a table with 1000 rows in it and Cross Join two times and you end up with a billion rows. — Eric J. Price, May 02 '13 at 14:49

Bogdan Sahlean · Accepted Answer · 2013-05-03T13:27:27.923

3

To force query timeout error for SELECT ... FROM MySchema.MyTable WITH (NOLOCK) queries:

SELECT ... FROM MySchema.MyTable WITH (NOLOCK) still requires Sch-S lock. According to Lock Compatibility (Database Engine), Sch-S lock has a conflict with Sch-M lock.

1) So, create a new query in SSMS and execute the next script:

BEGIN TRAN

ALTER TABLE MySchema.MyTable
ADD DummyCol INT NULL DEFAULT 0 -- It requires Sch-M lock

--ROLLBACK

2) Execute the queries (from client app. or from another SSMS query) with NOLOCK table hint:

SELECT  *
FROM    MySchema.MyTable d WITH(NOLOCK) -- It requires Sch-S lock

3) Now, you have to wait.

edited May 03 '13 at 13:27

answered May 02 '13 at 15:17

Bogdan Sahlean

19,233
3
42
57

`ALTER TABLE` requires `Sch-M` lock. T – Bogdan Sahlean May 02 '13 at 16:47
now this is interesting :) first thing tomorrow will check it out and hopefully it will do what we need.. – Knaģis May 02 '13 at 18:49
Don't try this on a production server. And, of course, don't forget to `ROLLBACK` first transaction. – Bogdan Sahlean May 02 '13 at 19:07

score 1 · Answer 2 · answered May 02 '13 at 14:35

1

Use WAITFOR DELAY to, well, make the code wait if you use stored procedures.

Controlling CPU requires Resource Governer, but I've not used it. IIRC, it requires Enterprise edition

answered May 02 '13 at 14:35

gbn

422,506
82
585
676

I cannot modify the client application to change those queries. – Knaģis May 02 '13 at 14:38
1

@Knaģis: what can you change? – gbn May 02 '13 at 14:39
SQL Server settings, probably some database setting changes as long as I can reverse them. – Knaģis May 02 '13 at 14:44

Force SQL Server to respond slowly in order to test client application response to timeout

2 Answers2