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.
Best Answer
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).