Why Is This Query Plan So Much Different Than Previous Query Plan
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).
• "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.