Setting the cost limit for a query... 
Author Message
 Setting the cost limit for a query...

Hi all,

I would like to limit the amount of time that SQL 7 spends on running a
query.  I read about the SET QUERY_GOVERNOR_COST_LIMIT command that
would work for the current connection.

I have not been able to use this command succesfully.  I set the cost
limit to 60 seconds, but the following query still executes for a
another minute.

using the query analyser, the following is what I do:

SET QUERY_GOVERNOR_COST_LIMIT 60

SELECT * FROM ...

any thoughts?

Sent via Deja.com http://www.***.com/
Before you buy.



Sun, 06 Oct 2002 03:00:00 GMT
 Setting the cost limit for a query...

It is not necessarily seconds. It is rather a "tick" which happened to
correlate to seconds on the test/reference machine which MS used when
developing the code. If your machine is faster, set this to a higher
value...
Also, it is an estimation, and the optimizer can go wrong here...
--
Tibor Karaszi, Cornerstone Sweden AB
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Please reply to the newsgroup only, not by email.
Quote:

> Hi all,

> I would like to limit the amount of time that SQL 7 spends on running a
> query.  I read about the SET QUERY_GOVERNOR_COST_LIMIT command that
> would work for the current connection.

> I have not been able to use this command succesfully.  I set the cost
> limit to 60 seconds, but the following query still executes for a
> another minute.

> using the query analyser, the following is what I do:

> SET QUERY_GOVERNOR_COST_LIMIT 60

> SELECT * FROM ...

> any thoughts?

> Sent via Deja.com http://www.deja.com/
> Before you buy.



Sun, 06 Oct 2002 03:00:00 GMT
 Setting the cost limit for a query...
Thanks for the reply.

It makes sense... but no metter what value I assign I could not get the
query from being executed ( I used the value 1, 500, and 1000 just to
see what happens...).  From what I understand, when the cost value is
set the optimizer makes its estimation and if the query is too costly
the system prevents it from running ( do not know if it raises an
exception that I can trap or it just stops the execution).  I have not
been able to see this running yet! Is there another setting that needs
to be turned on for the database?  or Maybe the optimizer needs to be
tuned? the help is not very clear on how to use this feature...



Quote:
> It is not necessarily seconds. It is rather a "tick" which happened to
> correlate to seconds on the test/reference machine which MS used when
> developing the code. If your machine is faster, set this to a higher
> value...
> Also, it is an estimation, and the optimizer can go wrong here...
> --
> Tibor Karaszi, Cornerstone Sweden AB
> MCDBA, MCSE, MCSD, MCT, SQL Server MVP
> Please reply to the newsgroup only, not by email.




Quote:
> > Hi all,

> > I would like to limit the amount of time that SQL 7 spends on
running a
> > query.  I read about the SET QUERY_GOVERNOR_COST_LIMIT command that
> > would work for the current connection.

> > I have not been able to use this command succesfully.  I set the
cost
> > limit to 60 seconds, but the following query still executes for a
> > another minute.

> > using the query analyser, the following is what I do:

> > SET QUERY_GOVERNOR_COST_LIMIT 60

> > SELECT * FROM ...

> > any thoughts?

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.


Sun, 06 Oct 2002 03:00:00 GMT
 Setting the cost limit for a query...
And further to Tibor's reply it works on the estimated cost and stops the query
from running at all.  If the estimate is too low then it runs and nothing stops
it running after the limit is reached

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please reply only to newsgroups)
 SQL FAQ (428 entries) see
 forumsb.compuserve.com/vlforums/UK/default.asp?SRV=MSDevApps (sqlfaq.zip - L7
- SQL Public)
 or www.ntfaq.com/sql.html
 or www.sql-server.co.uk
 or www.mssqlserver.com/faq



Sun, 06 Oct 2002 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Query Analyzer - I/O Cost, CPU Cost, and Cost

2. Query Governor Cost Limit Values

3. question about "query governor cost limit "

4. Query regarding SQL Query - Limiting Result Set

5. MS-SQL: Costs, benefits, limits for web apps?

6. Cost limit.

7. Cost limit.

8. Cost of DISTINCT to remove duplicates from result sets

9. HOW TO SET THE OPTIMIZER_MODE TO COST IN ORACLE 7.2.3 / 7.3.3

10. cost of setting tuning parameters

11. SET EXPLAIN - why are INSERT costs high???

12. Q: Estimated Cost from SET EXPLAIN?


 
Powered by phpBB® Forum Software