Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 860
  • Last Modified:

using query governer to limit long running queries in MS SQL 2005

We have a MS Access aplication that uses MS SQL 2005 as back end.  We have a client that locks up when a particluar query is run. It does not happen all the time but when it does it appears that this query is the culprit.  We are rewriting the query for the long term fix. For the short term i wanted to know if using the query governer to kill the query if runs too long (over 10000 ms) will do the trick?  or am i just barking up the wrong tree?
0
martini60123
Asked:
martini60123
1 Solution
 
derobyCommented:
AFAIK query governer will cause the query to be 'refused' when it detects that the query will take to long. Which is even better I suppose ...

So I think you've found the right tree all right =)

From the BOL :

query governor cost limit Option
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).

0
 
mastooCommented:
Unless it's a pass-through query in Access, the sql you see may not bear any resemblence to what actually runs on Sql Server.  Access may decide to run a couple of quick queries to get the data it needs and then process the results locally, instead of letting the server do it.  This locks up the client with little load on the server, so the governor won't kick in.  You might run sql profiler to watch what sql is actually sent to sql server when executing the access query.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now