sqlserver AlwaysOn同步流程与延迟

区分异步同步提交的关键就在 Log Hardened 这一步，主副本是否需要确认辅助副本已经完成日志固化后才能提交事务。

异步提交模式

主副本无须确认该辅助副本是否已经完成日志固化，就可以提交事务。但是，辅助数据库的更新可能会滞后于主数据库，如果发生故障转移，可能会导致某些数据丢失。

同步提交模式

主副本必须确认辅助副本已经完成日志固化才可以提交事务，这样就保证从库不会有数据丢失，但是这种保障的代价是主数据库上的事务提交会有滞后时间。另外注意，同步模式保证的是从库不会有数据丢失，而不是保证主从同步没有延迟！！

仅配置模式

SQL Server 2017 CU1 的新功能，适用于不在WSFC上的可用性组。仅配置模式下的副本不包含用户数据，副本主数据库存储可用性组配置元数据。

通过扩展事件跟踪，我们可以知道日志块移动的每个步骤，并且可以确切地知道事务延迟来自何处。

通常，延迟来自三个部分：

主库日志固化的持续时间：它等于Log_flush_start（步骤2）和Log_flush_complete（步骤3）的时间之和。
从库日志固化的持续时间：它等于Log_flush_start（步骤10）和Log_flush_complete（步骤11）的时间之和。
网络传送的持续时间 ：primary：hadr_log_block_send_complete-> secondary：hadr_transport_receive_log_block_message（步骤6-7）和（secondary：hadr_lsn_send_complete-> primary：hadr_receive_harden_lsn_message（步骤12-13）的时间之和
扩展事件创建脚本如下：

/* Note: this trace could generate very large amount of data very quickly, depends on the actual transaction rate. On a busy server it can grow several GB per minute, so do not run the script too long to avoid the impact to the production server. */

CREATE EVENT SESSION [AlwaysOn_Data_Movement_Tracing] ON SERVER ADD EVENT sqlserver.file_write_completed, ADD EVENT sqlserver.file_write_enqueued, ADD EVENT sqlserver.hadr_apply_log_block, ADD EVENT sqlserver.hadr_apply_vlfheader, ADD EVENT sqlserver.hadr_capture_compressed_log_cache, ADD EVENT sqlserver.hadr_capture_filestream_wait, ADD EVENT sqlserver.hadr_capture_log_block, ADD EVENT sqlserver.hadr_capture_vlfheader, ADD EVENT sqlserver.hadr_db_commit_mgr_harden, ADD EVENT sqlserver.hadr_db_commit_mgr_harden_still_waiting, ADD EVENT sqlserver.hadr_db_commit_mgr_update_harden, ADD EVENT sqlserver.hadr_filestream_processed_block, ADD EVENT sqlserver.hadr_log_block_compression, ADD EVENT sqlserver.hadr_log_block_decompression, ADD EVENT sqlserver.hadr_log_block_group_commit , ADD EVENT sqlserver.hadr_log_block_send_complete, ADD EVENT sqlserver.hadr_lsn_send_complete, ADD EVENT sqlserver.hadr_receive_harden_lsn_message, ADD EVENT sqlserver.hadr_send_harden_lsn_message, ADD EVENT sqlserver.hadr_transport_flow_control_action, ADD EVENT sqlserver.hadr_transport_receive_log_block_message, ADD EVENT sqlserver.log_block_pushed_to_logpool, ADD EVENT sqlserver.log_flush_complete , ADD EVENT sqlserver.log_flush_start, ADD EVENT sqlserver.recovery_unit_harden_log_timestamps ADD TARGET package0.event_file(SET filename=N'c:\mslog\AlwaysOn_Data_Movement_Tracing.xel',max_file_size=(500),max_rollover_files=(4)) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
下面是捕获的xevent截图

主库：
 

从库：

您可能会注意到hadr_receive_harden_lsn_message的log_block_id（146028889512）与其他（146028889488）不同。这是因为返回ID始终是强化日志块的下一个即时ID，我们可以使用hadr_db_commit_mgr_update_harden xevent来关联xevent。 

通过上面的xevent日志，可以获得事务提交的详细时间延迟：

为了获得总的事务延迟，我们不能简单地将它们汇总起来，因为主数据库上的日志刷新和网络传输是并行进行的。假设网络需要4.494秒，但主要日志硬化完成（log_flush_complete：2018-03-06 16：56：28.8785928）远早于主要数据库从副本获得确认（hadr_receive_harden_lsn_message：2018-03-06 16：56：33.3732126）。幸运的是，我们不需要手动确定要使用哪个时间戳来计算事务的总提交时间。我们可以使用两个hadr_log_block_group_commit xevents之间的时间差来知道提交时间。

例如，在上面的日志中：

主库hadr_log_block_group_commit：2018-03-06 16：56：28.2167393
主库hadr_log_block_group_commit：2018-03-06 16：56：33.3732847
提交的总时间=两个时间之差= 5.157秒
该数字等于网络传输时间加上辅助节点上的日志固化时间。这是有道理的，因为辅助数据库必须等待网络等待可用的日志块，然后再进行日志固化，它无法像主数据库中那样并行固化日志。

查看从库hadr_log_block_group_commit事件，可以看到该事件有个processing_time列，该列正是我们所讨论的事务的提交时间：
 

顺便说一下，您可能会注意到在主要Xevents中发生了“ hadr_db_commit_mgr_harden_still_waiting” xevent。当主库正在等待来自从库确认消息时，此事件每2秒（硬编码2秒）发生一次。如果确认在2秒内返回，您将不会看到此xevent。

本文来源https://www.modb.pro/db/610544