sql server transactional - replication - the internal procedures often deadlock with user processes Very often I have deadlocks on my subscription server, involving the replication internal stored procedures and user processes either ad hoc or procedure queries. For example:


  Deadlock of the replication procedure frame procname=Bocss2.dbo.sp_MSupd_dbotblBOrderItem line=79 stmtstart=8684 stmtend=14714 sqlhandle=0x03002a004d01df507b1245016ba3000001000000000000000000000000000000000000000000000000000000


With the following query:

SELECT 'INSERT INTO @TempTable1 (OrderNo, OrderSeqNo, OrderItemDate) 
VALUES (LTRIM(''',strBxOrderNo, '''),', sintOrderSeqNo, ', ''', 
  CONVERT(VARCHAR(20), sdtmOrderItemStatusUpdated, 113), ''')' 
FROM tblBOrderItem
WHERE sintOrderItemStatusId = 4 -- In Picking
AND sdtmOrderItemStatusUpdated BETWEEN GETDATE() -30 AND GETDATE() -1
 -- Give the warehouse a chance to deliver the order
AND decFinalPrice &gt; 0
ORDER BY strItemNo


I don't want to enable the snapshot isolation level - where writes don't block readers - at the cost of maintaining a row version at the tempDB, but I am considering partitioning the big tables that are usually the culprits on the deadlocks.

what stats I could have a look at so that I could guess whether or not this is a good idea?
Partitioning will likely not help, unless the user query in the deadlock can be rewritten in order to let partition elimination kick in. 

I would focus on making both operations in the deadlock graph as fast as possible instead. This will involve ad-hoc indexing or rewriting the query in a different form.

If the replication agent is often the deadlock winner, you could consider lowering its deadlock priority, in order to let the user queries run without being killed. However, I would explore this option only as a safety net in case you're not able to reduce deadlocks to a very rare event.