FORCEPLAN causes different results in SQL SERVER 2000

Hello,
Has anyone run into a situation where using SET FORCEPLAN ON changes
the results set of a stored procedure? I've got a large, complicated
procedure that spits out a large hierarchical table of aggregated
values. I noticed that some of the data was coming out incorrectly,
even though the components seem to be correct. In the process of
troubleshooting I tried setting FORCEPLAN to ON, and found that the
procedure started spitting out the correct data.
Unfortunately due to performance issues we can't just leave FORCEPLAN
on, so I need to get to thr root of this problem. I'm also curious as
to why this would happen in the first place. Does anyone have any
ideas? My understanding of FORCEPLAN is that it changes the order of
joins in a query, changing the performance. But it shouldn't change
the data itself, correct?
On a hunch I tried reindexing, thinking that maybe some bad info was
cached, but no luck.
Does anyone know of any instances where FORCEPLAN would change the
results set of a query?
Thanks,
Chris RutledgeWhat is the actual query?
"Chris Rutledge" <csrutledge@.gmail.com> wrote in message
news:1138826752.664351.310480@.g43g2000cwa.googlegroups.com...
> Hello,
> Has anyone run into a situation where using SET FORCEPLAN ON changes
> the results set of a stored procedure? I've got a large, complicated
> procedure that spits out a large hierarchical table of aggregated
> values. I noticed that some of the data was coming out incorrectly,
> even though the components seem to be correct. In the process of
> troubleshooting I tried setting FORCEPLAN to ON, and found that the
> procedure started spitting out the correct data.
> Unfortunately due to performance issues we can't just leave FORCEPLAN
> on, so I need to get to thr root of this problem. I'm also curious as
> to why this would happen in the first place. Does anyone have any
> ideas? My understanding of FORCEPLAN is that it changes the order of
> joins in a query, changing the performance. But it shouldn't change
> the data itself, correct?
> On a hunch I tried reindexing, thinking that maybe some bad info was
> cached, but no luck.
> Does anyone know of any instances where FORCEPLAN would change the
> results set of a query?
> Thanks,
> Chris Rutledge
>|||It's pretty enormous, but I think the pertinent bit is in here:
SELECT SUM(Event0.Qty * EventCmpt.AggSign ) AS SQ ,
NodeSku.ProductStream, NodeSku.ProductDescription, NodeSku.PrdName,
Event0.TimePeriod - @.MaxRel AS TimePeriod
-- Node Table Joins
FROM ( SELECT EventId, TimePeriod, NodeId, EventTypeId, Qty,
TP.PlanId, GroupId, TransId FROM Event
INNER JOIN @.TableEventTimePeriod AS TP
ON TP.EventTimePeriod=TimePeriod
AND TP.PlanId=Event.PlanId
AND IsValidFlag=1 ) AS Event0
INNER JOIN NODESKU
ON Event0.NodeId=NODESKU.NodeId
JOIN ( SELECT EventCmpt, AggSign FROM EventCmpt
WHERE EventId=@.ElementId UNION SELECT @.ElementId, 1 AS
AggSign )
EventCmpt ON Event0.EventTypeId=EventCmpt
GROUP BY Event0.TimePeriod, NodeSku.ProductStream,
NodeSku.ProductDescription, NodeSku.PrdName
WITH ROLLUP
-- multi-component grouping.
HAVING GROUPING (NodeSku.ProductDescription)=GROUPING(NodeSku.PrdName)
AND
GROUPING(Event0.TimePeriod)=0
AND (NodeSku.ProductStream IS NOT NULL OR
GROUPING(NodeSku.ProductStream ) = 1 )
AND (NodeSku.ProductDescription IS NOT NULL OR
GROUPING(NodeSku.ProductDescription ) = 1 )
AND (NodeSku.PrdName IS NOT NULL OR GROUPING(NodeSku.PrdName ) = 1
)
) B
INNER JOIN PlanPeriod ON B.TimePeriod <= PlanPeriod.PeriodId
AND PlanPeriod.CalendarId = ( SELECT CalendarId FROM Plans WHERE
PlanId = @.PlanId )
AND PlanPeriod.PeriodId BETWEEN @.SubRangeMin AND @.SubRangeMax
GROUP BY B.ProductStream, B.ProductDescription, B.PrdName,
PlanPeriod.PeriodId
ORDER BY B.ProductStream, B.ProductDescription, B.PrdName
END
ELSE ...|||I can not say why is this happening without looking at the sample code and
sample data to reproduce the problem in my environment. However, it appears
to me that you may be using ANSI 89 standard in your SQL. Try changing that
to ANSI 92 and see what output are your seeing.
"Chris Rutledge" wrote:

> Hello,
> Has anyone run into a situation where using SET FORCEPLAN ON changes
> the results set of a stored procedure? I've got a large, complicated
> procedure that spits out a large hierarchical table of aggregated
> values. I noticed that some of the data was coming out incorrectly,
> even though the components seem to be correct. In the process of
> troubleshooting I tried setting FORCEPLAN to ON, and found that the
> procedure started spitting out the correct data.
> Unfortunately due to performance issues we can't just leave FORCEPLAN
> on, so I need to get to thr root of this problem. I'm also curious as
> to why this would happen in the first place. Does anyone have any
> ideas? My understanding of FORCEPLAN is that it changes the order of
> joins in a query, changing the performance. But it shouldn't change
> the data itself, correct?
> On a hunch I tried reindexing, thinking that maybe some bad info was
> cached, but no luck.
> Does anyone know of any instances where FORCEPLAN would change the
> results set of a query?
> Thanks,
> Chris Rutledge
>|||Thanks for the reply, Nitin. Which elements appear to be from ANSI 89?
I'm not very familiar with the differences.|||This might be the case if you have the old style outer join in your
query (for example WHERE MyCol *= OtherCol). For an old style inner join
it shouldn't matter. (for example SELECT ... FROM A, B WHERE A.id=B.id)
However, the snippet you posted does not contain such syntax.
Now, the bottom line is that it is a bug. Because with SQL you specify
the result, and this result should be the same (i.e. correct) regardless
of the order in which the steps are executed.
Unfortunately, to analyse the problem, the entire query and execution
plan is necessary, and probably also the DDL and maybe even (some?)
data.
Gert-Jan
Chris Rutledge wrote:
> Thanks for the reply, Nitin. Which elements appear to be from ANSI 89?
> I'm not very familiar with the differences.|||I've had problems where certain indexes have been corrupt, and because
SET FORCEPLAN can cause different indexes to be used, this may explain
why you're getting different resuts.
Are you able to identify the different indexes being used in the two
scenarios and try recreating them?|||Hi folks,
I've done some more experimentation with your guidance and I think I'm
narrowing in on the problem. I tried adding some where statements to
the sp to limit the data i'm getting back. With the new, smaller
dataset it's returning the correct values.
I did a side-by-side comparison of the two versions of the stored
procedure using Beyond Compare. You can take a look yourself here:
http://www.wamsystemsweb.com/SQL/DiffReport.html
(note, the filtered version of the sp is waaaay off to the right)
The differences between the two are highlighted in red.
The filter itself is from lines 357 to 364.
I believe the relevant bits of the execution plan are from 383 to 410
or 504 to 520.
Based on the feedback I've been getting I located the indexes that were
being used by the unfiltered version that weren't being used by the
filtered version. I dropped and recreated those indexes, but I had no
luck.
Now I'm looking at the details of the execution plans for the two. The
filtered version (the one that works) uses more nested loops, while the
unfiltered version is using a lot of hash matches and merge joins.
Thanks much for all your feedback, folks. Any additional hints are
greatly appreciated.
Chris|||Even more to the point, except for the SET FORCEPLAN ON statement early
on, these two stored procedures are identical. Their execution plans
appear to be identical. The sp on the left generates the correct data,
the one on the right does not:
http://www.wamsystemsweb.com/SQL/DiffReport2.html
Chris|||Hmm, never mind. What I actually illustrated in the two above examples
is SHOWPLAN_TEXT doesn't work the way I think it does.