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

[sourcecode language=”sql”]
USE MASTER
go

EXEC sp_configure ‘show advanced options’, 1
GO

RECONFIGURE
GO

SELECT * FROM sys.configurations
WHERE name = ‘query governor cost limit’
GO
[/sourcecode]

Settings value before:

Query governor cost limit (before)

[sourcecode language=”sql”]
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
[/sourcecode]

Settings value after:

Query governor cost limit (after)

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

[sourcecode language=”sql”]
SET query_governor_cost_limit 100
[/sourcecode]

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.

Hope this helps,

_SqlTimes