SQL Server Performance Tuning - QUERY_GOVERNOR_COST_LIMIT option

By Santhosh N

This explains the QUERY_GOVERNOR_COST_LIMIT option for controlling long running queries in the SQL Server.

While working with large databases and if you are required to query for larger datasets of the result, there is every possibility that your query runs for larger amount of time and at times you need to control this for overall better performance of the server.

In this case, you can use QUERY_GOVERNOR_COST_LIMIT option to limit the time you can allow for the query to be executed, failing which an exception is thrown to the client.


You can specify a nonzero and non-negative value which means the execution of the query is disallowed by the query governor if the estimated cost exceeds the value set. Specifying 0, which is default, turns off this option and all the queries are allowed to run without any cost limit.


Syntax: SET QUERY_GOVERNOR_COST_LIMIT n
Where n is the integer value which specifies the longest time a query can run.

Related FAQs

This explains where to have a loop if at all required to have one to get the desired result from the database.
This explains how to approach and check for bottlenecks in the SQL queries.
This explains the various precautions needs to be taken care while indexing and choosing what columns needs to be indexed for better performance.
This explains about using functions with Indexed columns in Sql query
This explains the performance overhead of using NULL values in the columns of the Database table.
This describes the usage of DBCC SHOW_STATISTICS command which is used for performance tuning in the Sql Server.
SQL Server Performance Tuning - QUERY_GOVERNOR_COST_LIMIT option  (1377 Views)