How to improve simple update statement causing blocking issues, inneficient index scan in query plan
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