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.

Answers


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.


Need Your Help

Script to save varbinary data to disk

sql sql-server sql-server-2005 varbinary

I have some varbinary data stored in a table in MS Sql Server 2005. Does anyone have SQL code that takes a query as input (lets say the query guarantees that a single column of varbinary is return...

while loop applying custom function to mysql_fetch_assoc

php mysql function while-loop

I have a question regarding applying a custom function to mysql_fetch_assoc while it is in the while loop. See code below.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.