Solved

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

Posted on 2006-07-19
2
781 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

896 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now