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

Question

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

I am getting error as above on live while running one of the stored procedure threads where parameter contain XML variable.

I have checked the configuration value of QUERY_GOVERNOR_COST_LIMIT is set to 1500. To get resolve this problem i have added SET QUERY_GOVERNOR_COST_LIMIT 0 in stored procedures. And it is working fine.

When i run stored procedures in back end with and without SET QUERY_GOVERNOR_COST_LIMIT 0 statement, it is running fine, and run within 0 seconds. But it is creating problem with .Net application and getting error.

So, why it is giving error with application and not with SQL Query analyzer? Even query is run within 0 seconds as it can give error when execution time will exceed more then 15 seconds (as configured QUERY_GOVERNOR_COST_LIMIT 1500 )?

Please share your idea for the analysis and solution.

Answer 1

usually this happens because of different default ANSI setting for SSMS and .net they could create different execution plans. the first you need to check is the execution plans from both sources. you can do this with sql profiler's Showplan XML event

Answer 2

Could be because SET ARITHABORT is OFF from .NET

could also be a conversion problem, look at your execution plan do you see any conversions. How are you executing this from .NET and are you using the correct data types?