Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Columns not in key look up, why? Expand / Collapse
Author
Message
Posted Thursday, October 11, 2012 7:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 07, 2014 4:09 AM
Points: 26, Visits: 183
Hey all,

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

Plans have been attached

Regards - Yasub


  Post Attachments 
plan - before index change.sqlplan (12 views, 27.63 KB)
plan - after index change.sqlplan (5 views, 25.45 KB)
Post #1371449
Posted Thursday, October 11, 2012 7:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 07, 2014 4:09 AM
Points: 26, Visits: 183
Sorry to spam, 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
Post #1371457
Posted Friday, October 12, 2012 7:21 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, March 14, 2013 11:00 AM
Points: 444, Visits: 807
To me this 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 I am making sense to you ;)
Post #1372109
Posted Monday, October 15, 2012 3:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 07, 2014 4:09 AM
Points: 26, Visits: 183
Hmmm... so do you mean that if the other 2 columns (Message, DateTimeStamp) were a part of another index on the table... and were not a part of the index which it is using (PegasusMap), then it would do a key look up on those 2 as well?
Post #1372631
Posted Monday, October 15, 2012 3:55 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, March 14, 2013 11:00 AM
Points: 444, Visits: 807
yasubmj (10/15/2012)
Hmmm... so do you mean that if the other 2 columns (Message, DateTimeStamp) were a part of another index on the table... and were not a part of the index which it is using (PegasusMap), then it would do a key look up on those 2 as well?


Yes, this is how it would be IMHO. You can easily test it in your case
Post #1372636
Posted Monday, October 15, 2012 5:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 1,059, Visits: 5,756
yasubmj (10/11/2012)
Hey all,

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:

...


Don't forget that as well as the clustered index (the table), you are also updating any index which contains the updated columns. You only have one with this set of columns - IX_tblBIUsers_OCU. SQL Server has to find the correct rows to update in the index, and if the available columns are insufficient then it will do a key lookup. It looks like, in this case, only columns [RuTestBatchStatus] and [UserStatus] are sufficient to identify the correct rows.
By including these columns in index IX_tblBIUsersRuTestMap, SQL Server no longer needs to do a key lookup to find rows in IX_tblBIUsers_OCU to update.

Edit: Should have read Usman's post first!



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1372663
Posted Tuesday, October 16, 2012 3:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, March 14, 2013 11:00 AM
Points: 444, Visits: 807
ChrisM@home (10/15/2012)

Edit: Should have read Usman's post first!


No worries Your way of explaining was rather good for me to analyze how better my explanation could have been
Post #1373089
Posted Wednesday, October 17, 2012 6:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 07, 2014 4:09 AM
Points: 26, Visits: 183
Thanks a ton Usman, able to solve a lot of issues after your explanation. Thanks, you rock!
Post #1373753
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse