Distribution2:Distribution Writer

时间:2023-03-09 04:11:09
Distribution2:Distribution Writer

Distribution Writer 调用Statement Delivery 存储过程,将Publication的改变同步到Subscriber中。查看Publication Properties->Ariticle Properties->Statement Delivery 属性,Distribution Writer调用Insert,Update 或 Delete 存储过程,实现数据的同步。

Distribution2:Distribution Writer

Distribution Writer不是每一个Command单独提交,而是根据CommitBatchSize 和CommitBatchThreshold来批量提交Commands,这样可以提高性能。

-CommitBatchSize commit_batch_size Is the number of transactions to be issued to the Subscriber before a COMMIT statement is issued. The default is 100.

-CommitBatchThreshold  commit_batch_threshold Is the number of replication commands to be issued to the Subscriber before a COMMIT statement is issued. The default is 1000.

一般情况下,Distribution Writer的写入速度十分快,很少出现 Latency Problem。如果出现PageIOLatch_EX 或  PageIOLatch_SH 等待,请参考《Latch2:Latch和性能》:

PAGEIOLATCH_SH 发生在用户访问一个数据页,同时SQL Server正在将数据页从磁盘写入内存,经常发生PAGEIOLATCH_SH等待,说明内存不够大,导致SQL Server需要做很多页面读取的操作,磁盘IO是内存压力的副作用。

PAGEIOLATCH_EX:发生在从Disk page读取到内存buffer中,经常发生PAGEIOLATCH_EX等待,说明Disk读取速度慢,这和内存没直接关系。

Appendix:

引用《Transactional Replication Conversations》:

Distribution Writer thread writing queue commands to the Subscriber via parameterized stored procedures prefixed with sp_MSupd…, sp_MSins…, sp_MSdel… to apply individual row changes to each article at the subscriber.

The Writer-Thread calls Replication created stored procedures to apply changes to the subscriber.  These sp’s rely on a user table’s unique index (usually primary key) to locate records to be updated or deleted.  Latency in the Writer thread is usually see in long-running execution time for these stored procedures.

Possible Cause: Not Replicating SQL statements as “Parameters”

Obtain the create publication script and check ALL articles to see if the SQL statements are being sent as batch of ‘parameters’ statements.  The status = 8 indicates ‘parameters’ batching is not enabled.

查看Article的Status,推荐将Status设置为25。

select artid,
    name,
    objid,
    pubid,
    status
from dbo.sysarticles

Satus:The bitmask of the article options and status, which can be the bitwise logical OR result of one or more of these values:

1 = Article is active.

8 = Include the column name in INSERT statements.

16 = Use parameterized statements.

24 = Both include the column name in INSERT statements and use parameterized statements.

64 = Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

For example, an active article using parameterized statements would have a value of 17 in this column. A value of 0 means that the article is inactive and no additional properties are defined.

Article status can be updated on-demand using the following Replication stored procedure.  This should be executed on all articles.

USE [Publisher_database]
GO

exec [sys].[sp_changearticle]
    @publication    sysname = NULL,
    @article        sysname = NULL,
    ) = NULL,
    ) = NULL

参考doc:

Transactional Replication Conversations

事务复制会话 (五)

事务复制会话 (四)

sysarticles (Transact-SQL)