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

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.

Plan.pesession (175.3 kB)
avatar image By rambal 0 asked Feb 03 at 06:31 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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.

avatar image By Aaron Bertrand ♦ 1.7k answered Feb 22 at 09:22 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: Feb 03 at 06:31 PM

Seen: 923 times

Last Updated: Feb 22 at 09:22 PM