November 30, 2009 at 3:56 am
"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 configure QUERY_GOVERNOR_COST_LIMIT 1500 ).?
Please share your idea fir the analysis and solution.
November 30, 2009 at 4:38 am
Have you traced to see what options are in effect (Ansi Null Default On, Ansi Nulls On, Ansi Padding On, Ansi Warnings On etc) at the point the SP is execute via the app?
.Net apps can specify within the connection what option are available and if connection pooling is enabled then these can be cached (in a way).
November 30, 2009 at 7:10 am
Connection settings differences makes the most sense. Especially the ANSI standard settings. Differences there will absolutely result in different execution plans.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply