I have been tuning a query/ indexes in a dev environment, but cannot replicate the new query plan when applying the same changes to a UAT environment.
Specifically, in the UAT environment the optimizer chooses to ignore a particular index and instead performs a nonclustered index seek on an existing unique constraint, followed by a key lookup on the clustered index.
An abridged version of the query is:
select d.dim_date_id , f.dim_form_id , d.date INTO #TMP from DWH.dbo.tbl_fact_outcome f join DWH.dbo.tbl_dim_date d ON d.date >= DATEADD(DAY,-1,f.known_from) and d.date < f.known_to join DWH.dbo.tbl_dim_form df ON f.dim_form_id = df.dim_form_id join DWH.dbo.tbl_dim_question Q ON f.dim_question_id = Q.dim_question_id where (d.flag_latest_day = 'Y' or d.flag_end_of_month = 'Y' or (d.flag_end_of_week = 'Y' AND d.flag_latest_week = 'Y')) and d.flag_future_day = 'N' and df.flag_latest_form = 'Y' and f.deleted = 0 and q.question_key like 'R%' and d.date >= '14/07/2020'
The index in question is:
CREATE NONCLUSTERED INDEX [ix_tbl_dim_date_flag_future_day_includes] ON [dbo].[tbl_dim_date] ([flag_future_day] ASC) INCLUDE([dim_date_id],[date],[flag_end_of_month],[flag_latest_day],[flag_end_of_week],[flag_latest_week])
It's not a large table and should only return one or two dates which filter results from subsequent joins accordingly. Adding other fields to key columns in index made no difference in the dev environment - the only improvement came from the order/ shape of the query plan.
- The dev query plan (good) is here : https://www.brentozar.com/pastetheplan/?id=SyZjIGA1v
- The UAT query plan (bad) is here : https://www.brentozar.com/pastetheplan/?id=BkBBLzCyv
Both environments have the exact same table structure and indexes and are using the same query. The dev environment does have around half the volume of data overall, but even if I amend the date range in either to return a similar number of records the query plans in both environments stay as they are. There was a dramatic improvement on performance in the dev environment since this query plan has been used and I am fairly confident the same would be seen in my UAT environment.
I have tried using query hints to force the index and/ or the order but haven't been able to replicate this plan.
Essentially I have two questions:
- Why would the optimizer choose a key lookup over this index?
- Is there anything I can do to force it to follow the shape seen in the dev environment?
I am using SQL Server 2014 enterprise edition.
EDIT - 20/07/2020 - Actual execution plans added
Actual good execution plan from dev environment - running this took 37 seconds: https://www.brentozar.com/pastetheplan/?id=BJkr-Q7gP
Actual bad execution plan from UAT - running this took 33m 42s: https://www.brentozar.com/pastetheplan/?id=H1nsxX7ew