In SQL2016, this SQL which has unions on 3 similar tables, optimizer chooses inefficient access path for one of the tables
The tables are Clustered column store INDEX with a an additional Primary key enforcing uniqueness.
Note the access path for EOY_2011 has a Key look up on CCI instead of a Index scan. In plan explorer this part of the SQL is the most expensive.
Now, if I add one more UNION with EIS_CLM_COST_EOY_2010, then the access path for 2011 changes to be in line with that for 2013 and 2012.
I have stats updated.
- For temporary relief you could consider adding those output columns needed from the key lookup to the include portion of the chosen index. Specifically, you may want to add IDXPROV_JCL_SUB_CD and JURSDCTN_TYPE_DESC to the key for the index CLM_INJ_LCM.xcinjclm11, which will make that index a much better choice for multiple seeks than the wider clustered index.
- You could also see if the estimates improve (your estimates on most operations are under by about 1/3) by removing DISTINCT and adding GROUP BY, or adding a window function to the subquery and filtering outside.