469,090 Members | 1,092 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

Query Governor Cost Limit

I have enabled the query governor on our SQL2000 SP2 server with a
threshold of 3600. Now, some of the maintenance jobs fail due to the
limit being to low (e.g. one of the user databases integrity check
fails nightly).

I have tried to put the command 'SET QUERY_GOVERNOR_COST_LIMIT 0' just
before the line in the step which reads 'EXECUTE
master.dbo.xp_sqlmaint N'-Plan etc'
but it has no effect.

Does anyone know how to get around this situation without using
sp_configure to change the query governor settings at a systemwide
level?

GC.
Jul 20 '05 #1
2 5647

"Garry Clarke" <gc*****@euro.banta.com> wrote in message
news:fe**************************@posting.google.c om...
I have enabled the query governor on our SQL2000 SP2 server with a
threshold of 3600. Now, some of the maintenance jobs fail due to the
limit being to low (e.g. one of the user databases integrity check
fails nightly).

I have tried to put the command 'SET QUERY_GOVERNOR_COST_LIMIT 0' just
before the line in the step which reads 'EXECUTE
master.dbo.xp_sqlmaint N'-Plan etc'
but it has no effect.

Does anyone know how to get around this situation without using
sp_configure to change the query governor settings at a systemwide
level?

GC.


I'm not entirely sure about this, however xp_sqlmaint is a wrapper for
sqlmaint.exe, and I assume that sqlmaint.exe connects to the server as a
separate connection. According to BOL, the SET option only affects the
current connection, ie. the one where you execute xp_sqlmaint. sqlmaint.exe
is effectively an entirely separate client program.

One workaround would be to write your own maintenance procedures with all
the DBCC commands etc. in a single batch and executing in the same
connection. Or set the server default back to zero, and use the SET option
only in the code that needs it, if that's less work.

Simon
Jul 20 '05 #2
Thanks - what I've tried to do is use the below commands but it still
ignores the QUERY GOVERNOR COST LIMIT setting so it's back to the
drawing board...

SET QUERY_GOVERNOR_COST_LIMIT 0
exec xp_cmdshell '"C:\Program Files\Microsoft SQL
Server\MSSQL\Binn\sqlmaint.exe" -S SERVER3 -D data -Rpt
e:\mssql\MSSQL\LOG\data_DB_Maintenance_Plan2.txt -DelTxtRpt 4WEEKS
-WriteHistory -CkDB'
"Simon Hayes" <sq*@hayes.ch> wrote in message news:<3f**********@news.bluewin.ch>...
"Garry Clarke" <gc*****@euro.banta.com> wrote in message
news:fe**************************@posting.google.c om...
I have enabled the query governor on our SQL2000 SP2 server with a
threshold of 3600. Now, some of the maintenance jobs fail due to the
limit being to low (e.g. one of the user databases integrity check
fails nightly).

I have tried to put the command 'SET QUERY_GOVERNOR_COST_LIMIT 0' just
before the line in the step which reads 'EXECUTE
master.dbo.xp_sqlmaint N'-Plan etc'
but it has no effect.

Does anyone know how to get around this situation without using
sp_configure to change the query governor settings at a systemwide
level?

GC.


I'm not entirely sure about this, however xp_sqlmaint is a wrapper for
sqlmaint.exe, and I assume that sqlmaint.exe connects to the server as a
separate connection. According to BOL, the SET option only affects the
current connection, ie. the one where you execute xp_sqlmaint. sqlmaint.exe
is effectively an entirely separate client program.

One workaround would be to write your own maintenance procedures with all
the DBCC commands etc. in a single batch and executing in the same
connection. Or set the server default back to zero, and use the SET option
only in the code that needs it, if that's less work.

Simon

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Ranjith | last post: by
13 posts views Thread by Dmitry Tkach | last post: by
4 posts views Thread by Joost Kraaijeveld | last post: by
1 post views Thread by traceable1 | last post: by
2 posts views Thread by matias.cornejo | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.