Governors

22 Mar 2021 10142 views 0 minutes to read Contributors

SQL Server Query Governor

Consider using the SQL Server query governor cost limit option to prevent potentially long running queries from ever executing.

Setting this option causes any query to be analyzed and if it exceeds the cost limit it will not allow the query to run. Instead it will generate the following error:

Error: "The query has been canceled because the estimated cost of this query (####) 2597 exceeds the configured threshold of (####) 300. Contact the system administrator."

This option should only be set on a system that is thoroughly optimized because it does not discriminate any application, transaction or user.

In SQL Server Properties enable the checkbox and set a cost threshold that any query cannot exceed.

SQL Server Resource Governor

SQL Server Resource Governor is a feature that you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU, physical I/O, and memory that incoming application requests can use.

Resource Governor can be scoped to a any Application, User, Object, Query or situation via the Classifier function name. A standard T-SQL function in which you can decide if you want to apply a resource limit. The Resource Governor check is done at Login of a user.