troubleshooting sql server Distribution（分发库优化）

表象

我们某个较核心项目的分发库【Distribution】磁盘使用接近200G；
监控的阻塞源头中较频繁出现【分发清除: distribution】；
【分发清除: distribution】 job平均耗时11分钟以上；
从【distribution 】所在的独立E盘（ssd）磁盘队列以及活动时间%来看IO使用非常频繁；

系统版本相关信息如下

Microsoft SQL Server 2017 (RTM-CU14) (KB4484710) - 14.0.3076.1 (X64) Mar 12 2019 19:29:19 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: ) (Hypervisor)

库大小

表大小

阻塞情况

通过在线查看阻塞情况，常常会出现【分发清除: distribution】这个job；

查看该job历史执行记录，平均耗时11分钟以上；

日期		2020/2/26 7:45:00
日志		作业历史记录 (分发清除: distribution)

步骤 ID		1
服务器		************
作业名称		分发清除: distribution
步骤名称		运行代理。
持续时间		00:11:43
SQL 严重性		0
SQL 消息 ID		21010
已通过电子邮件通知的操作员	
已通过网络发送通知的操作员	
已通过寻呼通知的操作员	
重试次数		0

消息
已以用户 ************* 的身份执行。 从 MSrepl_commands 开始每毫秒删除 0 行 [SQLSTATE 01000] (消息 22121)  从 MSrepl_transactions 开始每毫秒删除 0 行 [SQLSTATE 01000] (消息 22121)  从 MSrepl_commands 开始每毫秒删除 0 行 [SQLSTATE 01000] (消息 22121)  从 MSrepl_transactions 开始每毫秒删除 0 行 [SQLSTATE 01000] (消息 22121)  在 670857 毫秒内删除了 1784 个由 5349 条语句组成的复制的事务(0 行/毫秒)。 [SQLSTATE 01000] (消息 21010).  该步骤成功。

初步思路

检查分发、发布相关设置；
distribution占用空间接近200G，可在维护窗口进行收缩或索引重建；
从现有的表象来看distribution所在E盘现阶段可能有IO瓶颈，如有必要可以收集一下perf；

处理步骤

1.查看分发服务器属性

因我们使用是SQL server 2017的版本，所以有【事务删除批大小】（默认5000），【命令删除批大小】（默认2000）的设置，如在2017以下的版本无界面设置就需手动在对应的发布库去调整存储过程【sp_MSdelete_publisherdb_trans】和【sp_MSdelete_dodelete】，可以使用sp_helptext 查看上述2个存储过程，然后分别把【sp_MSdelete_publisherdb_trans】代码中2000改成10000，【sp_MSdelete_dodelete】中5000改成10000；调整的原则是建议在原来参数的基础上10-100倍的调整；

2.查看各个发布属性

发布的订阅过期还是默认的永不过期设置，需调整成订阅过期间隔120小时；

如发布比较多可以使用如下脚本查看

use distribution
set transaction isolation level read uncommitted
select distinct 
srv.srvname publication_server 
, a.publisher_db
, p.publication publication_name
, p.retention
, ss.srvname subscription_server
, s.subscriber_db
from MSArticles a 
join MSpublications p on a.publication_id = p.publication_id
join MSsubscriptions s on p.publication_id = s.publication_id
join master..sysservers ss on s.subscriber_id = ss.srvid
join master..sysservers srv on srv.srvid = p.publisher_id
join MSdistribution_agents da on da.publisher_id = p.publisher_id 
and da.subscriber_id = s.subscriber_id
ORDER BY p.retention

把过期时间调整为120小时之后的查询结果如下：

3.查看publication 的【immediate sync】等参数设置

exec sp_helppublication

把所有的发布的【immediate sync】和【allow_anonymous】调整为0

EXEC sp_changepublication 
    @publication = '', -- put your publication name here 
    @property = 'allow_anonymous', 
    @value = 'false' 
GO 

EXEC sp_changepublication 
    @publication = '', -- put your publication name here 
    @property = 'immediate_sync', 
    @value = 'false' 
GO

上述3个步骤做完，待【分发清除: distribution】job成功执行一次之后（本次执行时间因磁盘的性能时长不一，我们执行了大概1小时左右），查看distribution的表情况如下