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

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)

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.

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

  • Why does tbl_fact_outcome not have one row per date? That appears to be the root cause of the trouble. Jul 16, 2020 at 19:07
  • @DavidBrowne-Microsoft - the fact table contains known from and known to dates for each record. Any PK can only have one row relating to any given date. Dim date is joined to create snapshots eg month-end, week-end etc. If there are two snapshot dates in the date range queried, it’s possible to return one fact record twice (eg if week end and month end dates are both between the known from and known to dates in fact table)
    – JShark
    Jul 16, 2020 at 20:59
  • Is it possible to include the actual plan? Jul 19, 2020 at 8:32
  • @Learning_DBAdmin - I have updated my post with actual execution plans at the bottom.
    – JShark
    Jul 20, 2020 at 13:53

2 Answers 2


This was totally a workaround, but it worked for me. I had 4 servers running the same database with different data sets. Three of them would chose an optimal plan, but the fourth would never choose that plan, and was consistently taking significantly longer. In my case I could see the good plans seeking one of the tables for a handful of rows out of around 100,000 first, then doing the other joins. The bad plan would do that seek last and would seek that table 500,000 plus times. I also tried the query hints and re-writing the query in different ways with no luck.

So my work around was to manually query that table for those handful of rows first into a temp table, then join to that instead of the full table.

So in your case could you run something like this first into a temp table and then join against it instead of the whole DWH.dbo.tbl_dim_date table:

select  d.dim_date_id 
from    DWH.dbo.tbl_dim_date d 
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 d.date >= '14/07/2020'

Not sure in your case if the above query would be selective enough to be really helpful and I realize that it is a workaround. But sometimes workarounds work!

Another thought, are you able to modify that NCI you included? It might help if you added the date column to the indexed columns instead of the INCLUDE columns. There are a lot of filters done in that query based on that date column.

  • Thanks for this @john. I'm certainly not averse to a workaround if it does the trick! The script (minus the date filter) is actually a view so can't add a temp table directly in it, but could certainly look at trying that in a stored proc or standalone query. Will have a go next week. As for the index, I did try various combos with the date included as a key column but it didn't have much impact and didn't give me the query plan I'm after.
    – JShark
    Jul 17, 2020 at 8:20
  • @JShark Ah, that makes sense. maybe you could use a CTE to get the same behavior? stackoverflow.com/a/8256515/9996618 Though reading that more, the CTE might mimic the bad plan more than a temp table. Maybe worth testing?
    – John
    Jul 17, 2020 at 16:21
  • the temp table workaround does seem to result in the right query plan - thanks. CTE makes no difference, I guess as it doesn't actually materialise the output. I've marked your answer as useful, but not ticked it as solved - while I may be able to workaround using a stored proc or something, ideally I want to fix this so any queries on the view will run OK, and also I'd like to understand more about why the better plan is not being generated in UAT environment but it is in dev. Thanks for your good suggestions though!
    – JShark
    Jul 20, 2020 at 14:02

This is not a direct answer to your question however few observation which might put you in right direction to solve the issue. First few observations from actual plan:

  1. Number of rows in table tbl_dim_form is different. In good plan , number of rows are 6653269 whereas in bad plan it is 9387471. Which is close to 40% more.
  2. Same is the case with table tbl_fact_outcome, In good plan, number of rows are 28011736 whereas for bad plan, it is 65679017. This is more than double value.
  3. I didn't check other tables however it seems volume of data is totally different between dev and UAT environment.

You may read more on this link how number of rows could affect query plan and other joining conditions.

I downloaded the XML for good and bad plan. If you use SentryOne Plan explorer, below is the one which is highlighted in bad plan:

Bad Query Plan

Number of rows read is in billions and it is 3 times than that of actually required.

Good Query Plan

In the case of good plan, I/O residual is much lesser.

So, you need to solve this issue first and also point raised by David Browne on table tbl_fact_outcome. Below are some of the pages where it could help you:


"Warnings: Operation caused residual I/O" versus key lookups


I hope this helps you.

  • I acknowledged in my initial post that dev environment has about half the data. Both return a similar number of records and crucially the good query plan from dev is almost definitely more suitable in UAT too. Your first link is specifically about SQL engine treating top 100 and top 101 differently which doesn’t apply here. You’re right about the residual IO in the bad plan, but if the plan was the same shape as the good one that wouldn’t be the case. The best solution seems to be indexing... but I already have an index which it’s overlooking, which is my conundrum.
    – JShark
    Jul 21, 2020 at 21:38
  • Re Fact_outcome having known_from/ known_to instead of a single date... unfortunately I’m not able to amend the table structure in this case... although the fact it runs so much better with the ‘good’ plan in dev environment implies it absolutely can run (more than) good enough in UAT with the existing data structure.
    – JShark
    Jul 21, 2020 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.