Resource Governor Lite with QUERY_GOVERNOR_COST_LIMIT ?

Recently I had an idea about trying to emulate some of the functionality of Resource Governor using options available in Microsoft SQL Server Standard Edtion. The SET Option QUERY_GOVERNOR_COST_LIMIT is available in all versions of SQL Server, and promises to let us configure a maximum limit on the cost of a query that will be allowed to execute. This is what Books Online has to say about it:

“Use the query governor cost limit option to specify an upper limit on the time period in which a query can run. Queries that have estimated run times greater than this limit, return an error and are not executed. Query cost refers to the estimated elapsed time, in seconds, required to complete a query on a specific hardware configuration.

If you specify a nonzero, nonnegative value, the query governor disallows execution of any query that has an estimated cost exceeding that value. Specifying 0 (the default) for this option turns off the query governor, and all queries are allowed to run without any time limitation.

If you use sp_configure to change the value of query governor cost limit, the changed value is server wide. To change the value on a per-connection basis, use the SET QUERY_GOVERNOR_COST_LIMIT statement.

The query governor cost limit option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change query governor cost limit only when show advanced options is set to 1. The setting takes effect immediately (without a server restart).”

So, in other words we can set it on the server level but that affects all queries even the too costly ones that we would like to let run anyway, and the users themselves can change it on the session level.

Scenario: You have a SQL Server instance with a homegrown application. Users are not only connection via the application, but also via Excel, Access, Query Analyzer, PowerPivot or other similar uncontrolled ways. You would like to let the continue with that, but not in a way that kills the performance of the entire server, and all other users of your application.

The plan is as follows:

Configure QUERY_GOVERNOR_COST_LIMIT to 100 on the server level using the following code.

exec sp_configure ‘show advanced options’, 1

go

exec sp_configure ‘query governor cost limit’, 10 –(For our testing purposes only. In real life you would likely set it higher, for example 200)

go reconfigure

go

Then, since this is a homegrown application, you configure your application to remove the limit on its own sessions at logon time, using the following statement:

SET QUERY_GOVERNOR_COST_LIMIT  0

This would work well, atleast until your rouge users read this blog post 🙂

To verify the functionality of this, log on to SQL Server and run the following query:

select * from master.sys.objects ma cross apply msdb.sys.objects ms

You should get the following error: Msg 8649, Level 17, State 1, Line 2 The query has been canceled because the estimated cost of this query (34) exceeds the configured threshold of 1. Contact the system administrator.

Now run our secret command to give you access to the unlimited power of SQL Server:

SET QUERY_GOVERNOR_COST_LIMIT  0

Run the query again: select * from master.sys.objects ma cross apply msdb.sys.objects ms

…and you should succeed!

Good luck!