CODEWITHSUNDEEP
sql-server
salman
salman

Reputation:

How to stop a cpu/time consuming running query on SQL Server!

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.

Upvotes: 1

Views: 4335

Answers (3)

KM.
KM.

Reputation: 103717

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!

Upvotes: 0

Guy
Guy

Reputation: 9816

You could roll-your-own solution here, or upgrade to SQL 2008.

Managing SQL Server Workloads with Resource Governor

Upvotes: 1

Cybergibbons
Cybergibbons

Reputation: 464

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

Upvotes: 0

Related Questions