SE の雑記

AlwaysOn 可用性グループの可用性モードは同期コミットと非同期コミットの 2 種類のモードがあります。
可用性モード (AlwaysOn 可用性グループ)

今回の投稿では同期コミットを使用した場合のスループットへの影響を確認するための考え方についてまとめてみたいと思います。

以前投稿した データベースミラーリングの同期モードによる遅延について と近い内容ですね。

■クエリの実行数への影響

同期コミットを使用した場合、トランザクションの流れは以下のようになります。
# TechParty で私が実施したセッションスライドからの抜粋になります。

同期コミットを使用した場合はセカンダリでトランザクションログが書き込まれ、その ACK がプライマリに返ってトランザクションが完了します。
# どのタイミングで ACK が返るかは別の投稿でまとめる予定です。

非同期コミットの場合はプライマリのトランザクションログに書き込まれれば処理が完了します。

そのため、同期コミットと非同期コミットではプライマリ / セカンダリ間の同期の分の処理差が出てくることになります。
この処理量の差が Batch Requests/sec として現れてきます。

バッチは SQL のコマンドの集まりで

という 2 つのセッションで SQL を実行した場合は 2 バッチとして数えられます。

セッション A では 1 つのステートメントで 1 バッチ、セッション B では 2 つのステートメントで 1 バッチとしてあらわされます。
# ステートメントとしてみた場合は 3 ステートメントになります。

パフォーマンスモニターでこの値を取得することで、同期モードを設定した場合の処理実行数の影響を確認することができます。
更新がかからないクエリだけ実行した場合には影響は出てこないと思いますが、更新系のクエリを含めて実行した場合、同期モードの設定前と比較してバッチの実行数が低い値を示してきます。

■トランザクションの完了への影響

次にトランザクションの完了がどれくらい遅れているのかを見ていきたいと思います。

現在、実行されているトランザクション数については Transactions で確認することができます。
そのトランザクション数に対してコミット時に待機されている時間 (ミリ秒) が Transaction Delay として確認できます。
# Transaction Delay は実行中のトランザクション数に対しての遅延の合計のため、一つのトランザクションでそれだけ遅延が発生しているということではありません。

非同期コミットの場合は、セカンダリへの書き込みの完了を待たずにトランザクションが完結しますので、Transaction Delay は 0 となります。

同期コミットの場合は、セカンダリへの書き込みが完了するまで待機する必要がありますので Transaction Delay が発生してきます。プライマリとセカンダリ間でネットワークの遅延が恒常的に発生している場合などは、その遅延が更新系の処理にダイレクトに影響してくることになります。
可用性グループでは複数のセカンダリを設定し、同期コミットと非同期コミットのレプリカを混在できますので、同期コミットを使用する拠点間はできるだけ遅延が発生しないネットワークに配置し、ネットワーク遅延が発生する拠点に関しては非同期コミットを使用する等の設計が必要になってくるかと思います。

Transaction Delay 以外にもトランザクションの完了へどれだけ影響を及ぼしているかを確認するための方法があります。
それが、HADR_SYNC_COMMIT になります。
これは、sys.dm_os_wait_stats の項目になるのですが、この項目を確認することでもトランザクションの遅延に対しての影響を確認することができます。
先ほどの Transaction Delay と同様、非同期コミットの場合は数値は増加しないのですが、同期コミットを設定した場合は増加していきます。
sys.dm_os_wait_stats はサービスの再起動や明示的なクリア (DBCC SQLPERF) をしない限り加算されていきますので、サービスを起動してからどれくらい遅延が発生したかを確認することもできます。
# ただし、これは全トランザクションの遅延の合計になりますので、単純に数値の時間だけ直列に遅延が発生したということではありません。

同期モードを使用した場合のスループットへの影響はこれらの値を参考にすると見えてくるかと思います。