January 6, 2021 at 6:37 am
Happy New Year to all. Hope all doing excellent
This is my first post in 2021.
I have below update statement which updates multiple columns , the index in SourceID was giving lookup on UpdateUserID ,UpdateDT. Included those columns then lookup vanished. My doubt here is why only those 2 columns in lookup , the only difference I can found was that other columns allow NULL values. I added a NOT NULL date column to UPDATE and was expecting a lookup but index seek happened. Can experts help me understand why?
Update [pcs].[Weight_Test]
SET Weight = '23793.000',
TlWeight = '23793.000',
VWeightingDate = '2021-01-01 18:12:56.747',
DetDateTime = '2021-01-01 18:12:56.747',
Party = 'Te',
Person = 'P01',
WeightUnitID = '1',
UpdateUserID = 'SERVICE',
UpdateDT = '2021-01-01 18:12:56.713',
IsSent = 1,
DataSource = 'API'
where SourceID = 766843
January 6, 2021 at 2:24 pm
Without seeing the execution plan itself, I'm just guessing. However, it's likely that you're seeing lookups because of foreign keys. Modifying columns that don't contain any foreign keys will likely be a clustered index seek because it simply has to find the right row and then modify the data. However, as soon as there is a foreign key, it has to validate that value against the other table. That sounds like what you're seeing. Again, however, guessing without the execution plan.
"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
January 6, 2021 at 5:15 pm
Without seeing the execution plan itself, I'm just guessing. However, it's likely that you're seeing lookups because of foreign keys. Modifying columns that don't contain any foreign keys will likely be a clustered index seek because it simply has to find the right row and then modify the data. However, as soon as there is a foreign key, it has to validate that value against the other table. That sounds like what you're seeing. Again, however, guessing without the execution plan.
Thanks Grant, it's a Non-Clustered Index seek happening once I included those columns to that Non_Clustered index. No foreign key exists in that table.
January 6, 2021 at 7:01 pm
Looking at the plan, it's calculated columns or constraints. It has to retrieve the data in order to do the validations, so that's why you're seeing a lookup operation.
"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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply