question about " query governor cost limit " 
Author Message
 question about " query governor cost limit "

hi all

i have set my
 query governor to prevent queries exceeding specified cost =10
& restart my sqlserver
so i run two statement in query analyzer
fisrt:
 begin tran
 select * from mytable with(holdlock) where id=1

(open a new query)
second:
 begin tran
 update mytable  set col1=col1 where id=1

 here the second process is blocked by the first process
 but it doesn't quit when 10 seconds are passed, i have thinked
 that "query governor cost limit " is set to 10(s),so the blocked
 process will quit when 10 seconds pass
 i wonder why?

 best reguard



Sat, 01 Jan 2005 15:45:19 GMT
 question about " query governor cost limit "

wh,

The query governor handling (estimation) is done by the optimizer. At that stage, it doesn't
know whether you will run into blocking problems when the query executes. So, the governor only
takes into account the resource usage (like large scans, high CPU utilization). Check out SET
LOCK_TIMEOUT...

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...

Quote:

> hi all

> i have set my
>  query governor to prevent queries exceeding specified cost =10
> & restart my sqlserver
> so i run two statement in query analyzer
> fisrt:
>  begin tran
>  select * from mytable with(holdlock) where id=1

> (open a new query)
> second:
>  begin tran
>  update mytable  set col1=col1 where id=1

>  here the second process is blocked by the first process
>  but it doesn't quit when 10 seconds are passed, i have thinked
>  that "query governor cost limit " is set to 10(s),so the blocked
>  process will quit when 10 seconds pass
>  i wonder why?

>  best reguard



Sat, 01 Jan 2005 15:49:26 GMT
 question about " query governor cost limit "
Hi there,

My guess is that your second query is not actually running, but rather
waiting for a lock to be released. If you want to eliminate this kind of
wait times, you should instead take a look at SET LOCK_TIMEOUT.

HTH,

Andrs Taylor
http://www.sql.nu/


Quote:
> hi all

> i have set my
>  query governor to prevent queries exceeding specified cost =10
> & restart my sqlserver
> so i run two statement in query analyzer
> fisrt:
>  begin tran
>  select * from mytable with(holdlock) where id=1

> (open a new query)
> second:
>  begin tran
>  update mytable  set col1=col1 where id=1

>  here the second process is blocked by the first process
>  but it doesn't quit when 10 seconds are passed, i have thinked
>  that "query governor cost limit " is set to 10(s),so the blocked
>  process will quit when 10 seconds pass
>  i wonder why?

>  best reguard



Sat, 01 Jan 2005 15:52:45 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. question about "query governor cost limit "

2. Query Governor Cost Limit Values

3. max of ("...","...","..")

4. query governor cost

5. "Assembly" Database for Cost Estimating

6. Cluster "isalive" Query security related question

7. Question about queries using "UNION"

8. Question about queries using "UNION"

9. "newbie" Query Analyzer question

10. Question: Research on "typical" database queries

11. "LIMIT" problem

12. "LIMIT" problem again


 
Powered by phpBB® Forum Software