SQL server

QUERY_GOVERNOR_COST_LIMIT.

The explanation of QUERY_GOVERNOR_COST_LIMIT is simple: specify the longest (elapsed) time in seconds that a query can run. I found this interesting as in pre-2008 systems there is no resource governor. Also, this setting is available from SQL 2000 onwards. Hence I looked into this setting the first time I saw it on BOL.

Now the explanation is a bit misleading. You cannot set a value and run a query and relax without worrying about it crossing the set time. How it works is that the query will not even run in the first place if it will take longer that the set time. The query run time is estimated, and if this time is higher than the value that was set then the query will not run.

I found this feature interesting as there are times when I have to do some test queries on a busy system, but am not sure how intensive they are. I tested this setting on a test server.

set query_governor_cost_limit 3
select * from myTable

I got the following response: Msg 8649, Level 17, State 1, Line 2
The query has been canceled because the estimated cost of this query (39) exceeds the configured threshold of 3. Contact the system administrator.

I changed the value to 39 and now the query ran. It look longer than 39 seconds for the query to get the full result set. I did a set statistics time on; select * from myTable;

It showed a CPU time of 6 seconds and elapsed time of 90 seconds.  The elapsed time was correct, so maybe the estimated time was off because of my statistics.  I did a dbcc show_statistics(‘myTable’,myTable_CPK_000). The result showed that it had been 2 years since the stats were updated. Naturally the next step was to update the statistics: UPDATE STATISTICS myTable. Next I ran my query with query_governor_cost_limit at 3, but it still returned the same error with an estimated cost of 39. I guess it was not the stats after all.

Advertisements
Standard

One thought on “QUERY_GOVERNOR_COST_LIMIT.

  1. GREAT!! I read all this and then got to the end and theres no solution or conclusion other than that you dont know. I guess time to move on to another article.

Thinking about someting? 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