April 23, 2010 at 9:27 am
Hi Everyone,
Below is a query that is running extremely slow in 2008. By slow I mean 15+ minutes. On SQL2000 it would run in a 3-5 seconds. On the 2000 server it ran a parallelism execution plan. I also forced it to use a serial plan and it finished just as quickly.
In 2008 the execution plan won't make use of parallelism and only has a serial plan, which for some reason is very slow. The only way to make it fast on 2008 is to comment out the "OR IS NULL" and then the execution plans switches to parallelism and completes in 1 second. As you can see in the attatchments SQL 2000 has a very different serial plan than 2008. Any ideas as to what is going on?
Thank You!
select count(t.AIID)
FROM
boc.POSKit p RIGHT OUTER JOIN
boc.Terminal t INNER JOIN boc.Merchant m ON t.WMerchantSeqNo = m.WMerchantSeqNo
LEFT OUTER JOIN boc.ASA ON t.TerminalSeqNo = boc.ASA.TerminalOrdinal ON p.AIID = t.AIID where t.TerminalSeqNo
IN (SELECT MAX(t2.TerminalSeqNo) FROM boc.Terminal t2 GROUP BY t2.AIID)
and (p.SequenceNum IN (SELECT MAX(p2.SequenceNum)FROM boc.POSKit p2 GROUP BY p2.AIID)
OR p.SequenceNum IS NULL)
and t.status IN (17, 18)
April 24, 2010 at 5:37 am
The simple answer is that the 2008 optimiser has a lot more options than in 2000. In this case, 2008 picks a poor plan which was not available in 2000. The reason for that is almost certainly down to a lack of good statistics or indexes - though it's only possible to say so much based on a picture.
A quick fix would be to add OPTION (HASH JOIN, MERGE JOIN) to the query.
A full explanation and suggestions for better indexing and so on would require a more detailed analysis.
Paul
April 24, 2010 at 7:33 am
It did not like that hint at all as shown per the error below. I have already done all the normal stuff such as running through the tuning engine and adding recommended indexes and stats. Rebuilding indexes and stats and so forth.
Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
select count(t.AIID)
FROM
boc.POSKit p RIGHT OUTER JOIN
boc.Terminal t INNER JOIN boc.Merchant m ON t.WMerchantSeqNo = m.WMerchantSeqNo
LEFT OUTER JOIN boc.ASA ON t.TerminalSeqNo = boc.ASA.TerminalOrdinal ON p.AIID = t.AIID where t.TerminalSeqNo
IN (SELECT MAX(t2.TerminalSeqNo) FROM boc.Terminal t2 GROUP BY t2.AIID)
and (p.SequenceNum IN (SELECT MAX(p2.SequenceNum)FROM boc.POSKit p2 GROUP BY p2.AIID)
OR p.SequenceNum IS NULL)
and t.status IN (17, 18)
OPTION (HASH JOIN, MERGE JOIN)
April 24, 2010 at 8:20 pm
Try:
SELECT COUNT(t.AIID)
FROM boc.POSKit p
RIGHT
JOIN boc.Terminal t
JOIN boc.Merchant m
ON t.WMerchantSeqNo = m.WMerchantSeqNo
LEFT
JOIN boc.ASA
ON t.TerminalSeqNo = boc.ASA.TerminalOrdinal
ON p.AIID = t.AIID
WHERE t.TerminalSeqNo IN
(
SELECT MAX(t2.TerminalSeqNo)
FROM boc.Terminal t2
GROUP BY
t2.AIID
)
AND 1 =
(
CASE
WHEN p.SequenceNum IN (SELECT MAX(p2.SequenceNum) FROM boc.POSKit p2 GROUP BY p2.AIID) THEN 1
WHEN p.SequenceNum IS NULL THEN 1
ELSE 0
END
)
AND t.status IN (17, 18)
Otherwise, you're going to have to post some code so we can build the tables and play with the plans directly.
An actual execution plan from 2008 would help too - just right-click on the graphical plan from an actual run and save it as a *.sqlplan file.
Paul
April 26, 2010 at 9:55 am
Thank you very much Paul!
That did the trick and kicked it over to a parallel execution plan which now runs in 1-2 seconds. I really appreciate the help!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply