Posted by Erland Sommarskog on 05/17/05 01:15

Krisnamourt Correia via SQLMonster.com (forum@nospam.SQLMonster.com) writes:
> I have one query that executes many times in a week.
> I created one Maintenances plan that Rebuild all index in my Database that
> has been executed at 23:40 Saturday until stop finished at Sunday.
>
> However at middle of week (Wednesday or Thursday), that query don't
> return result like that must be. The time exceeded and the result are
> total wrong.
>
> I compare the normal executed plan and the "crazy" one that SQL create to
> mount result.
>
> The normal is nested with index seek (very fast, the wrong is Merger
> with hash aggregate (very slow). After Index Rebuild, the executed plan
> bring result that must be, but when the merge plan are executed with
> many updates on that tables (SAM_GUIA_EVENTO and SAM_GUIA), at middle of
> week, the result are total wrong, with many rows back.
>
> I recommended Index Seek force by coalesce function on one column
> aggregate, but everyone here were very panic with that behavior of SQL
> Server.

Do I understand you clarifiation in the other article correctly, that
when you say "results are total wrong", you do in fact mean the query
plan? If you really get incorrect resuls from the query, this is a
serious bug, and you should definitely open a case with Microsoft to
have it investigate.

If the problem is "only" the incorrect query plan, and the slow execution
time, this is more "normal" behaviour.

Recall that SQL Server uses a cost-based optimizer that estimates the
cost of various query plans from statistics about the data. A small
error in the estimate can have serious consequences.

Since you have good performance after index rebuild, it might be a good
idea to schedule index rebuild on these two tables daily.

I also notice that the bad plan involves parallelism. If you add
OPTION (MAXDOP 1), you tell SQL Server not to use parallelism. This
is often enough to get a good plan.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]