SatishKartan
Blog Post
QUERY GOVERNOR COST LIMIT – SHOW ME SOME LOVE
- By Satish Kartan
- •
- 30 Aug, 2017
- •
How many of you are utilizing “Query Governor Cost Limit” (QGCL) configuration option? Probably very few. This seems to be an ignored and misconstrued option. By default, QGCL is off (a value of zero), which means that all queries will run indefinitely regardless of their estimated cost. To change QGCL to a particular value (say 300 seconds), you would execute the following code, which will cause SQL to not run the queries if their estimated cost is more than 300 seconds. Note that this may not be exactly 300 seconds on your server as this value is based on a hardware specification of a server that Microsoft used during their testing.
EXEC sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
EXEC sp_configure ‘query governor cost limit’, 300;
GO
RECONFIGURE WITH OVERRIDE;
Note that setting this option at the server level will clear the Plan cache – so you may want to implement it during non-peak hours. Versions prior to SQL 2005, QGCL could only be specified at the server level. Starting with SQL 2005, you can also set it at connection level with the following code. Setting QGCL at connection level will not clear the Plan cache. To set this to 300 seconds at a connection level, use the following code:
SET QUERY_GOVERNOR_COST_LIMIT 300
If you configure it to a non-zero QGCL value and if a query’s estimated cost is greater than the configured value, SQL will not run that query and will generate the following error:
“The query has been canceled because the estimated cost of this query (%d) exceeds the configured threshold of %d. Contact the system administrator.”
This can be a boon or a bane depending on the proactive or reactive culture of your company. However, QGCL will preclude any runaway queries from hogging your system. It can also warn you of any outdated statistics as the estimated cost can be skewed with stale statistics. It can also give a forewarning of resource scarcity. However, setting this option too low can make your application look bad as the queries fail to run.
“I would surmise for some companies with “show must go on” mentality, it is better to have runaway queries than failing queries. I would recommend starting using QGCL at least on your test servers to weed out problem queries. Use your discretion when deploying it on production servers” Satish Kartan.
Satish Kartan has been working with SQL Server for the past 20 years. To read more, please visit Satish Kartan's blog at http://www.sqlfood.com/ where he has shared more details on this.
EXEC sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
EXEC sp_configure ‘query governor cost limit’, 300;
GO
RECONFIGURE WITH OVERRIDE;
Note that setting this option at the server level will clear the Plan cache – so you may want to implement it during non-peak hours. Versions prior to SQL 2005, QGCL could only be specified at the server level. Starting with SQL 2005, you can also set it at connection level with the following code. Setting QGCL at connection level will not clear the Plan cache. To set this to 300 seconds at a connection level, use the following code:
SET QUERY_GOVERNOR_COST_LIMIT 300
If you configure it to a non-zero QGCL value and if a query’s estimated cost is greater than the configured value, SQL will not run that query and will generate the following error:
“The query has been canceled because the estimated cost of this query (%d) exceeds the configured threshold of %d. Contact the system administrator.”
This can be a boon or a bane depending on the proactive or reactive culture of your company. However, QGCL will preclude any runaway queries from hogging your system. It can also warn you of any outdated statistics as the estimated cost can be skewed with stale statistics. It can also give a forewarning of resource scarcity. However, setting this option too low can make your application look bad as the queries fail to run.
“I would surmise for some companies with “show must go on” mentality, it is better to have runaway queries than failing queries. I would recommend starting using QGCL at least on your test servers to weed out problem queries. Use your discretion when deploying it on production servers” Satish Kartan.
Satish Kartan has been working with SQL Server for the past 20 years. To read more, please visit Satish Kartan's blog at http://www.sqlfood.com/ where he has shared more details on this.
