Estimating Query run times with QUERY_GOVERNOR_COST_LIMIT

Ive blogged on this subject before when trying to make a reindexing decision about whether to reindex an object online or offline based on the cost. https://dbamohsin.wordpress.com/2011/02/08/query-control-with-query_governer_cost_limit/

Its easy enough to apply the same theory for cost onto any query. I do this to give me a basic estimate of how long a query will take on a certain server.

I do this by setting a Query limit of 1 and then executing the query. If it is an intensive query then it will almost certainly fail and give you a cost estimate

SET QUERY_GOVERNOR_COST_LIMIT 1
GO
select * from this_is_my_big_table

The result is something like this:

Msg 8649, Level 17, State 1, Line 1
The query has been canceled because the estimated cost of 
this query (526) exceeds the configured threshold of 1. 
Contact the system administrator.

This immediately tells me that running the SQl for real is likely totake around the 10 minute mark (526 Seconds)

Note that cost is dependent on server hardware and configuration, running the same query on a test server produced the following output:

Msg 8649, Level 17, State 1, Line 1
The query has been canceled because the estimated cost of 
this query (694) exceeds the configured threshold of 1. 
Contact the system administrator.
Advertisements

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: