How to simulate SQL Server under an unworkable load?

Question

Our company are currently beta testing an application which uses our SQL Server 2008 box as a backend..

One day our server was under some serious strain to the point where the only way to recover it was to reboot.

During this time of heavy load the we found an interesting bug in the application which manifests itself when the backend database times out.

Now to be able to send the company some decent stack trace data and screenshots I need to replicate the error..

How can I put the SQL server under an unworkable amount of strain / make all queries timeout?

Answer 1

I wanted to simulate Sql server deadlock and timeout. Deadlock is hard to replicate, but sql client timeout is relatively easy to reproduce.

BEGIN TRANSACTION
SELECT TOP 1 * FROM MyTable WITH (TABLOCKX, HOLDLOCK)
ROLLBACK TRANSACTION

This will lock the table and any select query will timeout. The timeout value depends on your client's library settings.

If you want to lock the table for a certain time, you can use waitfor delay (https://stackoverflow.com/a/798234/437961)

BEGIN TRANSACTION
SELECT TOP 1 * FROM MyTable WITH (TABLOCKX, HOLDLOCK) WAITFOR DELAY '00:05'
ROLLBACK TRANSACTION

Answer 2

If you want to simiulate a long running query, you can use this WAITFOR DELAY '00:00:35'.

Answer 3

There are a couple of tools to simulate heavy usage, these were for sql 2005 but they probably work in 2008 too.

Actually, this has been asked before on S/O so I'll just paste that question:

How to simulate heavy database usage with SQL server 2005

Answer 4

If all you want is for the app to timeout, you can just start transactions on your tables to block other processes. For instance, this will lock MyTable until you COMMIT:

BEGIN TRANSACTION

SELECT top 1 * FROM MyTable

--COMMIT

Unless you have finagled concurrency settings this should do the trick without any other scripting gymnastics.