Bad executed Plan and wrong Result by SQL

[Krisnamourt Correia via SQLMonster.com]

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_EVENT O 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_EVENTO S E,
SAM_GUIA G
WHERE G.PEG=736740
AND E.GUIA=coalesce (G.HANDLE,G.HAN DLE) AND E.CLASSEGERENCI ALPAGTO is NULL


--Normal Query
SELECT count(*)
FROM SAM_GUIA_EVENTO S E,
SAM_GUIA G
WHERE G.PEG=736740
AND E.GUIA=G.HANDLE AND E.CLASSEGERENCI ALPAGTO is NULL

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

[Krisnamourt Correia via SQLMonster.com]

Re: Bad executed Plan and wrong Result by SQL

StmtText
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--------------------------------------
--Normal Query
SELECT count(*)
FROM SAM_GUIA_EVENTO S E,
SAM_GUIA G
WHERE G.PEG=736740
AND E.GUIA=G.HANDLE AND E.CLASSEGERENCI ALPAGTO is NULL
option(merge join)

(1 row(s) affected)

StmtText
----------------------------------------------------------------------------
----------------------------------------------------------------------------
---------------------------------
|--Compute Scalar(DEFINE:([Expr1002]=Convert([globalagg1004])))
|--Stream Aggregate(DEFIN E:([globalagg1004]=SUM([partialagg1003])))
|--Parallelism(Gat her Streams)
|--Merge Join(Inner Join, MERGE:([G].[HANDLE])=([E].[GUIA])
, RESIDUAL:([G].[HANDLE]=[E].[GUIA]))
|--Parallelism(Dis tribute Streams, PARTITION COLUMNS:
([G].[HANDLE]))
| |--Index Seek(OBJECT:([Saude].[dbo].[SAM_GUIA].
[AX_1603PEG] AS [G]), SEEK:([G].[PEG]=736740) ORDERED FORWARD)
|--Sort(ORDER BY:([E].[GUIA] ASC))
|--Hash Match(Aggregate , HASH:([E].[GUIA]),
RESIDUAL:([E].[GUIA]=[E].[GUIA]) DEFINE:([partialagg1003]=COUNT(*)))
|--Parallelism(Rep artition Streams,
PARTITION COLUMNS:([E].[GUIA]))
|--Clustered Index Scan(OBJECT:([Saude]
..[dbo].[SAM_GUIA_EVENTO S].[PK__SAM_GUIA_EV ENTOS__68736660] AS [E]), WHERE:(
[E].[CLASSEGERENCIAL PAGTO]=NULL))

(10 row(s) affected)

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

[Krisnamourt Correia via SQLMonster.com]

Re: Bad executed Plan and wrong Result by SQL

I Mean...the wrong result bring back many row with E.CLASSEGERENCI ALPAGTO
not null(this column shows many data )....CRAZY!!!


Anyone help me to explain that!!

Kris

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

[Erland Sommarskog]

Re: Bad executed Plan and wrong Result by SQL

Krisnamourt Correia via SQLMonster.com (forum@nospam.S QLMonster.com) writes:[color=blue]
> 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_EVENT O 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.[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Krisnamourt Correia via SQLMonster.com]

Re: Bad executed Plan and wrong Result by SQL

The real problem is incorret result. I can´t rebuild index on these two
table , because our scenario works 24 hours by day. These table are too big
(17 Gbytes one and 4 Gbytes other), with many Index. The Index Rebuild only
can do at weekends. I intend to eliminated some Index that are redundant(I
just begun), but that bug is very crazy. That became SQL Server not a good
solution for OLTP that grows up strongly. I saw many scenarios like
that...bad performance when the Database became too large.

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

[Erland Sommarskog]

Re: Bad executed Plan and wrong Result by SQL

Krisnamourt Correia via SQLMonster.com (forum@SQLMonst er.com) writes:[color=blue]
> The real problem is incorret result. I can´t rebuild index on these two
> table , because our scenario works 24 hours by day. These table are too
> big (17 Gbytes one and 4 Gbytes other), with many Index. The Index
> Rebuild only can do at weekends. I intend to eliminated some Index that
> are redundant(I just begun), but that bug is very crazy. That became SQL
> Server not a good solution for OLTP that grows up strongly. I saw many
> scenarios like that...bad performance when the Database became too
> large.[/color]

Looking at your query, the incorrect results may be a known issue.
I think I recognize the type of query. I would suggest that you open
a case with Microsoft to investigate this.

If there is a fix, it is likely to be available in SP4 which was recently.
Unfortunate there is an issue which concerns AWE which I would expect to
concern you, given your table sizes. I would expect Microsoft to have a fix
for this issue soon, though. See further
http://www.microsoft.com/sql/downloads/2000/sp4.asp.

Note that SP4 is only likely to address the incorrect result. The query
plan and the fragmentation is less likely to improve.

Some questions:
o Do you have autostats enabled on these tables? (Maybe you should turn
them off)
o What actual fragmentation do you have by the middle of the week?

If the fragmentation increases rapidly, maybe you should look at changing
the clustered index to one that is less prone to fragmentation given
the update pattern. But here is of course a tradeoff with queries
that may depend on the clustered index.

Could you post the CREATE TABLE and CREATE INDEX statements for the
two tables?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp