What does this little check box do? Query Governor SQL Database Setting

Ever wander around SQL Server properties and wonder what these little check boxes turn on? I do, and I get very tempted to check them. Here is one of those tempting little boxes that seems pretty handy, Use query governor to prevent long running queries.

Syntax

EXEC sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE ; 
GO 
EXEC sp_configure 'query governor cost limit', 180; 
GO 
RECONFIGURE; 
GO

It’s simple. This option, available in SQL Server 2008 standard and forward, will prevent long running queries based on run time measured in seconds. If I specify a value of 180 the query governor will not allow any execution of a query that it estimates will exceed that value. Notice it says ESTIMATES which means it will be based on optimizer estimates and not ACTUAL run times. It does NOT KILL an actively running query after designated amount of time.  There is no worries for rollback scenarios or partial data.

CAUTION

This is an advanced option, keep in mind this is a server instance wide option. This will also effect your maintenance queries, so please use with caution, this is not “a let me check this box for fun” option.

But Wait There’s More

Now there is a query “transaction” based option available to us that will limit a specific query. This option will estimate a transaction and prevent it from running if it will go over the boundary we have set. Notice we set the limit before the query and then back to 0 after.

SET QUERY_GOVERNOR_COST_LIMIT 180

SELECT * FROM REALLYBADTABLE

SET QUERY_GOVERNOR_COST_LIMIT 0