Solved

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

Posted on 2006-07-19
2
816 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

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?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

617 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