Use Query Governor to Prevent Long Running Queries

Today I learned about a new instance level option, its called Query Governor. Looks like its been an available feature for a while, but I never heard of it before. This setting allows you to prevent any queries running longer than a predetermined set value. Any query that has a running cost value more that a set number (Cost measured in seconds).

You can set it two ways. T-sql and SSMS.

1. T-Sql: Since this option is an advanced option, first we must set ‘show advanced options’. Then RECONFIGURE to be able to set ‘query governor cost limit’ value

1 2 3 4 5 6 7 8 9 10 11 12

USE MASTER go   EXEC sp_configure 'show advanced options', 1 GO   RECONFIGURE GO   SELECT * FROM sys.configurations WHERE name = 'query governor cost limit' GO

Settings value before:

Query governor cost limit (before)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

USE MASTER go   EXEC sp_configure 'query governor cost limit' , 100   -- 100 seconds GO   RECONFIGURE GO   SELECT * FROM sys.configurations WHERE name IN ('query governor cost limit')   EXEC sp_configure 'show advanced options', 0 GO   RECONFIGURE GO

Settings value after:

Query governor cost limit (after)

Also, you can set this value per connection using SET command:

1	SET query_governor_cost_limit 100

2: Right click on server >> properties >> Connections >> Check box (Use query governor to prevent long running queries)

Query Governor in SSMS

Points to keep in mind:

1. Before you activate ‘query governor’ setting in your production environment, get an estimate of the cost of queries on your production system.