473,729 Members | 1,907 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_s qlmaint 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 5881

"Garry Clarke" <gc*****@euro.b anta.com> wrote in message
news:fe******** *************** ***@posting.goo gle.com...
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_s qlmaint 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\Bi nn\sqlmaint.exe " -S SERVER3 -D data -Rpt
e:\mssql\MSSQL\ LOG\data_DB_Mai ntenance_Plan2. txt -DelTxtRpt 4WEEKS
-WriteHistory -CkDB'
"Simon Hayes" <sq*@hayes.ch > wrote in message news:<3f******* ***@news.bluewi n.ch>...
"Garry Clarke" <gc*****@euro.b anta.com> wrote in message
news:fe******** *************** ***@posting.goo gle.com...
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_s qlmaint 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
1689
by: Ranjith | last post by:
We have a console.php which takes in SQL queries and displays them in a result.php webpage. Sometimes the query takes minutes to execute or crashes the PHP application. Is it possible to cancel a query during execution? If yes, how does one go about it. The PHP Console is used by multiple users simultaneously. The queries are executed on a Remote Database.
13
1989
by: Dmitry Tkach | last post by:
Hi, everybody! Here is a weird problem, I ran into... I have two huge (80 million rows each) tables (a and b), with id as a PK on both of them and also an FK from b referencing a. When I try to run a query like: select * from a, b where a.id >= 7901288 and a.id=b.id limit 1; The query takes *forever*.
7
12088
by: Egor Shipovalov | last post by:
I'm implementing paging through search results using cursors. Is there a better way to know total number of rows under a cursor than running a separate COUNT(*) query? I think PostgreSQL is bound to know this number after the first FETCH, isn't it? On a side note, why queries using LIMIT are SO terribly slow, compared to cursors and sometimes even ones without LIMIT? Shouldn't LIMIT be internally implemented using cursor mechanism then?...
21
6977
by: CSN | last post by:
I have a pretty simple select query that joins a table (p) with 125K rows with another table (pc) with almost one million rows: select p.* from product_categories pc inner join products p on pc.product_id = p.id where pc.category_id = $category_id order by p.title
2
1598
by: Gellert, Andre | last post by:
Hello, I have a problem with this simple query : explain select * from ref_artikel a where a.artnr in ( 351275 , 351346 , 293082 ) LIMIT 20 OFFSET 0 ; QUERY PLAN ---------------------------------------------------------------------------- ------------------------------------------------------ Limit (cost=0.00..9.06 rows=3 width=299)
4
1342
by: Joost Kraaijeveld | last post by:
Hi everyone, I have a customer table (17518 records) and an orders table (88393 records). One of the columns of orders is customerid, containing the customerid (what else, but it is not a foreign key as this table is imported from a database that did not support foreign keys). If I do this query (with pgadmin III): select customer.id, customer.name, orders.id from customers, orders order by customer.id, orders.id limit 25
1
2556
by: traceable1 | last post by:
I have 2 SQL databases which are the same and are giving me different query plans. select s.* from hlresults h inner join specimens s on s.specimen_tk = h.specimen_tk where s.site_tk = 9 and s.location in ('ABC','WIAD') and s.date_collected between '2/1/2003' and '2/3/2006' order by s.location, s.date_collected
2
3332
by: P. Adhia | last post by:
Hi, setlimit configuration file clause, applies to each SQL, UOW or connection. Only setlimit uowtime is specifically mentioned as elapsed time since the start of UOW. For example if I have, setlimit cpu 60; action force;
2
1649
by: matias.cornejo | last post by:
I have some users that have 5 or 6 connections with the same login at the same time. I want to limit that each user can have less than 3 connectios per login. How can I do that?? Thanks in advance
0
8768
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9436
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9298
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9226
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9166
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6722
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4538
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3248
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2173
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.