Columns not in Key look Up, why?

Question

Hey all,

The sql plans are available here:

http://www.sqlservercentral.com/Forums/Topic1371449-391-1.aspx

We came across a problem today, no solutions yet, here is the scene, we have a query which uses an index and makes a key look up as the columns that it updates are not in the index it is using, here’s the query:

DECLARE @var1 tinyint = 1, @var2 tinyint = 1, @var3 nvarchar(100) = N'11', @var4 varchar(50)= '1128651', @var5 int = 8
BEGIN TRAN
UPDATE tblBIUsers SET DateTimeStamp=GETDATE(), 
[Message] = @var3,
RuTestBatchStatus=@var1,UserStatus=@var2
WHERE RuTestBatchID=@var4 AND TestPartSeqNumber=@var5
ROLLBACK TRAN

Here is the definition of the index that it uses:

CREATE NONCLUSTERED INDEX [IX_tblBIUsersRuTestMap] ON [tblBIUsers] 
(
[RuTestBatchID] ASC,
[TestPartSeqNumber] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

If you observe the key lookup in the attached plan (plan – before index change), it shows an output list of only 2 columns i.e. RuTestBatchStatus and UserStatus, whereas if you compare the update and the index, there are 2 more columns which are in the update but not shown in the key look up (Message, Datetimestamp). If we modify the current index to look like below then the key look up vanishes and does a plain index seek (check out the plan – after index change), so the question is why it doesn’t perform the key look up for the other columns (Message, Datetimestamp) even though they are not a part of the index?

CREATE NONCLUSTERED INDEX IX_tblBIUsersRuTestMap ON [tblBIUsers] 
(
[RuTestBatchID] ASC,
[TestPartSeqNumber] ASC
)
INCLUDE ([RuTestBatchStatus], [UserStatus]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Unable to attach the plans, it doesnt allow, .sqlplan attachment.

just to add, the other 2 columns, on which its not doing a key look up (Message, Datetimestamp) are not part of the clustered index. Here's the entire table + index definition:

CREATE TABLE [tblBIUsers](
[BIUserID] [bigint] IDENTITY(1,1) NOT NULL,
[LoginName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Password] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FirstName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MiddleName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Gender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tblBIUsers_Gender] DEFAULT ('X'),
[Email] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Student] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrganisationID] [bigint] NULL,
[TestBatchID] [uniqueidentifier] NOT NULL,
[TestPartSeqNumber] [int] NOT NULL,
[TestBatchName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RuTestBatchID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RuTestBatchStatus] [tinyint] NULL,
[UserStatus] [tinyint] NULL,
[Message] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CourseID] [bigint] NULL,
[IsDeleted] [bit] NOT NULL CONSTRAINT [DF_tblBIUsers_IsDeleted] DEFAULT ((0)),
[DateTimeStamp] [datetime] NOT NULL CONSTRAINT [DF_tblBIUsers_DateTimeStamp] DEFAULT (getdate()),
[DateTimeStampIns] [datetime] NOT NULL CONSTRAINT [DF_tblBIUsers_DateTimeStampIns] DEFAULT (getdate()),
[UploadedUserID] [bigint] NULL,
[UploadedDate] [datetime] Not NULL CONSTRAINT [DF_tblBIUsers_UploadedDate] DEFAULT (getutcdate()),
[BusinessRuleSet] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_tblBIUsers] PRIMARY KEY NONCLUSTERED 
(
[BIUserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[tblBIUsers]') AND name = N'CIX_tblBIUsers')
CREATE CLUSTERED INDEX [CIX_tblBIUsers] ON [tblBIUsers] 
(
[TestBatchID] ASC,
[TestPartSeqNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[tblBIUsers]') AND name = N'IX_tblBIUsersRuTestTestMap')
CREATE NONCLUSTERED INDEX [IX_tblBIUsersRuTestTestMap] ON [tblBIUsers] 
(
[RuTestBatchID] ASC,
[TestPartSeqNumber] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO 



IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[tblBIUsers]') AND name = N'IX_tblBIUsersUplDtPwdIsDel')
CREATE NONCLUSTERED INDEX [IX_tblBIUsersUplDtPwdIsDel] ON [tblBIUsers] 
(
[UploadedDate] ASC,
[Password] ASC,
[IsDeleted] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO 


IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[tblBIUsers]') AND name = N'IX_tblBIUsers_OCU')
CREATE NONCLUSTERED INDEX [IX_tblBIUsers_OCU] ON [tblBIUsers] 
(
[OrganisationID] ASC,
[CourseID] ASC,
[UploadedUserID] ASC
)
INCLUDE ( [UploadedDate],
[RuTestBatchID],
[RuTestBatchStatus],
[UserStatus],
[IsDeleted]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Regards - Yasub

Answer 1

If I did understand your question properly (Attached plan should have helped), you are saying that since four columns are to be updated then they should be part of the key lookups (rather should be called seek predicates as key lookup is another term)? If yes, then you are confusing yourself. The index seek/scan is dependent upon what you are trying to find / to filter the rows from the table OR in more precise manner what you are asking for in your WHERE clause. Hence the optimizer only needs to seek/scan rows where RuTestBatchID = '1128651' and TestPartSeqNumber = 8, which rightly makes index IX_tblBIUsersRuTestMap the right choice. That index seek/scan should then be followed by the Clustered Index Update as every index does have the clustered index values. So there should be no need to have the other two columns as part of the index. Hope I was able to make you understand.

EDIT:

After seeing the execution plans

To me this still is fine and the optimizer is doing it what seems to be an efficient way. In first case, the optimizer knows that it has to update the depending indexes as well. Hence, the optimizer feels that it is better to get [RuTestBatchStatus] and [UserStatus] values from the clustered index with key lookups and later perform the updates in clustered index and the dependent index i.e. IX_tblBIUsers_OCU which is the only index where values of [RuTestBatchStatus] and [UserStatus] are to be changed.

In second case, when you did add [RuTestBatchStatus] and [UserStatus] columns as included columns in the IX_tblBIUsersRumbaPegasusMap index, the optimizer knows the values of the [RuTestBatchStatus] and [UserStatus] columns in advance. Hence, no key lookups were required. But then an additional cost is added of updating the IX_tblBIUsersRumbaPegasusMap itself. So the number of indexes to be updated increased from 2 to 3. I hope this time I am making more sense to you ;)

Answer 2

0

Hi,

Based on the plans it seems, that the reason for the key lookup is that those two columns are part of the clustered key, so they are necessary to find the original record to be updated. As they are not covered by the index, therefore server is doing a key lookup to get values of that columns.

In the second case, when all the fields are covered by the index, no additional lookup is necessary as all the columns are retrieved during the index see.

In the Plans there is clustered index update, so as I have mentioned, the clustered index will contain those fields as part of the key.

answered Oct 12 '12 at 01:15 PM

Pavel Pawlowski
20.3k ● 5 ● 10 ● 20

Sorry Pavel, those two columns are not part of clustered index but are part of the non-clustered index. Otherwise, I see it roughly the same as my edited answer but with better wording ;)

Oct 12 '12 at 01:28 PM Usman Butt

Usmann, in first plan, where the lookup was in the plan, those columns were not part of the non clustered index. Those two column were part of non clustered index when pure index seek was done. I cannot re-check the plans right now as I'm currently on mobile device, but if I remember good, it was as I mentioned.

Oct 12 '12 at 01:40 PM Pavel Pawlowski

Hi Pavel. So sorry for the late response. Actually, I was talking about the non-clustered index IX_tblBIUsers_OCU. The two columns [RuTestBatchStatus] and [UserStatus] are part of it. Also the DDL of the clustered index shows that these same columns are not part of the clustered index. So as we know, the more number columns are going to be introduced in the indexes, the more costly updates and deletes would be. But I am glad we are on the same page ;)

Oct 15 '12 at 05:02 AM Usman Butt

(comments are locked)

Answer 3

Thanks Usman, Pavel.... i am sorry i had to change some name to obfuscate some business data.... hence different name... i am still not sure of the answers.... i will re phrase the question here, will try to make it clearer....

Original Query:

DECLARE @var1 tinyint = 1, @var2 tinyint = 1, @var3 nvarchar(100) = N'11', @var4 varchar(50)= '1128651', @var5 int = 8 BEGIN TRAN UPDATE tblBIUsers SET DateTimeStamp=GETDATE(), [Message] = @var3, RuTestBatchStatus=@var1,UserStatus=@var2 WHERE RuTestBatchID=@var4 AND TestPartSeqNumber=@var5 ROLLBACK TRAN

Original Index:

CREATE NONCLUSTERED INDEX [IX_tblBIUsersRuTestMap] ON [tblBIUsers] ( [RuTestBatchID] ASC, [TestPartSeqNumber] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

If you see the plan (Before), it uses the above index. The update has a total of 6 columns (4 in update and 2 in where clause). The ones in where clause are in the index, hence sql server optimiser uses it seeking for the records.... ideally, it should do the key lookup to find the 4 columns in the update as they are not in the index, but it does a key look up only for 2 columns.... WHY?????

Plans can be found here:

http://www.sqlservercentral.com/Forums/Topic1371449-391-1.aspx

Columns not in Key look Up, why?

Follow this question

Related Questions