How to improve simple update statement causing blocking issues, inneficient index scan in query plan Please bear with me - I am on the development side but I've been tasked with finding the cause of several blocking issues after upgrading from sql server 2008 to 2016. The blocking is intermittent, everything runs fine for a couple weeks, then maybe once or twice a month it gets so bad the entire application is unusable. This is one of the repeat offending queries when that happens: 

UPDATE campaign_log 
SET date_modified = N'2019-06-18 14:28:19'
    , mail_disposition_status = '1'
    , status_date = N'06/17/2019'
    , activity_date =N'05/22/2019' 
WHERE id = N'03938240-0112-437C-AF97-ECCF2EF78E77'


It seems like this https://www.brentozar.com/pastetheplan/?id=H1YyzoqJS query execution plan is telling me the index scan [SUGARCRM].[dbo].[campaign_log].[idx_mail_disposition_status] is really inefficient. 

I'm not sure why Mail_disposition_status column is indexed or why this index is being chosen to run this query. It seems like maybe it should be using an index on id? There are 2,622,020 rows in the campaign_log table. 

Can anyone point me in the right direction on what I should be looking at to fix this? I'm honestly pretty lost on what to even search for. 

Editing to add definition of table - apologies but I don't have permissions to get a Create statement, so these are the fields. Also wanted to mention this is a 3rd party product so the table design is just stock. 

id, varchar(36)
campaign_id, varchar(36) 
target_tracker_key, nvarchar(36) 
target_id, varchar(36) 
target_type, nvarchar(100) 
activity_type, nvarchar(100) 
activity_date, datetime 
related_id, varchar(36) 
related_type, nvarchar(100) 
archived, bit 
hits, int 
list_id, varchar(36) 
deleted, bit 
date_modified, datetime 
more_information, nvarchar(100) 
marketing_id, varchar(36) 
prospect_address_id, varchar(36) 
mail_disposition_status, nvarchar(50) 
status_date, datetime 
mail_index, int

Here is the definition for Index idx_mail_disposition_status.
There are a few easy things to try:


Stop passing in GUIDs as NVARCHAR


It looks like the log table has id stored as a UNIQUEIDENTIFIER or VARCHAR. You should take the N prefix off the GUID literal string.




The idx_mail_dispositionindex might not be ideal


It's unclear what the index definition is, but if id isn't the leading column, you may not get a seek to the id you're looking for anyway. You could try just creating an index on id, if one doesn't exist already. If it's truly unique, create a unique index on it. The single row estimate makes it attractive.


The reason this plan is slow is because you need to read the entire index because of one or both of the reasons above.




For more related information, check out my recent blog post about this: Index Key Column Order And Locking