第8回では「バックアップ／リストア」についてまとめてみました。今回は SQL Serverの チェックポイント について、自分の整理も兼ねて、まとめてみようと思います。
チェックポイントについて
チェックポイント処理では、予期しないシャットダウンやクラッシュが発生した後、トランザクション ログからコミットが確定した整合性の取れる状態に復旧するためのポイントの作成が行われます。
また、パフォーマンスを向上させるため、データページ上のデータの変更はメモリ上 (バッファ キャッシュ) で変更され、データページが変更されるたびにディスクへの書き込みが行われない実装になっており、チェックポイントが行われるタイミングで、メモリ上 (バッファ キャッシュ) で変更されたデータページをディスクに書き込む (フラッシュ) 処理も行われます。
例えば、以下のクエリで テーブル tab1 に 1 レコードに挿入した場合、
| insert into tab1 (c2) values (N'TEST') go |
今回の場合、Page ID : 0001:000002cb (ファイル ID : 0001, ページ ID : 715) にデータが挿入されており、チェック ポイントが実行される前のタイミングでは、メモリ上 (バッファキャッシュ) でのみデータ ページの内容が更新されている状態になっています。
上記の状態時に、 動的管理ビュー sys.dm_os_buffer_descriptors を使用し、以下のクエリを実行すると、該当のページ (Page ID : 0001:000002cb (ファイル ID : 0001, ページ ID : 715) ) の「is_modified」列が ”1"と該当ページが更新されているが、まだディスクに書き込まれていない状態(以下 ダーティページ)になっています。
| select database_id, file_id, page_id, allocation_unit_id, page_type, is_modified from sys.dm_os_buffer_descriptors where database_id = 13 and file_id = 1 and page_id = 715 go |
※ 動的管理ビュー sys.dm_os_buffer_descriptors は、SQL Server 2019 以降で使用可能。
この状態でチェックポイント処理が行われた場合、ダーティページがディスクに書き込まれ (フラッシュ)、該当データページの「is_modified」列が ”0" に戻ります。
チェックポイントには、「自動」、「間接」、「マニュアル」、「内部」と複数の種類があります。
各チェックポイントの種類についてまとめてみようと思います。
チェックポイントの種類について
自動
recovery interval サーバー構成オプションに指定した値をもとに、 SQL Server プロセスのバックグラウンドで自動的にチェックポイントが実行されます。
本構成オプションの既定値は "0" で、データベース エンジンによって、自動的に復旧間隔が構成されます。
なお、SQL Server 2012 以降、チェックポイントのロジックが改良された「間接」チェックポイントが実装され、「自動」ではなく、「間接」チェックポイントを使用することが推奨されています。
recovery interval サーバー構成オプションの詳細は、以下の URL を参照。
間接
データベース単位で指定したターゲット復旧時間 (TARGET_RECOVERY_TIME) をもとに、SQL Server プロセスのバックグラウンドで自動的にチェックポイントが実行されます。
本構成の既定値は "1分" に設定されています。
「間接」チェックポイントでは、ダーティページ マネージャでダーティページを管理することで、チェックポイント処理の中で実行される更新されたダーティページをディスクに書き込む処理を効率的に実施し、より正確にターゲットの復旧時間内でデータベースの回復が行えるような動作が行われます。
また、「間接」チェックポイントでは、積極的にダーティページをディスクに書き込むように動作する分、処理の効率化がされているが、大量のDML操作(Update, Insert, Delete)が行われることで、ダーティページの書き込みが増大し、ディスク I/O が増大することで、全体的なパフォーマンスを低下させる要因になる可能性があります。
上記のような現象が頻繁に発生する場合は、ターゲットの復旧時間を特定の時間帯 (大量のDML操作が行われる時間帯など)のみ延ばすなどの対処が必要になるかもしれません。
なお、SQL Server 2016 では「間接」チェックポイントが既定になっており、「間接」チェックポイントを使用することが推奨されています。
ターゲット復旧時間変更の詳細については、以下の URL を参照。
マニュアル
SQL Server Management Studio (SSMS) などから手動で ”checkpoint” コマンドを実行することで、チェックポイント処理を実行します。
一般的に、SQL Server プロセスのバックグラウンドで自動的にチェックポイントが実行されるため、手動で ”checkpoint” コマンドを実行する必要はないと思います。
なお、手動で ”checkpoint” コマンド実行した場合、すべてのダーティページがディスクに書き込まれることになり、トランザクション ログの肥大化により、即座にトランザクションログの切り捨てを実施する必要がある場合などに、トランザクション ログのバックアップと組み合わせて実行することはあるかもしれません。
”checkpoint" コマンドの詳細については、以下の URL を参照。
内部
内部チェックポイントは、SQL Server 内部的に現在のログ状態とディスク上の状態を一致させる必要がある場合に、自動的に実行されるチェックポイントになります。
例えば、以下のような場合にチェックポイントが実行されます。
- ALTER DATABASE コマンドでデータベース ファイルが追加／削除された場合
- データベースのバックアップが生成された場合
- 整合性チェック (DBCC CHECKDB) でデータベース スナップショットが作成された場合
- データベースのシャットダウンが必要な動作が実行された場合
- SQL Server インスタンスを停止した場合
- SQL Server リソース (フェールオーバー クラスター インスタンス (FCI)) がオフラインになった場合
まとめ
今回は、SQL Serverの チェックポイント についてまとめてみました。SQL Server プロセスのバックグラウンドで自動的にチェックポイントが行われるため、あまり意識しなくても良いかと思いますが、チェックポイントによるダーティページのディスク書き込みにより、パフォーマンスが大きく低下するような状況が確認できた場合、ターゲット復旧時間などを調整することを検討してみると良いかと思います。
参考 URL
【第10回】基本から始める SQL Server【整合性チェック】へ
