Sql-server – How to improve simple update statement causing blocking issues, inneficient index scan in query plan

blockingexecution-planperformancequery-performancesql serversql-server-2016

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.
enter image description here

Best Answer

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.

NUTS

  1. 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.

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

NUTS

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

Related Question