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.
By rambal 0 asked Feb 03 at 06:31 PM
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:
By Aaron Bertrand ♦ 1.7k answered Feb 22 at 09:22 PM