March 7, 2006 at 11:08 am
Hi,
I have an app where some of the sqls tend to take more than 3 min to run and takes up all resources. What setting do I need to do (and where ) to prevent any query to run more than 3 min ?
Add on question ..
How can this be done at a session level or app level ?
TIA
KNM
March 8, 2006 at 4:09 am
See Books onLine topic "query governor cost limit" which will not allow SQL statements that exceed the ESTIMATED run time to start.
Since there may be a time window when long running queries are allowed to run and setting this limit at the server level does not require a restart, this command could be in a scheduled job.
e.g.
long run queries for reports are allowed to run during the time period of 1 AM to 5AM, so at 1AM, run a job that sets the limit to infinity and then have another job that runs at 5AM that sets the limit.
Here is the help text:
Use the query governor cost limit option to specify an upper limit for the time in which a query can run. Query cost refers to the estimated elapsed time, in seconds, required to execute a query on a specific hardware configuration.
If you specify a nonzero, nonnegative value, the query governor disallows execution of any query that has an estimated cost exceeding that value. Specifying 0 (the default) for this option turns off the query governor. In this case, all queries are allowed to run.
If you use sp_configure to change the value of query governor cost limit, the changed value is server-wide. To change the value on a per connection basis, use the SET QUERY_GOVERNOR_COST_LIMIT statement.
query governor cost limit is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change query governor cost limit only when show advanced options is set to 1. The setting takes effect immediately (without a server stop and restart).
SQL = Scarcely Qualifies as a Language
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply