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
“[FONT=“Courier New”]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.” [/FONT]
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:
[FONT=“Courier New”]delete
from dbo.Modules
where PortalID is null
and ModuleTitle = ‘Solutions’ [/FONT]
(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)
I just missed this one, 60 is 60 seconds, and in most cases it is quit accurate. We have upped it to 90 on a number of servers but 289 is quite a high cost estimate. Are there a number of columns or other info that would cause such a query to take as long as the estimate says?
Yes, but not allways. There are lots of discussions about this. One of the discussions is that the estimates are based on seconds on a very old machine. Nowadays most SQL server admins set the value to 3000. (And not any query takes longer than a minute on those platforms)
There are only 10 or less columns here… I don’t see any reason…
Does the table have a primary key?
I’m hardly a MS SQL Server expert when it comes to execution plans, but I believe the lack of a primary key (having a clustered index) would result in a table scan.
Still, the name of the table doesn’t suggest a very complex table structure or a large amount of records. A table scan on such a table shouldn’t cause a problem.
In case I’m underestimating the number of records, I assume there is an index on at least PortalID, it being a foreign key?
We had the very same problem when trying to remove modules/pages in DNN on several sites after the query governor was put in place (many, many months ago). The problem is with the DNN search tables. ALL (and I mean ALL) of the words of every (searchable) module gets indexed in the search_word table in DNN. You’ll have to CLEAR that table and move up the table chain to clear out all the search tables before you can remove the module/page.
The reason the query goes over the limit is because those search tables in DNN get so full of indexed words that the storedproc to remove the module has a foriegn key index to the search_word table which forces matching records in that table to be deleted based on module_id. That costs a HUGE amount (because of a table scan on that table), and causes the query governor to trip. You can see it if you run an execution plan on the query in your original post, and look down where it starts to remove records from search_word. You’ll see a huge table scan there.
The good news is if you just delete all the rows in all the search tables, then it will clear the governor, and then you are free to delete any modules you want (because there are not rows for that massive delete statement in the storedproc). Plus, if you require the search on your site, you can goto the DNN scheduler and run the search provider to go back and re-index all the pages after you get your modules deleted.