Replication Commands That May Cause Blocking

Besides the replication snapshot agent, there are other replication commands that require a Schema Modification (SCH-M) lock for a very short duration on the article(s) of the publication, and they may cause blocking in very busy publishers.

To find out which commands use SCH-M, you can use the lock acquired extended event filtered by the object id of the article (table) in the publication database and the lock mode = SCH-M while executing the replication commands:

CREATE EVENT SESSION [replication_sch_m_locks] ON SERVER 
ADD EVENT sqlserver.lock_acquired(
    ACTION(sqlserver.client_app_name,sqlserver.sql_text,sqlserver.tsql_stack)
    WHERE ([mode]=(2) 
    AND ([object_id]=(2106086889) 
    OR [object_id]=(2138087003) 
    OR [object_id]=(22603469)) 
    AND [database_id]=(37)))
ADD TARGET package0.event_file(SET filename=N'c:\temp\replication_sch_m_locks'
    ,max_file_size=(100),max_rollover_files=(10))
WITH (TRACK_CAUSALITY=ON)
GO

Below are the commands that use SCH-M lock on articles (tables)

• Sp_addarticle @article='article'
• Sp_changearticle @article='article '
• Sp_dropsubscription @article='article' 
• Sp_dropsubscription @article=’All’ 
• Initialize with a snapshot the articles being added 

For example, if you add add one article (object id) to an existing publication that has two articles (object id = 2106086889 and object id = 2138087003), sp_addarticle requires SCH-M lock on the table you are adding (object id = 22603469)

However, when you start the snapshot agent, it will require SCH-M locks on all the tables of the publication (object id = 2106086889, object id = 2138087003, and object id = 22603469), not just the one you just added (object id = 22603469)