March 21, 2016 at 4:31 am
Hi,
When trying to reduce the number of key lookups generated by an update statement, is there any benefit in adding extra key or included columns to the non-clustered index the query is using? Or will an update operation always require a key lookup to the clustered index anyway, when it comes to updating the data?
So, for example, if we have the following, would adding Col3 as a key column to IndexA remove the need for a key lookup? Or would a key lookup take place anyway in order for Col2 to be set to the value ‘abc’?:
TableA : Col1, Col2, Col3, Col4
PK (clustered index) : Col1
IndexA (non-clustered index) : Col4
Our query :
Update TableA Set Col2=’abc’
Where Col4 = ‘xyz’ and Col3 = ‘qrs’
March 21, 2016 at 6:44 am
Since you're updating the table, and the table is the clustered index, no matter what, the cluster is going to be accessed. However, look at the execution plan. If it's referencing the clustered index twice, once to update and once to read, it would be worth testing this index, yes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 21, 2016 at 8:22 am
Thanks for that Grant.
Looking at the Execution Plan for the query I can see that in fact there are two separate operations, the Key Lookup and then a Clustered Index Update, so as you suggest, it would make sense to look at adding a column to the non-clustered index.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply