Hi All
Let's imagine I have several hundred queries running on my sql server. Now I want to protect my server against bad queries by stopping these queries automatically. How can I achieve this behavior in SQL Server 2005 without using profiler and KILL command?
Thanks, Salman Shehbaz.
SQLServer has query execution timeout, which times out a query if it is not allocated memory within a given time, based on the estimated cost in the execution plan (it waits 25 times as long as estimated by default). If a query frequently times out, it needs optimising or you need more memory.
If you are looking at already running queries that are taking longer than expected, the only real way is to use the profiler and identify what the problem is. It's not good practice to kill queries if they are taking too long in a production system.
This may provide some help
create a new stored procedure based on something like:
sp_lock
sp_who2
beta_lockinfo (http://www.sommarskog.se/sqlutil/beta_lockinfo.html)
Just copy one of those stored procedures and edit it to find what is a "bad query" and then issue a KILL on the spid. Create a job to run every N minutes and run this stored procedure.
Warning, your job may find itself a "bad query" and kill itself. This approach will waste system resources, but if you really want to do this, give this method a try!