Solved

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

Posted on 2006-07-19
2
791 Views
Last Modified: 2008-02-01
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
Comment
Question by:martini60123
2 Comments
 
LVL 11

Accepted Solution

by:
deroby earned 125 total points
ID: 17139366
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
 
LVL 21

Expert Comment

by:mastoo
ID: 17139659
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question