March 19, 2013 at 11:42 am
I have a query that has a very costly Key Lookup step in its execution plan. It looks like this:
SELECT B.BenefitID,
PW.WaiverReason,
EW.DateWaived,
EW.DateEntered,
CBA.OrderID
FROM EmployeeWaivers EW
JOINCompanyBenefitAssoc CBA ON
CBA.BenefitID = EW.BenefitID AND
CBA.CompanyID = YYYY
LEFT JOINPlanWaivers PW ON PW.WaiverID = EW.WaiverID
LEFT JOINBenefits B ON B.BenefitID = EW.BenefitID
WHEREEW.EmployeeID = XXXX AND
EW.Overwritten = 0
ORDER BY CBA.OrderID, PW.WaiverReason, EW.DateEntered
The Key Lookup is on the EmployeeWaivers table. I created the following covering index:
CREATE NONCLUSTERED INDEX [IX_EmployeeWaivers_BenefitID_WaiverID_EmployeeID_Overwritten_incl_DateWaived_DateEntered] ON [dbo].[EmployeeWaivers]
(
[BenefitID] ASC,
[WaiverID] ASC,
[EmployeeID] ASC,
[Overwritten] ASC
)
INCLUDE ( [DateWaived],
[DateEntered])
I still have the Key Lookup as part of the execution plan. What did I miss?
March 19, 2013 at 11:47 am
First request please post the code for this function: dbo.GetBenefitName.
March 19, 2013 at 11:49 am
Does it matter if that UDF doesn't touch the table dealing with the Key Lookup?
March 19, 2013 at 11:50 am
Second request, please post the actual execution plan of the query. Save it as a .SQLPLAN file and post it as an attachment to a post on this thread.
March 19, 2013 at 11:53 am
Done
March 19, 2013 at 12:16 pm
The covering index you listed there is not being used, because it's not efficient. The query filters on EW.EmployeeID AND EW.Overwritten, while those are in the index, they're not the left-based subset and hence the only way SQL could use the index you listed is with an index scan, not very efficient. As a result, SQL chooses to use an index it can seek on, accepting the cost of the lookup as less than the cost of scanning the covering index
Widen the [IX_EmployeeWaivers_EmployeeID] index, add Overwritten to the key and BenefitID, WaiverID, DateWaived, DateEntered as include columns if you want a covering index.
The only queries that can use the covering index you posted are ones that filter on any of these:
BenefitID
BenefitID and WaiverID
BenefitID and WaiverID and EmployeeID
BenefitID and WaiverID and EmployeeID and Overwritten
That's filter, not join. That table is the outer table in the joins, so the indexes on the join columns can't filter the resultset.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 19, 2013 at 12:29 pm
Ok, thanks, do I have this right? If you want to use an index to assist in a join, it should be on the joining table, not the base table?
March 19, 2013 at 12:58 pm
Typically the order of keys in teh index should be where clause first, joins second. If you have them that way round, it's going to be useful no matter whether the table is picked as inner or outer table, if you have them the other way around (as you currently do), the index is only really useful if the table is the inner table of the join. You can't specify which table is inner or outer, that's up to the optimiser based on indexes and data distribution and volume.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 19, 2013 at 1:04 pm
Ok, thanks for the clarification.
March 19, 2013 at 1:57 pm
TheGreenShepherd (3/19/2013)
Does it matter if that UDF doesn't touch the table dealing with the Key Lookup?
Maybe not, but good chance it could be rewritten as an inline table valued function to improve performance.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply