MSSQL Query Cancelled ... estimated cost exceeds the configured threshold of 60.

[fgomes]

Following is the message received from a client:

===========================================
The below is the error we got when we try to run a query.

"Microsoft OLE DB Provider for SQL Server error '80040e14'

The query has been canceled because the estimated cost of this query (417) exceeds the configured threshold of 60. Contact the system administrator."

We even tried by creating a procedure in the sql server and run the same query. But still it gives us the same error.

Pls tell us what could be the possible reason, and let us know how to get rid of this.

=============================================

I am told that the above problem did not occur with their previous hosting company. It seems that it is the setting at the server that is causing the above abort. Can something be done about it. It is over a week now.

Thanks

fgomes


Vortech support ticket number: 106956

[Brangwyn]

See here also http://www.matrixwebhosting.net/foru...light=governer

Brad, Matt, someone please at least get these things in sync so the treshold is even across machines.

Consider removing it all together or at least raising it to around 500

[admin]

I thought it was 60 on all system not 500.. From what I have read 500 is a little "high".

[Brangwyn]

It was 30 according to that other post, I hit 125 on another server a few days ago. 500 isn't very high I couldn't even delete a few hundred thousand rows from a Database the other day.

In my personal opinion the use of a governer isn't a good idea to begin with, there maybe other issues such as parallal execution thresholds (if its a dual CPU box) which could be tweaked to perhaps improve performance before resorting to a goverener

[fgomes]

Brad, I am with Brangwyn on this. Keeping it at 500 should be considered as I am told that it involves only two tables (kind of master detail) with inner joins, and it had worked at their earlier location. I am familiar with Informix and Oracle performance tuning, so unable to offer any suggestion other than creating indexes, or stored procedure. And they have tried them with no success. I dont have access to their database to analyse the problem.