October 11, 2012 at 7:08 am
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
October 11, 2012 at 7:21 am
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
October 12, 2012 at 7:21 am
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 😉
October 15, 2012 at 3:45 am
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?
October 15, 2012 at 3:55 am
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 🙂
October 15, 2012 at 5:39 am
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!
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 16, 2012 at 3:24 am
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 😉
October 17, 2012 at 6:13 am
Thanks a ton Usman, able to solve a lot of issues after your explanation. Thanks, you rock! 😎
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply