September 23, 2020 at 12:20 pm
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
September 23, 2020 at 12:32 pm
can you post table and indexes ddl for that table.
September 23, 2020 at 12:34 pm
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
September 24, 2020 at 11:14 am
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
September 24, 2020 at 3:11 pm
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
Change is inevitable... Change for the better is not.
August 26, 2021 at 3:36 pm
@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
August 26, 2021 at 8:53 pm
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) "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".
August 26, 2021 at 9:00 pm
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) "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".
August 30, 2021 at 1:26 pm
@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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply