Query Governor

Anybody out there have any luck (good or bad) using the server-level Query
Governor? We've tried to implement it, but when we run queries (primarily
selects) to test it, it doesn't seem to engage. Is there a source of better
info than BOL on what Query Governor actually does?
The "query governor cost limit" option allows you to limit the maximum length
a query can run on a server, and is one of the few SQL Server configuration
options that I endorse. For example, let's say that some of the users of your
server like to run very long-running queries that really hurt the performance
of your server. By setting this option, you could prevent them from running
any queries that exceeded, say 300 seconds (or whatever number you pick). The
default value for this setting is "0", which means that there are no limits
to how long a query can run.
The value you set for this option is approximate, and is based on how long
the Query Optimizer estimates the query will run. If the estimate is more
than the time you have specified, the query won't run at all, producing an
error instead. This can save a lot of valuable server resources.
On the other hand, users can get real unhappy with you if they can't run the
queries then have to run in order to do their job. What you might consider
doing is helping those users to write more efficient queries. That way,
everyone will be happy.
If this setting is set to "0", consider adding a value here and see what
happens. Just don't make it too small. You might consider starting with value
of about 600 seconds and see what happens. If that is OK, then try 500
seconds, and so on, until you find out when users start complaining.
"Tim Brown, DAC DBA" wrote:

> Anybody out there have any luck (good or bad) using the server-level Query
> Governor? We've tried to implement it, but when we run queries (primarily
> selects) to test it, it doesn't seem to engage. Is there a source of better
> info than BOL on what Query Governor actually does?
|||Another thing to know is that the governer makes the go/nogo decision after
the query has been optimized and before the query is run... It is possible
that the optimizers cost estimate is incorrect. The governer will NOT stop a
query that actually runs longer than the limit...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<Tim Brown>; "DAC DBA" <Tim Brown, DAC DBA@.discussions.microsoft.com> wrote
in message news:7855542F-3805-45B2-96C2-11B386C580C9@.microsoft.com...
> Anybody out there have any luck (good or bad) using the server-level Query
> Governor? We've tried to implement it, but when we run queries (primarily
> selects) to test it, it doesn't seem to engage. Is there a source of
better
> info than BOL on what Query Governor actually does?