Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

The query has been canceled because the estimated cost of this query (3373) exceeds the configured threshold of 300 Expand / Collapse
Author
Message
Posted Tuesday, May 5, 2009 12:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 7, 2012 4:47 PM
Points: 31, Visits: 193
Hi,

The sql which is running using sql server agent produces the below error:

"The query has been canceled because the estimated cost of this query (3373) exceeds the configured threshold of 300"

We have never encountered the above error, so far.

Running the sql at backend does not produce any error. Also, have checked the query governor property (which is set 300)

Appreciate any help!!!

Thanks in advance...








Post #709933
Posted Tuesday, May 5, 2009 1:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 6:56 AM
Points: 1,330, Visits: 1,434
Hello,

Is it possible that somebody e.g. a SQL Server Administrator, has recently changed the query governor cost limit?

I am just wondering why your job was working before, and not now. Have there been any other major changes on the Server e.g. a large increase in data volume or removal of indexes that might cause the job to run a lot longer than before?

Regards,

John Marsh


www.sql.lu
SQL Server Luxembourg User Group
Post #709952
Posted Tuesday, May 5, 2009 2:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 7, 2012 4:47 PM
Points: 31, Visits: 193
No, I dont think anyone has changed the query cost time...

Even after increasing query cost to 1200, there was no difference...

Now, even the sql's (using except) are not running in backend. So i have changed query governor setting to 0 and remote query timeout to 0
Post #709968
Posted Tuesday, May 5, 2009 2:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 7, 2012 4:47 PM
Points: 31, Visits: 193
I had increased query cost to 1200, there was no difference... Now, even the sql's (using except) are not running in backend.

So i have changed query governor setting to 0 and remote query timeout to 0 which seems to have solved teh problem...Am not sure if anyone has changed the query cost time,earlier...

Thanks..
Post #709970
Posted Tuesday, May 5, 2009 6:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 13,865, Visits: 28,262
Just a question, why have the governor in place at all if you're going to remove the limits as soon as you hit them?

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #710121
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse