x

Columns not in Key look Up, why?

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

more ▼

asked Oct 12, 2012 at 09:08 AM in Default

yasubmj gravatar image

yasubmj
40 2 2 3

can you attach the plan as a plain xml file?
Oct 12, 2012 at 10:15 AM Fatherjack ♦♦
Oct 12, 2012 at 10:37 AM yasubmj
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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 ;)
more ▼

answered Oct 12, 2012 at 10:22 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

Thanks for your reply Usman, the attached plans can be found here:

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

If you see the plan, you ll get what i am trying to say here.
Oct 12, 2012 at 10:33 AM yasubmj

I cannot see the index IX_tblBIUsersRumbaPegasusMap DDL in your post which is used in the execution plan. Changing the names in the provided DDLs is making it a bit more difficult

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 ;)
Oct 12, 2012 at 12:11 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Oct 12, 2012 at 01:15 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

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, 2012 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, 2012 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, 2012 at 05:02 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Oct 15, 2012 at 04:50 AM

yasubmj gravatar image

yasubmj
40 2 2 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1840
x249

asked: Oct 12, 2012 at 09:08 AM

Seen: 840 times

Last Updated: Oct 15, 2012 at 05:02 AM