SQL Server Performance Forum – Threads Archive
Query Cost Governor and error 8649
I have several SQL Servers and a couple of these have had the query cost governor set to 900 (To prevent runaway queries executed by a crap CRM product). Under the SQL Agent logs I have had numerous error logs with the following: Message[298] SQLServer Error: 8649, The query has been canceled because the estimated cost of this query (10987055) exceeds the configured threshold of 900. Contact the system administrator. [SQLSTATE 42000] I’ve checked this out on Microsoft and SP4 is supposed to have fixed this problem. It appears that this is a SQL system process that is generating these erors. Any ideas on how to prevent this from happening (apart from setting the governor back to 0, which defeats the object)? Thank you
N
That means you’ve SP4 applied to SQL server, may try applying the hotfix as per KBAhttp://support.microsoft.com/?kbid=840208 link. BTW what kind of activity is running on SQL server when this error is generated. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Not sure what is doing this but the sql agent log is logging this error every 30 seconds or so. Bit puzzling that two servers are doing this, both SQL2K SP4.
Check which job is scheduled to run during that time.
Use server side trace to catch the events. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Think it could be something to do with sp_sqlagent_get_perf_countersEXECUTE msdb.dbo.sp_sqlagent_get_perf_counters. <br /><br />This seems to be happening on exactly the same time (to the second) as the above errors are logged. A snippet is below on one of the statements…<br /><br />– sp_sqlagent_get_perf_counters<br />SELECT ‘object_name’ = RTRIM(SUBSTRING(spi1.object_name, 1, 50)),<br /> ‘counter_name’ = RTRIM(SUBSTRING(spi1.counter_name, 1, 50)),<br /> ‘instance_name’ = CASE spi1.instance_name<br /> WHEN N” THEN NULL<br /> ELSE RTRIM(spi1.instance_name)<br /> END,<br /> ‘value’ = CASE <br /> WHEN (spi1.cntr_type = 53700300<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> — A ratio<br /> THEN CONVERT(FLOAT, spi1.cntr_value) / (SELECT CASE spi2.cntr_value WHEN 0 THEN 1 ELSE spi2.cntr_value END<br /> FROM master.dbo.sysperfinfo spi2<br /> WHERE (spi1.counter_name + ‘ ‘ = SUBSTRING(spi2.counter_name, 1, PATINDEX(‘% Base%’, spi2.counter_name)))<br /> AND (spi1.instance_name = spi2.instance_name)<br /> AND (spi2.cntr_type = 1073939459))<br /> WHEN (spi1.cntr_value < 0) <br /> THEN CONVERT(FLOAT, CONVERT(bigint, spi1.cntr_value) + convert(bigint, 0x100000000))<br /> ELSE spi1.cntr_value<br /> END,<br /> ‘type’ = spi1.cntr_type<br /> <br /> FROM master.dbo.sysperfinfo spi1,<br /> #temp tmp<br /> WHERE (spi1.cntr_type <> 1073939459) — Divisors<br /> AND ((@all_counters = 1) OR<br /> (tmp.performance_condition = RTRIM(spi1.object_name) + ‘|’ + RTRIM(spi1.counter_name)))<br />
The stored procedure msdb.dbo.sp_sqlagent_get_perf_counters is the system SP which feeds the alert for SQLAgent service. If you want to change the interval of this alert you can modify the registry or if you want to avoid the alerts then remove all the alerts set under SQL Server management –> sqlagent –> alerts. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>