Tip: Use the Query Governor to Control Excessive Query Execution

The query governor does not allow the execution of any query that has a running time that exceeds a specified query cost. The query cost is the estimated time, in seconds, required to execute a query, and it is estimated prior to execution based on an analysis by the query engine. By default, the query governor is turned off, meaning there is no maximum cost. To activate the query governor, complete the following steps:
1. In the Server Properties dialog box, go to the Connections page.
2. Select the option Use Query Governor To Prevent Long-Running Queries.
3. In the box below the option, type a maximum query cost limit. The valid range is 0 through 2,147,483,647. A value of 0 disables the query governor; any other value sets a maximum query cost limit.
4. Click OK.

With sp_configure, the following Transact-SQL statement will activate the query governor:
exec sp_configure "query governor cost limit", <limit>

You can also set a per-connection query cost limit in Transact-SQL using the following statement:
set query_governor_cost_limit <limit>

Note Before you activate the query governor, you should use the Query view to estimate the cost of current queries you are running on the server. This will give you a good idea of a value to use for the maximum query cost. You can also use the Query view to optimize queries.

From the Microsoft Press book Microsoft SQL Server 2008 Administrator's Pocket Consultant by William R. Stanek.

Looking for More Tips?

For more SQL Server Tips, visit the TechNet Magazine SQL Server Tips page.

For more Tips on other products, visit the TechNet Magazine Tips index.