In SQL2016, this SQL which has unions on 3 similar tables, optimizer chooses inefficient access path for one of the tables

rambal 2017-02-03 18:31:14

EIS_CLM_COST_EOY_2011, EIS_CLM_COST_EOY_2012, EIS_CLM_COST_EOY_2013 are all end of year snapshots of year end claims totals

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.

Aaron Bertrand 2017-02-22 21:22:40
On first glance I'm not sure quite how to force SQL Server to look at the columnstore index instead of the rowstore. But a couple of other ideas:

  • 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.