Key Lookup to Clustered Index

  • Adam Bean

    One Orange Chip

    Points: 26631

    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.
  • frederico_fonseca

    SSChampion

    Points: 14777

    can you post table and indexes ddl for that table.

     

  • Adam Bean

    One Orange Chip

    Points: 26631

    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: 396760

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jeff Moden

    SSC Guru

    Points: 997320

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

Viewing 5 posts - 1 through 5 (of 5 total)

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