Key Lookup to Clustered Index

Question

Post reply

Key Lookup to Clustered Index

Adam Bean

One Orange Chip

Points: 26631
More actions
September 23, 2020 at 12:20 pm

#3790316

Hey all,
Been a bit since I've been down this road. Clearly I'd assume this is as expected and I'm just old and rusty.
Attached is the execution plan I'm looking at. I have an index seek happening with a key lookup. The key lookup is happening on the clustered index. I attempted to add the usrt_int_id (the clustered PK) as a included column; however, that then makes this non clustered index the same size as the clustered index.
I feel like I'm doing this wrong.
Any advice?
Thanks
Attachments:
You must be logged in to view attached files.

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

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

frederico_fonseca SSCoach Points: 16013 More actions · Answer 1

frederico_fonseca

SSCoach

Points: 16013

September 23, 2020 at 12:32 pm

#3790326

can you post table and indexes ddl for that table.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

CREATE TABLE [dbo].[UserTrail](
[usrt_int_Id] [bigint] IDENTITY(1,1) NOT NULL,
[usrt_int_FacilityId] [bigint] NULL,
[usrt_int_EntityTypeId] [bigint] NOT NULL,
[usrt_int_EntityId] [bigint] NOT NULL,
[usrt_uid_TransactionId] [uniqueidentifier] NULL,
[usrt_vch_EntityDetails] [nvarchar](max) NULL,
[usrt_int_UserActionId] [bigint] NOT NULL,
[usrt_int_UserId] [bigint] NOT NULL,
[usrt_dtm_DateTime] [datetime] NOT NULL,
[usrt_int_UserFunctionsId] [bigint] NOT NULL,
 CONSTRAINT [PK_UserTrail] PRIMARY KEY CLUSTERED 
(
[usrt_int_Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [UserTrail_ET_DT_inc] ON [dbo].[UserTrail]
(
[usrt_int_EntityId] ASC,
[usrt_dtm_DateTime] ASC
)
INCLUDE([usrt_int_FacilityId],[usrt_int_UserActionId]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 3

In a key lookup operation, what's being looked up is not the key value, but other values. The clustered key value is actually already a part of the nonclustered index. In your case, go to the Key Lookup operator in the plan. Right click it and select properties (I always just pin the Properties window open when working on execution plans). Look for the Output List property. These are the columns that are being looked up through the key lookup operation. It's just using the clustered key value to find the right row.

In your case, you'd want to add these columns to the nonclustered index: [EZMULTI_DB_TRAN_QA_SSMERP].[dbo].[UserTrail].usrt_vch_EntityDetails, [EZMULTI_DB_TRAN_QA_SSMERP].[dbo].[UserTrail].usrt_int_UserId

And yeah, it will increase the size of it. The INCLUDE operation is adding these columns at the leaf level of the index, so the size necessarily goes up.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

Adam Bean wrote:

Hey all,
Been a bit since I've been down this road. Clearly I'd assume this is as expected and I'm just old and rusty.
Attached is the execution plan I'm looking at. I have an index seek happening with a key lookup. The key lookup is happening on the clustered index. I attempted to add the usrt_int_id (the clustered PK) as a included column; however, that then makes this non clustered index the same size as the clustered index.
I feel like I'm doing this wrong.
Any advice?
Thanks

You've been around long enough to know that you've posted an "Estimated Execution Plan" and that it would be much better if you posted an "Actual Execution Plan".

As a bit of a side bar, I also see a "fn_Split" entry in that and, if it's the function I'm thinking about, it's probably a worse problem than your key lookups but the execution plan won't show that..

--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)

Tac11 SSCertifiable Points: 6976 More actions · Answer 5

@Grant, I am having same issue. Execution plan "Output List' says 3 columns, col1, 2 and 3 above that Object" is 'PK_id'.

I understand if you add those columns to a NC index it will go away, but since I have few NC index and those columns are also part of the key or include. So my question is which NC index should add above 3 columns to eliminate 'Key Lookups' ?

Thanks

ScottPletcher SSC Guru Points: 100946 More actions · Answer 6

Get rid of the functions around the usrt_dtm_DateTime column; you don't need them and they will prevent lookup seeks, drastically harming performance:

SET @ToDate = CAST(@ToDate AS date) /* make sure time is 00:00:00 on @ToDate*/SELECT ...
WHERE ... 
    /* NOTE the "<" next day rather than "<="; this avoids having to convert
       usrt_dtm_DateTime to date in order to strip the time */ 
    AND usrt_dtm_DateTime >= @FromDate AND usrt_dtm_DateTime < DATEADD(DAY, 1, @ToDate)

Then, since hopefully (nearly) all of your queries against this table specify a datetime range, change the clustering key to:

CONSTRAINT [PK_UserTrail] PRIMARY KEY CLUSTERED ( usrt_dtm_DateTime, usrt_int_Id )

Then you likely won't need the other nonclus index(es?):

DROP INDEX [UserTrail_ET_DT_inc] ON dbo.UserTrail;

and you avoid have to endlessly futz with adding INCLUDEd columns to nonclus indexes as the queries change. Because, guaranteed, at some point another column will get added to the query and you'll be back in the same situation, figuring out which yet-another-column to add to some nonclus index(es). Over and over, repeating ad nauseum.

With the correct clustered index in place, as long as the date range is reasonable, any/all other conditions on the query will not cause performance issues, no matter how complex they are.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

ScottPletcher SSC Guru Points: 100946 More actions · Answer 7

Of course it's optional if you make the new clustering index a PK or not.

That is, you could drop the existing PK, create the new clustering index, then re-add the PK only on $IDENTITY, just make it NONCLUSTERED rather than clustered.

The critical thing is to get the clustering key to be ( usrt_dtm_DateTime, usrt_int_Id ) rather than just usrt_int_Id.

Whether it's officially the PK or not is irrelevant, as long as you have the best clustering on the table for best overall performance.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 8

Tac11 wrote:

@Grant, I am having same issue. Execution plan "Output List' says 3 columns, col1, 2 and 3 above that Object" is 'PK_id'.
I understand if you add those columns to a NC index it will go away, but since I have few NC index and those columns are also part of the key or include. So my question is which NC index should add above 3 columns to eliminate 'Key Lookups' ?
Thanks

What Scott said is likely the issue.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Key Lookup to Clustered Index

Attachments: