Strange Execution Plan !!!!!!!!!!!!

Question

Strange Execution Plan !!!!!!!!!!!!

Bhuvnesh

SSC Guru

Points: 59351
More actions
December 15, 2009 at 6:51 am

#137673

--------- table definition-----------------------------
acct_id int
acct_stub uniqueidentifier
acct_num char(100
acct_status_id smallint
acct_name char(100)
acct_company char(100)
acct_url char(100)
acct_activate_datedatetime
acct_inactivate_datedatetime
reference_id char(100)
acct_remove_ready_datedatetime
acct_removed_datedatetime
created_date datetime
----------------indexes------------------------
idx_acct clustered located on PRIMARYacct_id
idx_acct_stubnonclustered located on PRIMARYacct_stub
idx_name nonclustered located on PRIMARYacct_stub, acct_name
---------------------------
--------------Query ---------
select * from tmp_account where acct_stub = 'E0519F76-94DB-411A-8FAA-0CDA6AF046C2'
and acct_company = 'COMPANYNAME_QE'
------------
Why i am getting key lookup and is it good to have key lookup instead of non clustered index
and what chages i can make in above query to avoid key look up ?
execution plan is attached
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

You are getting a key lookup because you are asking for all the columns to be returned in the query. SQL Server is using the nonclustered index idx_name to identify the records that need to be returned.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

ok thanks

how can i avoid

key lookup

give me the query

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Here are the indexes you have:

idx_acct clustered located on PRIMARY acct_id

idx_acct_stub nonclustered located on PRIMARY acct_stub

idx_name nonclustered located on PRIMARY acct_stub, acct_name

First, if most of your queries are like the one in your original post; drop idx_acct and recreate it as a nonclustered index; then drop idx_name and recreate it as the clustered index.

There, now your query won't use a key lookup.

You should know that key lookups are not necessarily bad. It really depends on how much data exists in the table plus how many records are being returned.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Another option, you can make your nonclustered indexes covering indexes by including the columns from the table used frequently in the index using the INCLUDE clause of CREATE INDEX.

In this case, though, I wouldn't do it as your original query wanted ALL the columns returned (SELECT * FROM dbo.mytable).

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

Bhuvnesh (12/15/2009)
how can i avoid
key lookup

Start by only returning the columns that you really need. No SELECT *

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

As per ur suggestion , i have select only required columns ( in below query)

select acct_id , acct_num, acct_name from tmp_account where

acct_stub = 'E0519F76-94DB-411A-8FAA-0CDA6AF046C2'

and acct_name = 'event003'

but stil i m getting keylookup with index seek ( exec plan attached "plan2.zip")

however i m having non clustered index on acct_stub and acct_name.

please enlighten.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

I said it was a start.

The index is not covering, that's why you're getting key lookups. It's very expensive to cover a query that selects all columns. Hence the first thing is to just get the columns needed. Now you need to add the other columns to the index, probably as include columns.

Question first is why? Why do you want to remove the key lookups here. Sure, they are expensive operations but, if the query is not often used or returns a small number of rows, it may well not be bad. Creating a covering index will eliminate the key lookups, but now you've got a larger index to maintain, plus the additional storage it requires. It's a trade off. Just because a query can have a covering index, doesn't mean that it necessarily should. You need to decide if it's important enough to go that route.

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

thanks a lot

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)