Posted by Krisnamourt Correia via SQLMonster.com on 05/16/05 21:31

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.

Please , anyone help me to explain that!

Krisnamourt!

P.S: Attachments :

--Force Index Query with coalesce
SELECT count(*)
FROM SAM_GUIA_EVENTOS E,
SAM_GUIA G
WHERE G.PEG=736740
AND E.GUIA=coalesce(G.HANDLE,G.HANDLE) AND E.CLASSEGERENCIALPAGTO is NULL


--Normal Query
SELECT count(*)
FROM SAM_GUIA_EVENTOS E,
SAM_GUIA G
WHERE G.PEG=736740
AND E.GUIA=G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL

--
Message posted via http://www.sqlmonster.com

• Next in forum: Re: Bad executed Plan and wrong Result by SQL
• Prev in forum: NOT FOR REPLICATION, Enterprise manager
• Thread view: Bad executed Plan and wrong Result by SQL

[Reply to this message]