How to set ALL the replication to DEADLOCK_PRIORITY LOW?

Question

I have just got this email here:

Then looking at the sql server log I find this deadlock was caused by one of our procedures (that was the victim) against the replication.

O.K. in this case in particular I noticed that this specific procedure could be tuned, I even found a missing index, as you can see below.

But this same situation has happened so many times, I would have had the procedure won, the replication was the victim, and I would deal with the procedure later on.

How can I put this:

SET DEADLOCK_PRIORITY LOW

everywhere, in every replication transaction?

this is to check it out:

    SELECT session_id,deadlock_priority 
FROM sys.dm_exec_sessions 
    WHERE SESSION_ID = @@SPID

How

Answer 1

After having a good setup and monitoring the tempdb we opted and used the READ COMMITTED SNAPSHOT as described in the comments.

How to implement Read Committed Snapshot and how it is different to Snapshot has been nicely described (including links explaining how to set it up) here.

In addition, READ_COMMITTED_SNAPSHOT (henceforth called RCSI) will only give you some properties of optimistic concurrency. RCSI and Snapshot (SI) differ in three main ways:

1. RCSI will be used immediately by all RC isolation queries.
2. RCSI provides a point-in-time (snapshot) view of the database for a single statement.
3. SI provides a point-in-time view for an entire transaction.
4. SI can have write conflicts.

also quoted from the same source:

Other useful details relating some gotchas and caveats when using RCSI/SI can be found in these articles:

• Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide
• Read Committed Snapshot Isolation: Writers Block Writers (RCSI)