The sql plans are available here:
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:
Here is the definition of the index that it uses:
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?
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:
Regards - Yasub
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
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 ;)
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, 2012 at 01:15 PM
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....
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
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:
answered Oct 15, 2012 at 04:50 AM