Force SQL Server to respond slowly in order to test client application response to timeout
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.
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.