Pages

Thursday, March 23, 2017

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, we 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 most common commands

CREATE EVENT SESSION [replication_sch_m_locks] ON SERVER 
ADD EVENT sqlserver.lock_acquired(
    ACTION(sqlserver.client_app_name,sqlserver.sql_text)
    WHERE ([mode]=(2) AND [package0].[equal_int64]([object_id],(222623836))))
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, to create a publication, we will use sp_addarticle, and it will require SCH-M lock on each article. If we initialize them using the snapshot agent, it will require SCH-M locks on all articles of the publication.

To add articles to an existing publication, we will use sp_addarticle, and it will require SCH-M lock only on the articles being added. If we initialize them using the snapshot agent, it will require SCH-M locks only on the articles being added.

No comments:

Post a Comment