Key Lookup to Index Seek?
-
Hello All,
I am trying to educate myself a little more about Index usage in regards to performance tuning. I have one Stored Procedure on an Instance used for reporting that is taking longer and longer to complete. I took the source SQL from the STP and ran the Estimated Execution Plan and for one of the WHERE clauses, it shows a Key Lookup using 94% of the Operator Cost and no matter what type of Index I throw at it, it continues to use PK Clustered Index. It was 38%, but I removed all the columns in the SELECT statement except the Date field in question.The weird thing is the WHERE clause uses a Date Field and performs a Datediff to ensure none of the dates are more than 48 months old...I've created an Index with both the Date and the PK fields and included other fields in the SELECT but to no avail. Predicate is the Date function (datediff) and the seek Predicate is the PK I was referring to. Do I need to create another Index just on the PK field and include the Date field...is that my problem?
I apologize in advance if this is too elementary of a question...ive tried many different ways and just can't seem to get it to stop using the PK index and Key Lookup.
- chris_barnhart - Wednesday, February 22, 2017 12:04 PM
What is the actual predicate? You said there is a DATEDIFF but is the column in the function call? That would lead to the type of lookups you are referring to. If you can post the where predicates perhaps we can help explain what you are seeing and how you can fix it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Below is the predicate...Ive created an index base on this column, then included all fields within the Output List (20 total) and no good. Tried making an index with the Date column and PK value in the Seek Predicate with all the fields in the Output List, but again no good.
Predicate
datediff(month,[DBNAME].[dbo].[Orders].[ServiceFromDate] as [Claim].[ServiceFromDate],getdate())<=(48)Seek Predicate
Seek Keys[1]: Prefix: [DBNAME].[dbo].[Orders].OrderID = Scalar Operator([DBNAME].[dbo].[Orders].[OrderID] as [Claim].[OrderID])
- chris_barnhart - Wednesday, February 22, 2017 12:16 PMBelow is the predicate...Ive created an index base on this column, then included all fields within the Output List (20 total) and no good. Tried making an index with the Date column and PK value in the Seek Predicate with all the fields in the Output List, but again no good.
Predicate
datediff(month,[DBNAME].[dbo].[Orders].[ServiceFromDate] as [Claim].[ServiceFromDate],getdate())<=(48)Seek Predicate
Seek Keys[1]: Prefix: [DBNAME].[dbo].[Orders].OrderID = Scalar Operator([DBNAME].[dbo].[Orders].[OrderID] as [Claim].[OrderID])Yeah much as I suspected. This is nonSARGable because you stuck a column inside a function. This means that the engine has to calculate the result for every single row to determine if it meets the criteria. No amount of indexing is going to help there. All is not however, you just need to turn your logic around a little bit. If I can guess the requirement here is you want rows where the ServiceFromDate is within the last 4 years?
Try this and see if it doesn't help with the performance.
[DBNAME].[dbo].[Orders].[ServiceFromDate] > dateadd(month, -48, getdate()) as ServiceFromDate_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
OK, I'm still experimenting and trying things and I finally got it to use my new Index...but does an index scan versus a seek...Not sure how to approach that. In order to get it to use the new index, I removed the following query hint "option(maxdop 2, fast 5000)" and it seems the FAST 5000 portion was the piece that causes it to use the PK index versus the one I created. Now I have a new predicate and looks to mention the PK value (OrderID). My current experimental index has both the Date and PK field, then all the fields in the Output List except the PK---was thinking of removing the PK from the main portion of the index and add to the Include fields instead.
New Predicate
datediff(month,[DBNAME].[dbo].[Orders].[ServiceFromDate] as [Claim].[ServiceFromDate],getdate())<=(48) AND PROBE([Bitmap1103],[DBNAME].[dbo].[Orders].[OrderID] as [Claim].[OrderID],N'[IN ROW]') - Sean Lange - Wednesday, February 22, 2017 12:30 PMchris_barnhart - Wednesday, February 22, 2017 12:16 PMBelow is the predicate...Ive created an index base on this column, then included all fields within the Output List (20 total) and no good. Tried making an index with the Date column and PK value in the Seek Predicate with all the fields in the Output List, but again no good.
Predicate
datediff(month,[DBNAME].[dbo].[Orders].[ServiceFromDate] as [Claim].[ServiceFromDate],getdate())<=(48)Seek Predicate
Seek Keys[1]: Prefix: [DBNAME].[dbo].[Orders].OrderID = Scalar Operator([DBNAME].[dbo].[Orders].[OrderID] as [Claim].[OrderID])Yeah much as I suspected. This is nonSARGable because you stuck a column inside a function. This means that the engine has to calculate the result for every single row to determine if it meets the criteria. No amount of indexing is going to help there. All is not however, you just need to turn your logic around a little bit. If I can guess the requirement here is you want rows where the ServiceFromDate is within the last 4 years?
Try this and see if it doesn't help with the performance.
[DBNAME].[dbo].[Orders].[ServiceFromDate] > dateadd(month, -48, getdate()) as ServiceFromDateI have found more information and posted after your reply, but I'm going to try your suggestion to be thorough---i too thought this might be an issue and made a variable at the beginning to set the date to the threshhold and then changed the datediff as seen below.
declare @DateThreshhold datetime
set @DateThreshhold=DATEADD(M,-48,getdate())
--------------------------------------------------------------
claim.ServiceFromDate > @DateThreshhold -
Can you post the plan please (as a .sqlplan file)
A key lookup is Always to the clustered index. It cannot be to any other index. A key lookup occurs when SQL's done a seek on a nonclustered index (sometimes a scan), but the index used doesn't contain all the columns needed, so the missing columns are 'looked up' by reading them from the clustered index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Update. After removing the FAST 5000 option AND implementing the date comparison recommended, the Index Scan turned into an Index Seek. I really need to take an official Performance Tuning class when things settle down a bit.
If still desired, I can look into posting a plan file, but I need to check it out first---we are a healthcare company and I need to be cognizant of what I post on the ole interwebs...I REALLY do appreciate everyone's help!
- GilaMonster - Wednesday, February 22, 2017 12:38 PM
Thanks for the information...makes sense why it was using the clustered index now. Funny how the FAST option prevented the execution plan from using the Reporting Index I created...as soon as I add it back, it does a Key Lookup again---I tehn remove it and it uses my Reporting index. I always thought the FAST option just returned X number of rows if available based on the select---never knew it played a role in the execution plan. Learned several things today as a junior DBA!
Thanks All!
-
the FAST n hint says to return the first n rows as fast as possible, even if the overall query takes longer. So operations that start returning rows immediately are preferred, and things like sorts and hash joins avoided as far as possible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - chris_barnhart - Wednesday, February 22, 2017 12:43 PM
There is a free tool from Sentry One called Plan Explorer. It can anonymize a query plan. I'd suggest using that and then you should be fine. It's also a pretty good tool for exploring execution plans.
Until you track down a class, I can suggest the two books in my signature below. They ought to help. I've got a whole chapter on lookups in the fourth edition of the query tuning book.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply