Covering Index isn't being used and I don't know why

Question

We have this complicated query that I'm trying to make "better" until we move it to pull from a data warehouse. I need a solution that's "good enough" for now and I think I'm about 2-3 indexes away from making that happen. I'm stuck on this part, however.

I'm specifically targeting this part of my Execution Plan:

That table originally had only 2 indexes: the Clustered PK index (that this shows it doing the Key Lookup on) and another FK index on a column not referenced here. Given that this heavy query always needs these add'l columns (DateForValue [datetime], CurveValue [float], BTUFactor [float], and FuelShrink [float]), I thought a covering index was the obvious solution here to remove the (slow) Key Lookup being performed here. So I added the following covering index:

CREATE NONCLUSTERED INDEX [IxFK_TEST_tblPriceRequestCurveValues_ForQuoteViewing] ON [dbo].[tblPriceRequestCurveValues] 
(
    [DateForValue] ASC,
    [CurveValue] ASC,
    [BTUFactor] ASC,
    [FuelShrink] ASC
)
INCLUDE(oid)

However, even after adding this index, it seems the query is still doing the Key Lookup.

Am I missing something obvious here or is this the right idea and I just have a problem elsewhere? Note that all statistics and indexes have been refreshed and this isn't THAT highly dynamic of a table but it is approaching ~1M records.

A simplified version of this query, focusing on this table of interest, is as follows. Nothing I removed references the PrimaryTableOfInterest.

SELECT * FROM
(query1)
UNION ALL
(
SELECT
    Other table columns,
    PrimaryTableOfInterestForNow.oid, 
    PrimaryTableOfInterestForNow.PriceRequestCurveID, 
    PrimaryTableOfInterestForNow.DateForValue, 
    PrimaryTableOfInterestForNow.CurveValue, 
    PrimaryTableOfInterestForNow.BTUFactor, 
    PrimaryTableOfInterestForNow.FuelShrink
FROM
    (
        SELECT
            Other table columns,
            tblPriceRequestCurves.PriceRequestCurveID AS MyForeignKey
        FROM
            tblPriceRequestCurves
            INNER JOIN other stuff unrelated to PrimaryTableOfInterestForNow
        WHERE
            tblPriceRequestCurves.SomeID IS NOT NULL
    )
    INNER JOIN
        tblPriceRequestCurveValues AS PrimaryTableOfInterestForNow
            ON MyForeignKey = PrimaryTableOfInterestForNow.PriceRequestCurveID
WHERE
    tblPriceRequestCurves.SomeID = SomeOtherID
)
UNION ALL
(query3)
ORDER BY xxxx

I'm working with each of the 3 portions of the UNION ALL independent of one another and the other two parts are nice and speedy and executing this third of the unions either by itself or in the union performs similarly (i.e. ~30 seconds). So the UNION isn't a factor but I included it just for thoroughness sake.

Answer 1

Edit: Following partial posting of query.

The query hasn't flipped to seek on the suggested NCI because of:

WHERE
    tblPriceRequestCurves.SomeID = SomeOtherID

Whatever SomeId is, it needs to feature in the index. Again, difficult to tell due to the obfuscation but probably as the sole indexed value, everything else INCLUDE.

Can't deduce from the query why your doing it this way but the repeated joining to tblPriceRequestCurves doesn't make much sense as it stands. Back to my earlier point, its all guesswork without the full picture.

Original:

The seek is on [oid], you've included this rather than make it part of the index. Can't be sure this is optimal for the entire query without seeing the query and execution plan but to turn this particular lookup into the non-clustered index seek you're looking for:

CREATE NONCLUSTERED INDEX [IxFK_TEST_tblPriceRequestCurveValues_ForQuoteViewing] 
ON [dbo].[tblPriceRequestCurveValues] ([oid] ASC)
INCLUDE ([DateForValue], [CurveValue], [BTUFactor], [FuelShrink])

Of course, this may be a really bad idea. All we've achieved is swapping a clustered index seek with a non-clustered index seek. Without seeing the full query, its impossible to say whether its the "right" thing to do.

My initial thought is that a clustered index seek (key lookup) for 500 rows in what appears to be an analytic type query is likely to be reasonable. You refer to this lookup as being slow but in what context? Is it just the highest estimated cost operator in the plan?

If ultimately this query is looking at data across a timespan, it may be that DateForValue would be a better choice as the leading key:

CREATE NONCLUSTERED INDEX [IxFK_TEST_tblPriceRequestCurveValues_ForQuoteViewing] 
    ON [dbo].[tblPriceRequestCurveValues] ([DateForValue] ASC, [oid] ASC)
    INCLUDE ([CurveValue], [BTUFactor], [FuelShrink])

If you're always looking at data for a single [oid], perhaps they need to flip to [oid] ASC, [DateForValue] ASC.

This is optimisation through the eye of a needle. Query optimisation usually needs to assess the query and execution plan as a whole.