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.