Systems Engineering and RDBMS

Putting cost limits on bad Queries

Posted by decipherinfosys on April 27, 2007

SQL Server has a query governor cost option that can be applied to throttle bad/runaway queries in the application.  This option (QUERY_GOVERNOR_COST_LIMIT) is present in SQL Server and can be changed either at the server level or at the connection level.  If you are going to change it at the server level, you have to use the sp_configure command and if you want to change it at the connection level, you can use the SET command for changing the setting for that connection only.  What this option enables does is that it applies a limit (in seconds) to the time duration for which a query can run.  Once this setting is in place, the query engine will not allow the execution of any queries that have an “estimated” (not runtime) cost (in seconds) that exceeds that value.  This setting takes place at execute or run time and not at the parse time.

So, where can one use this option?  We had a client application which required us to build an interface for doing adhoc reporting for end users.  If we expose the application to the non-technical end users via an interface and let them create the adhoc reports to run and if they make bad choices which results into bad SQL queries being formed, then those SQLs can put a lot of load on the system.  Guaranteed that we controlled the mapping and would not have allowed a lot of bad SQL formation through the code generator but there is still always a corner case.  So, at the time of making the connection, we set this setting to be 5 seconds and any query that exceeded 5 seconds would then run into an error.  If there is a particular query that goes above the threshold, then an error is raised and that error gets trapped and the user gets notified – they also get notified of potential modifications that they can make in order to make it work better.

This option can also help in benchmark efforts to weed out bad query code – though the same can be done by profiling the code and then later on querying atop the results to see which ones need to be fixed.  Having it at the connection level helps though since it does not put any load on the system and we can easily trap and log the different bad SQL code.

3 Responses to “Putting cost limits on bad Queries”

  1. […] by decipherinfosys on February 14, 2008 Sometime ago, we had posted on how to put cost limits on bad queries in order to prevent the runaway queries from hogging resources. In this post, we will cover another […]

  2. […] having control over the settings in an environment like that helps in keeping things in check. The query governor cost limit is also a very useful option in that regard. The Advanced and the ANSI options provide a lot of […]

  3. […] can also set limits on bad queries (could be done in prior versions in a different way – see here and here) and put priority – either at the user level so that some users have a higher priority […]

Sorry, the comment form is closed at this time.

%d bloggers like this: