1

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

  • 2
    you should add the CREATE TABLE statement for the table involved, along with the indexes. You might find this helpful. – Max Vernon Jun 21 at 19:16
  • 1
    Why are you storing date and numbers as nvarchar and char ? can you post definition for campain_log table ? – Kin Shah Jun 21 at 19:51
2

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

  • ID is a primary key, clustered. idx_mail_disposition_status only has the mail_disposition_status column. I read your article - if I am understanding correctly another index with id and mail_disposition_status columns might help? Side question - why are the date fields not factoring in here? Thanks – nielsentm Jun 24 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.