Query control with QUERY_GOVERNER_COST_LIMIT

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 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.

How I have used this in the past

There was a need to split re-indexing on a database per object so that re-indexing jobs were grouped where by one job had low cost objects and another had the high cost objects so they could be scheduled accordingly.

During an out of hours maintenance slot, i generated dynamic SQL to rebuild all indexes but set an cost limit of 25 seconds. anything which failed to execute was over the 25 second limit and therefore a high cost object. short example below:

SET QUERY_GOVERNOR_COST_LIMIT 25
GO

PRINT 'Reindexing Account_Status at ' + cast(getdate() as varchar(20))
ALTER INDEX ALL ON Account_Status REBUILD
PRINT 'Completed Account_Status at ' + cast(getdate() as varchar(20))
go

PRINT 'Reindexing Accounting_Period at ' + cast(getdate() as varchar(20))
ALTER INDEX ALL ON Accounting_Period REBUILD
PRINT 'Completed Accounting_Period at ' + cast(getdate() as varchar(20))
Go

PRINT 'Reindexing Action_Description at ' + cast(getdate() as varchar(20))
ALTER INDEX ALL ON Action_Description REBUILD
PRINT 'Completed Action_Description at ' + cast(getdate() as varchar(20))
GO


PRINT 'Reindexing Address at ' + cast(getdate() as varchar(20))
ALTER INDEX ALL ON Address REBUILD
PRINT 'Completed Address at ' + cast(getdate() as varchar(20))
GO

All the above are small tables so would be reindexed properly without error. However, a bigger table, with 34gb of indexes would struggle to reindex within 25 seconds on any system:

SET QUERY_GOVERNOR_COST_LIMIT 25
GO

PRINT 'Reindexing Order_insert at ' + cast(getdate() as varchar(20))
ALTER INDEX ALL ON Order_Insert REBUILD
PRINT 'Completed Order_Insert at ' + cast(getdate() as varchar(20))
go

and the resulting output would be:

Reindexing Order_insert at Feb  8 2011  3:58PM
The statement has been terminated.
Msg 8649, Level 17, State 1, Line 3
The query has been canceled because the estimated cost of this query (1918) 
exceeds the configured threshold of 25. Contact the system administrator.

This can also be an unorthodox way of finding out how long a query is estimated to take. eg, above according to SQL Server would take 1918 seconds (31 mins)

Advertisements

3 Responses to Query control with QUERY_GOVERNER_COST_LIMIT

  1. Pingback: Estimating Query run times with QUERY_GOVERNOR_COST_LIMIT « Mohsin's DBA Blog

  2. Pingback: Intelligent Reindexing on Filer based volumes « Mohsin's DBA Blog

  3. Pingback: Intelligent Reindexing on Filer based volumes | ismailozcan68

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: