Query Governor Threshold limit

• John Rose-392294

  SSC-Addicted

  Points: 424

  More actions

  June 29, 2011 at 11:52 am

  #253643

  Hi -

  We are running SQL 2005 build 4053 on a Windows 2003 quad processor 24 GB of memory and I'm running into an issue that just started today.

  We run a hosted application on our website that enforces a SQL Governor threshold. For one report in our application, it has been erroring out saying the cost is too high. When I capture the underlying query, I run the statement SET QUERY_GOVERNOR_COST_LIMIT 1 GO to purposely make the query fail so that I can get the cost of this query.I get back a message "The query has been canceled because the estimated cost of this query (99980171) exceeds the configured threshold of 1. Contact the system administrator."

  I don't understand why the cost has been calculated so high. We have just recently imported in some new data into the database. Auto Update and Auto Create statistics is turned on and I have run "Update Statistics" on all tables in the database. If I look at the estimated execution plan, 100% of the cost is due to a sort operation. When I run the same query against a back up of the database from a couple of days ago on the same server the estimated cost is 843.

  Other than updating the statistics, what other maintenance can I do on this particular database, since it is only this database that is generating such a high cost for the query.

  I could post the query, but I think the query is irrelavent. The query hasn't changed for years and runs fine on a backup database on the same server. So I really think there is something in the particular database I'm using that is causing this issue.

  Thank you in advance for any help provided.