How to avoid KeyLookpu in Execution Plan of a Query ?

Question

How to avoid KeyLookpu in Execution Plan of a Query ?

prabhu.st

SSCrazy

Points: 2552
More actions
December 18, 2013 at 12:36 am

#301477

Table Structure: (with 17 Columns)
----------------
CREATE TABLE [dbo].[MyTable](
[Column1] [int] NOT NULL,
[Column2] [varchar](10) NOT NULL,
[Column3] [varchar](100) NULL,
[Column4] [varchar](20) NULL,
[Column5] [varchar](20) NULL,
[Column6] [char](2) NOT NULL,
[Column7] [varchar](20) NULL,
[Column8] [varchar](12) NULL,
[Column9] [varchar](20) NULL,
[Column10] [char](3) NULL,
[Column11] [varchar](20) NULL,
[Column12] [bit] NOT NULL,
[Column13] [bit] NOT NULL,
[Column14] [bit] NOT NULL,
[Column15] [datetime] NULL,
[Column16] [datetime] NULL,
[Column17] [char](2) NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[Column1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
MyQuery:
---------
SELECT Column1,Column2,Column3,Column4
FROM dbo.MyTable
WHERE Column2 ="Some Condition"
I found Key Lookup in the Execution plan, May I know how could I avoid this and get the performance
Thanks,
Prabhu

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Make the index on column2 (which you haven't shown) covering. Whether that's a good idea overall depends on how expensive that key lookup is and how expensive the additional modifications of the index will be. Test and see

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 2

Hi

You can also avoid Key lookup if you use the clustered key column (Column1) in the where condition instead of Column2, of course if it suits your case, so that there is no necessary to extend/create the non-clustered index.

Regards,

IgorMi

Igor Micev,My blog: www.igormicev.com

prabhu.st SSCrazy Points: 2552 More actions · Answer 3

@Gail,

I am Sorry, just I am going to try your Idea, I was little bit busy (sorry to say this...) with daily task, I would let you know how it works once Implemented.

@IgorMi,

No , Column1 is a Identity with seed 1 and Increment 1, so probably I cannot expect the query to use that Column, so I would like to try as Gail Suggested.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

If your lookups will most often be by column2, you need to cluster the table on column2, or an encoded version of it, rather than having a dopey, useless cluster on column1.

Since column2 is varchar(10), you could encode it into an integer and then cluster on that. Your lookups then become something like:

WHERE column2_code = (SELECT column2_code FROM dbo.column2_values WHERE column2_value = 'xxx')

You can keep column1 as the PK if you really, really want to, although it's meaningless really.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

ScottPletcher (12/18/2013)
If your lookups will most often be by column2, you need to cluster the table on column2, or an encoded version of it, rather than having a dopey, useless cluster on column1.
Since column2 is varchar(10), you could encode it into an integer and then cluster on that. Your lookups then become something like:
WHERE column2_code = (SELECT column2_code FROM dbo.column2_values WHERE column2_value = 'xxx')
You can keep column1 as the PK if you really, really want to, although it's meaningless really.

I wouldn't cluster based on Column2 because it doesn't follow the best recommendation of being narrow, unique, and ever-increasing. If you cluster on Column2, you're just asking for page splits.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

prabhu.st SSCrazy Points: 2552 More actions · Answer 6

Yeah, I too feel the same, Clustering the Column2 might not be the right choice for my requirement, what I did is I created the Covering Index with the ON Column2 and Included the Select Columns, (Hoorey.. ) found the keylookup went excluded, but there comes again a problem, this index is not suitable for some other queries and reflected in Index Scan and Eager Spooling

so I request you guys to give me a best way were I can find a Moderate(Optimized) for all Queries on the table,

(I think, I would like to request you to give a solution based on the Table structure rather than the Query) ' cause Query would change for time to time when someone else ALTERED the Stored Procedure.

Thanks,

Prabhu

prabhu.st SSCrazy Points: 2552 More actions · Answer 7

== Correction:

--------------

what I did is, I created a Non-Clustered (Non-Unique) index on Column2

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 8

so I request you guys to give me a best way were I can find a Moderate(Optimized) for all Queries on the table,
(I think, I would like to request you to give a solution based on the Table structure rather than the Query) ' cause Query would change for time to time when someone else ALTERED the Stored Procedure.

You cannot create one (or a few more) indexes so that all future queries are covered and no Key lookups, scans, eagar spool and etc. at the same time.

Igor Micev,My blog: www.igormicev.com

prabhu.st SSCrazy Points: 2552 More actions · Answer 9

You cannot create one (or a few more) indexes so that all future queries are covered and no Key lookups, scans, eagar spool and etc. at the same time.

I agree You are right, but recently I had one interview, the Interviewer asked me the same question, like if a table has 30-40 columns is it good to add all the columns in a INCLUDE clause, I said "NO" in a guessing, but I want to figure out the exact thing "is there any solution in such a case", that is why I wrote that Query for testing, still not able to get the answer, please help me to get out of this "Stuck".

Thanks.

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 10

prabhu.st (12/19/2013)
You cannot create one (or a few more) indexes so that all future queries are covered and no Key lookups, scans, eagar spool and etc. at the same time.
...the Interviewer asked me the same question, like if a table has 30-40 columns is it good to add all the columns in a INCLUDE clause, I said "NO" in a guessing

It depends. For example there is an index in one of the systems I'm working on where a non-clustered index with 2 key columns has included all other columns and that index and the clustered are the most used indexes by the queries in the system.

You should read some theory for indexes:

http://www.sqlservercentral.com/search/?q=indexes&t=a&sort=relevance

Regards,

IgorMi

Igor Micev,My blog: www.igormicev.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

prabhu.st (12/19/2013)
so I request you guys to give me a best way were I can find a Moderate(Optimized) for all Queries on the table,

Don't. While it is possible to create the absolute best index for each and every query, it's an exceedingly bad idea (unless you have a table which is only ever accessed in one way via one column or set of columns).

Optimise queries that need optimising. Tune for the important, frequent queries. Make sure that queries are running fast enough to meet their performance requirements.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

prabhu.st SSCrazy Points: 2552 More actions · Answer 12

Hi Gila,

Optimise queries that need optimising. Tune for the important, frequent queries

I don't know why both of our thought are synching for most of the time, I too feel the same,

what I replied exactly to the Interviewer is, "Appending all the Columns into the INCLUDE option will not be a right decision, Instead, we can try tune up the queries which were frequently used and needs a tuning.."

Thanks,

Prabhu

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

prabhu.st (12/19/2013)
what I replied exactly to the Interviewer is, "Appending all the Columns into the INCLUDE option will not be a right decision, Instead, we can try tune up the queries which were frequently used and needs a tuning.."

It might be the right decision in some circumstances, some situations. It might be completely the wrong decision in other situations. Depends on importance of the query, size of the data, form of the query, read-write balance of the table, etc

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

prabhu.st SSCrazy Points: 2552 More actions · Answer 14

so the Conclusion is:

Avoiding the KeyLookup (excluding from the Query Plan/Execution Plan) is depends on the Percentage it occupies the latency of performance.

If it has a low importance and low percentage of the performance degrade, then it can be left as it is, since we cannot add overhead for a simple issues on the DB side,

whereas we are suppose to sought out this Operation (KeyLookup) if it plays a major role of performance.

folks, thanks for all your replies and supports....

Thanks,

Prabhu