jpveen
European
Hi All,
I run a couple of DotNetNuke websites for several customers. Recently my customers complain about exceptions.
In the logs I find sometimes MSSQL messages like
"System.Data.SqlClient.SqlException: The query has been canceled because the estimated cost of this query (289) exceeds the configured threshold of 60. Contact the system administrator."
I have contacted jodohost support through a ticket, and they say that they cannot (or do not want) to change the QUERY_GOVERNOR_COST_LIMIT variable in MSSQL to an higher value.
I understand that too high values will encourage bad sql-statements, but it looks to me that a value of 60 is not very high.
Also I have found out that the MSSQL estimation-process does not produce correct estimations in several circumstances. (And that they are based on SQL running on a 100MHz Pentium-1 machine.)
To be sure that the problems are not caused by DotNetNuke bugs I am currently upgrading some sites (from 4.5.1 to 4.5.5 which is the latest release). Bu during upgrade I run into blocking problems, all caused by cancelled "exceeds estimated cost" queries.
For example a very simple SQL statement that gets such an error:
delete
from dbo.Modules
where PortalID is null
and ModuleTitle = 'Solutions'
(A simple query to delete a row in a table.)
In my opinion there are 2 possible reasons how this problem can be caused:
- bug in MSSQL2000 (1 db runs on mssql3, other db runs on mssql9)
- too thight QUERY_GOVERNOR_COST_LIMIT at jodohost
Anybody experience with this type of problems?
Thanks for any input,
Jan-Pieter
I run a couple of DotNetNuke websites for several customers. Recently my customers complain about exceptions.
In the logs I find sometimes MSSQL messages like
"System.Data.SqlClient.SqlException: The query has been canceled because the estimated cost of this query (289) exceeds the configured threshold of 60. Contact the system administrator."
I have contacted jodohost support through a ticket, and they say that they cannot (or do not want) to change the QUERY_GOVERNOR_COST_LIMIT variable in MSSQL to an higher value.
I understand that too high values will encourage bad sql-statements, but it looks to me that a value of 60 is not very high.
Also I have found out that the MSSQL estimation-process does not produce correct estimations in several circumstances. (And that they are based on SQL running on a 100MHz Pentium-1 machine.)
To be sure that the problems are not caused by DotNetNuke bugs I am currently upgrading some sites (from 4.5.1 to 4.5.5 which is the latest release). Bu during upgrade I run into blocking problems, all caused by cancelled "exceeds estimated cost" queries.
For example a very simple SQL statement that gets such an error:
delete
from dbo.Modules
where PortalID is null
and ModuleTitle = 'Solutions'
(A simple query to delete a row in a table.)
In my opinion there are 2 possible reasons how this problem can be caused:
- bug in MSSQL2000 (1 db runs on mssql3, other db runs on mssql9)
- too thight QUERY_GOVERNOR_COST_LIMIT at jodohost
Anybody experience with this type of problems?
Thanks for any input,
Jan-Pieter