SQL 2000 VS. 2008 query using parralelism

Hi Everyone,
   Can someone clearly explain why the query below acts the way it does after migrating from sql 2000 to sql 2008. The first query doesn't make sure of parallelism and runs extremely slow. The second version with the "IS NULL" statement commented out does use parallelism and returns results instantly.

Thank You

Query 1: Very Slow on 2008 vs 2000 because parallelism is not being used.

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)

Query 2: Very fast because the comment of IS NULL kicks in the use of parallelism, but why?

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)
eric121899Asked:
Who is Participating?
 
eric121899Author Commented:
Found a solution that works. Had to use a case statement and then a quick parallel execution plan kicked in!

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)
0
 
gothamiteCommented:
Have you rebuilt all applicable indexes since moving to SQL 2008?
0
 
eric121899Author Commented:
Yes indexes have been rebuilt along with stats and so forth. I believe it has to do with the following below but I don't quite understand it. Not sure which rule below that appears to be new for 2005/2008 is effecting the query.

http://msdn.microsoft.com/en-us/library/ms178065.aspx

#  The serial execution cost of the query is not high enough to consider an alternative, parallel execution plan.
# A serial execution plan is considered faster than any possible parallel execution plan for the particular query.
# The query contains scalar or relational operators that cannot be run in parallel. Certain operators can cause a section of the query plan to run in serial mode, or the whole plan to run in serial mode.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
cyberkiwiCommented:
In SQL Server indexes, NULLs are never stored.  So if you use IS NULL, SQL Server has to scan the entire table to find the matches and stops using indexes.
It can use other indexes if you have other conditions of course, but depending on the particular plan, it may choose to perform this hash merge/join first and therefore scans the entire table.

You may need some brackets around your AND.. OR .. AND to properly show your intension
Also, you can use index hints to force the execution plan for complex queries that SQL Server optimizer gets wrong
0
 
gothamiteCommented:
Can you post the execution plan ?
0
 
eric121899Author Commented:
I can export it, or run the text version of it if you want. What version would you like?

Thanks!
0
 
gothamiteCommented:
Enable 'include actual execution plan' then right-click on the plan and save as a .sqlplan file i.e. XML
0
 
eric121899Author Commented:
File has been attached. I don't believe there is a way to export the plan from sql 2000. The attached plan is the one from 2008.

Thank You
0
 
eric121899Author Commented:
it wouldn't let me upload that extension. Attached is the XML inside a word xml file.

Thanks
executionplan.xml
0
 
cyberkiwiCommented:
Give this a try, make it go to the table first

select count(t.AIID)
FROM      
boc.Terminal t INNER JOIN  boc.Merchant m ON t.WMerchantSeqNo = m.WMerchantSeqNo
LEFT OUTER JOIN boc.POSKit p ON p.AIID = t.AIID
LEFT OUTER JOIN  boc.ASA ON t.TerminalSeqNo = boc.ASA.TerminalOrdinal
where t.TerminalSeqNo IN (SELECT MAX(t2.TerminalSeqNo) FROM boc.Terminal t2 GROUP BY t2.AIID)
and t.status IN (17, 18)
and (p.SequenceNum IN       (SELECT MAX(p2.SequenceNum)FROM boc.POSKit p2 GROUP BY p2.AIID)
OR p.SequenceNum IS NULL)
option (force order)

The IS NULL is driving SQL Server down POSKit first simply because you put it there first.
0
 
eric121899Author Commented:
Another interesting fact. I forced the sql 2000 server to not use parallelism and saw that it now switched to a serial execution plan. But it ran just as fast without parallelism. So that kind of changes things a bit since SQL 2000 now runs the query the same speed with or without parallelism....but SQL 2008 will not run fast unless I get rid of that IS NULL and which kicks over to a parallelism plan

Attached is the screen shot from SQL 2000 using a serial execution and runs very fast.
execution2000.jpg
0
 
eric121899Author Commented:
cyberkiwi:
   I tried that and got the same results. Thanks for the help though!
0
 
cyberkiwiCommented:
Try this as well - parallellism is not good here

select count(t.AIID)
FROM      
boc.Terminal t INNER JOIN  boc.Merchant m ON t.WMerchantSeqNo = m.WMerchantSeqNo
LEFT OUTER JOIN boc.POSKit p ON p.AIID = t.AIID
LEFT OUTER JOIN  boc.ASA ON t.TerminalSeqNo = boc.ASA.TerminalOrdinal
where t.TerminalSeqNo IN (SELECT MAX(t2.TerminalSeqNo) FROM boc.Terminal t2 GROUP BY t2.AIID)
and t.status IN (17, 18)
and (p.SequenceNum IN       (SELECT MAX(p2.SequenceNum)FROM boc.POSKit p2 GROUP BY p2.AIID)
OR p.SequenceNum IS NULL)
option (force order, maxdop 1)
0
 
eric121899Author Commented:
That doesn't work either. That just turns off parallelism which isn't working in the first place. I also attached a picture of the sql2008 execution plan so you can now visually see the differences between the two versions
sql2008-execution.jpg
0
 
cyberkiwiCommented:
select count(t.AIID)
FROM      
boc.Terminal t INNER JOIN  boc.Merchant m ON t.WMerchantSeqNo = m.WMerchantSeqNo
LEFT OUTER JOIN boc.POSKit p with (index(MTT_ID***)) ON p.AIID = t.AIID
LEFT OUTER JOIN  boc.ASA ON t.TerminalSeqNo = boc.ASA.TerminalOrdinal
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 (force order)

Can you please list the indexes for Terminal, that seems to be the key.
0
 
eric121899Author Commented:
It didn't like your with index syntax or index name. The index for terminal are:

1. TerminalSeqNo  - Ascending Integer

2. Status - Ascending integer

3. AIID - Ascending bigint

4. WMerchantSeqNo - Ascending int

5. Status - Ascending BI IXNC Terminal Status
0
 
cyberkiwiCommented:
I meant the index names to replace into the MTT_ID***. From the images, I think it starts with MTT_ID
The index should be against the Terminal table as well so my apologies

select count(t.AIID)
FROM      
boc.Terminal t with (index(MTT_ID***))
INNER JOIN  boc.Merchant m ON t.WMerchantSeqNo = m.WMerchantSeqNo
LEFT OUTER JOIN  boc.ASA ON t.TerminalSeqNo = boc.ASA.TerminalOrdinal
LEFT OUTER JOIN boc.POSKit p 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 (force order)
0
 
eric121899Author Commented:
Ah sorry my mistake. The index name was MTT_IDIndex which I put in and have the same results. The issue seems to be at the bottom with the 57% and 43% costs when dealing with the POSKit table.
0
 
cyberkiwiCommented:
Is the 2008 db one with full dataset and the 2000 db only a test db with no data to compare plans?
Execution plans do change with data volume..
0
 
eric121899Author Commented:
They both have very similar data sets, maybe off by 1k rows or less.
0
 
cyberkiwiCommented:
Call me crazy but SQL 2000 plan shows that Merchant table is not being considered.  It also shows a 2nd index in use.
It goes Terminal, Terminal, ASA, PosKit - so I believe the right join inner join order has a different meaning.
The result set cannot be the same in 2000 and 2008 - unless by luck all t's have matching m's.
Too bad you cannot bracket the joins like you can with maths (  (1+2) * 3 )

select count(t.AIID)
FROM      
boc.Terminal t with (index(MTT_IDIndex , Terminal***))
INNER JOIN  boc.Merchant m ON t.WMerchantSeqNo = m.WMerchantSeqNo
LEFT OUTER JOIN  boc.ASA ON t.TerminalSeqNo = boc.ASA.TerminalOrdinal
LEFT OUTER JOIN boc.POSKit p 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 (force order)
0
 
eric121899Author Commented:
Interesting point. You might be onto something there. I wonder if a Full join could  be used instead. That query also led to the same results. It has to do with that IS NULL causing a full scan it seems.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.