5

In SQL Server, I'm getting the following error "The query has been canceled because the estimated cost of this query (5822) exceeds the configured threshold of 300. Contact the system administrator."

This is the result of an execution of a stored procedure, which is pretty complex. I haven't run into this for other stored procedures, only this one. Is it possible to change the query cost for this one procedure somehow? Can I do that in the stored procedure itself upon execution? Or do I have to define this on the server only?

I'm using ADO.NET command to execute the stored procedure.

Thanks.

|improve this question
  • 2
    Depending upon what the stored procedure does you might be able to alter it to have lower estimated cost by making things more opaque to the optimiser but seems like you should try and make it more efficient or discuss it with the system administrator. – Martin Smith Nov 21 '12 at 13:31
  • @MartinSmith It is a stored procedure that's responsible for setting up a yearly process, which has quite a few tables involved. I need the inserts all to be grouped together in a transaction; it needs to be in the same stored procedure. The process is run by a windows service, so however long it takes is not an issue (but it is fairly efficient as is). I just need it to run... I'm sending an email to the administrators to cover that end, but need to be aware of what to do if that doesn't work out. Thanks. – Brian Mains Nov 21 '12 at 13:36
5

It sounds like you're running into query governor cost limits. What's happening is that the database engine figures that your query will take 5822 seconds (97 minutes) to run. Somebody, however, has configured a setting to kill any query which is expected to run more than 300 seconds (5 minutes).

To see if this applies to you, exec sp_configure 'query governor cost limit' and see what that run_value is. My thought is that it's currently set to 300.

If you don't want to change the limit across the board, you should be able to run this query by setting the limit beforehand: set query_governor_cost_limit 0. That will adjust the particular session to turn off any cost limits, as you know that your query will blow past the limit and you don't much care. Do note that you'd need some serious permissions to run that statement (the MSDN doc says sysadmin).

|improve this answer
  • That makes a lot of sense, thanks. I'm not surprised a setting like that requires a heavy permission. – Brian Mains Nov 21 '12 at 15:49
  • 3
    I have seen bad estimates from the query governor in the past, which prevented reasonable queries from running. If you google around, you will find examples of this from others. I think that it is also worth mentioning that Microsoft dissuades use of the old "Query Governor" in favor of the "Resource Governor" that debuted in SQL Server 2008. An overview of the Resource Governor is here-> msdn.microsoft.com/en-us/library/bb895232(v=sql.105).aspx – darin strait Nov 21 '12 at 16:07
5

Your administrator has configured the global query governor cost limit to 300 (5 minutes). Seems like a decent protection to protect against unoptimized procedures being deployed. You have deployed such an unoptimized procedure and got caught. Your action should be to optimize the procedure. Find out which query in the procedure has such a high cost (the cost is per query, not per procedure), see what index you're missing and fix it. Don't ask how to circumvent the (reasonable) max and how to be allowed to run a query with an estimate cost of nearly 2 hours...

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.