Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2006-07-19
2
Medium Priority
?
845 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 375 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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

971 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