Why Is This Query Plan So Much Different Than Previous Query Plan

DBNewbie_2007 2016-11-30 17:10:44

[link text][1]I have two queries… "Inner_DivisionOnly" and this one, "ProblemArea". The only difference between the two queries is the inner select on Schema1.Object7. The "faster" query looks like this:

SELECT DISTINCT Column24
FROM Schema1.Object7
WHERE Column25 = ?

and the slower query ("ProblemArea") has this:

SELECT DISTINCT Column24
FROM Schema1.Object7
WHERE Column25 = ?
AND Column26 = ?

Also, the "Inner_DivisionOnly" has a predicate on Object11, while "ProblemArea" does not, but I do not see how Object11 is affected by Object7 and why SQL Server would make such drastic differences in the plans. (This is repeatable on both SQL Server 2008 and 2014).

/storage/attachments/1801-ProblemArea_Anonymized.pesession
/storage/attachments/1803-inner-divisiononly-anonymized.pesession
DBNewbie_2007 2016-11-30 19:41:49
OK… still playing with this. The difference between the two queries appears to be the logical join type.

• "Inner_DivisionOnly" is using a HASH MATCH.

• "ProblemArea" is using a NESTED LOOP, which is causing the Object11 to return the huge number of records.

In the "ProblemArea" query:

• Removing the following in the final WHERE clause caused the query to use a HASH MATCH.

    AND Object10.Column27 IN (
    SELECT DISTINCT
            Column24
    FROM    Schema1.Object7
    WHERE   Column25 = ?
        AND Column29 = ?)

• Adding a new non-clustered index also helped resolve the NESTED LOOP.

November 30, 2016
No Comments