データベース チェックポイント (SQL Server)
適用対象: 
SQL Server Azure SQL データベース
"チェックポイント" によって、予期しないシャットダウンやクラッシュの後の復旧中に、ログに格納されている変更を SQL Server データベース エンジンが適用するための最適なポイントが作成されます。
概要
パフォーマンス上の理由から、データベース エンジンでは、メモリ内のバッファー キャッシュにあるデータベース ページが変更されるようになり、ページが変更されるたびにディスクに書き込まれることはなくなりました。 代わりに、データベース エンジンでは定期的に各データベースに "チェックポイント" が発行されます。 チェックポイントでは、現在メモリにある修正ページ (つまり "ダーティ ページ") とトランザクション ログ情報がメモリからディスクに書き込まれ、トランザクション ログの情報も記録されます。
データベース エンジンでは、自動、間接、手動、内部などいくつかのチェックポイントの種類がサポートされています。 次の表は、チェックポイントの種類をまとめたものです。
| Name | Transact-SQL インターフェイス | 説明 |
|---|---|---|
| 自動 | EXEC sp_configure 'recovery interval', 'seconds' | recovery interval サーバー構成オプションに指定された期限に合わせて、バックグラウンドで自動的に発行されます。 自動チェックポイントは、最後まで実行されます。 自動チェックポイントは、未処理の書き込み数と、50 ミリ秒を超える書き込み待機時間の上昇をデータベース エンジンが検出したかどうかに応じて調整されます。 詳細については、「 recovery interval サーバー構成オプションの構成」を参照してください。 |
| INDIRECT | ALTER DATABASE ... SET TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES } | 所定のデータベースのユーザーが指定したターゲット復旧時間に合わせて、バック グラウンドで発行されます。 SQL Server 2016 (13.x) 以降、既定値は 1 分です。 旧バージョンの既定値は 0 です。これは、データベースが自動チェックポイントを使用することを示し、その頻度はサーバー インスタンスの復旧間隔の設定に依存します。 詳細については、「データベースのターゲットの復旧時間の変更 (SQL Server)」を参照してください。 |
| [手動] | CHECKPOINT [ checkpoint_duration ] | Transact-SQL CHECKPOINT コマンドを実行すると発行されます。 接続している現在のデータベースで手動チェックポイントが作成されます。 既定では、手動のチェックポイントは最後まで実行されます。 調整は自動チェックポイントの場合と同様に行われます。 必要に応じて、 checkpoint_duration パラメーターを使用し、チェックポイントを完了するのに必要な時間を秒単位で指定します。 詳細については、「CHECKPOINT (Transact-SQL)」を参照してください。 |
| 内部 | なし | ディスク イメージがログの現在の状態と一致することを保証するために、バックアップやデータベース スナップショット作成など、さまざまなサーバー操作によって発行されます。 |
一部の種類のチェックポイントでは、データベース管理者が -k SQL Server の詳細設定オプションを使用して、I/O サブシステムのスループットに基づいてチェックポイントの I/O 動作を調整できます。 -k 設定オプションは、自動チェックポイントと、その他のスロットルのない手動チェックポイントおよび内部チェックポイントに適用されます。
自動チェックポイント、手動チェックポイント、および内部チェックポイントでは、データベース復旧中にロールフォワードする必要があるのは、最新チェックポイントの後で行われた修正のみです。 これにより、データベースの復旧にかかる時間が短縮されます。
重要
実行時間の長い、コミットされていないトランザクションがあると、すべての種類のチェックポイントで復旧時間が長くなります。
TARGET_RECOVERY_TIME と 'recovery interval' オプションの相互作用
次の表は、サーバー全体の sp_configure 'recovery interval' 設定とデータベース固有の ALTER DATABASE ... TARGET_RECOVERY_TIME 設定の間の相互作用をまとめたものです。
| target_recovery_time | 'recovery interval' | 使用されるチェックポイントの種類 |
|---|---|---|
| 0 | 0 | ターゲット復旧間隔が 1 分の自動チェックポイント |
| 0 | > 0 | ターゲット復旧間隔が sp_configure 'recovery interval' オプションのユーザー定義設定によって指定されている自動チェックポイント。 |
| > 0 | 適用なし | 復旧時間が TARGET_RECOVERY_TIME 設定 (秒単位) によって設定されている間接チェックポイント ターゲット |
自動チェックポイント
自動チェックポイントが発生するのは、ログ レコード数が、recovery interval サーバー構成オプションで指定された時間内に処理できるとデータベース エンジンによって推定された数に達したときです。 詳細については、「 recovery interval サーバー構成オプションの構成」を参照してください。
ユーザー定義のターゲット復旧時間が設定されていないすべてのデータベースでは、データベース エンジンによって自動チェックポイントが生成されます。 自動チェックポイントの作成回数は、 recovery interval 詳細サーバー構成オプションに応じて異なります (これは、システムの再起動時に、所定のサーバー インスタンスがデータベースの復旧にかけられる最大時間を指定します)。 データベース エンジンは、復旧間隔内に処理できるログ レコードの最大数を推定します。 自動チェックポイントを使用するデータベースのログ レコードが最大数に達すると、データベース エンジンによってデータベースでチェックポイントが発行されます。
自動チェックポイントの作成間隔は 大幅に 変わります。 相当量のトランザクション ワークロードがあるデータベースでは、主に読み取り専用操作で使用されるデータベースよりもチェックポイントの作成回数が多くなります。 単純復旧モデルの場合、ログが全体の 70% に達すると自動チェックポイントもキューに登録されます。
単純復旧モデルでは、ログ トランザクションが何かの要因によって遅れていない限り、自動チェックポイントにより、トランザクション ログの未使用のセクションが切り捨てられます。 一方、完全復旧モデルおよび一括ログ復旧モデルでは、ログ バックアップ チェーンが確立されると、自動チェックポイントによるログの切り捨ては行われなくなります。 詳細については、「トランザクション ログ (SQL Server)」を参照してください。
システム障害の後で所定のデータベースの復旧に必要な時間は、障害発生時点でのダーティ ページを再実行するために必要なランダム I/O の容量に大きく依存します。 つまり、 recovery interval の設定は信頼できません。 正確な復旧間隔がわかりません。 さらに、自動チェックポイントの進行中に、データの一般的な I/O アクティビティが著しく増加します。
復旧のパフォーマンスに対する復旧間隔の影響
短いトランザクションを使用するオンライン トランザクション処理 (OLTP) システムでは、 recovery interval の設定が復旧にかかる時間を決定する主な要因になります。 ただし、recovery interval オプションは、実行時間が長いトランザクションを元に戻すために必要な時間には影響しません。 実行時間が長いトランザクションが行われたデータベースの復旧は、recovery interval 設定で指定された時間よりも長くかかることがあります。
たとえば、実行時間の長いトランザクションが 2 時間かけて更新した後にサーバー インスタンスが使用不能になった場合、長いトランザクションを復旧することになるので、実際の復旧時間は recovery interval 値よりもはるかに長くなります。 実行時間が長いトランザクションの復旧時間への影響の詳細については、「トランザクション ログ (SQL Server)」を参照してください。 復旧プロセスの詳細については、「復元と復旧の概要 (SQL Server)」を参照してください。
通常は、既定値によって復旧の最適なパフォーマンスが得られます。 ただし、次のような状況では recovery interval を変更するとパフォーマンスが向上する可能性があります。
実行時間の長いトランザクションがロールバックされていないときで、復旧にかかる時間が常に 1 分を超える場合
チェックポイントの回数が多いためにデータベースのパフォーマンスが損なわれていると判断できる場合
recovery interval 設定を長くする場合には、少しずつ値を増やして、そのたびに復旧のパフォーマンスへの影響を評価することをお勧めします。 recovery interval の設定を長くすると、完了するまでに何倍もの時間がかかるため、この方法で進めることが重要です。 たとえば、recovery interval の値を 10 分に変更すると、復旧には recovery interval が 1 分に設定された場合の約 10 倍かかります。
間接チェックポイント
SQL Server 2012 (11.x) に導入された間接チェックポイントは、自動チェックポイントの代わりに使用できる、構成可能なデータベース レベルのチェックポイントです。 これは、ターゲットの復旧時間データベース構成オプションを指定して構成できます。 詳細については、「データベースのターゲットの復旧時間の変更 (SQL Server)」を参照してください。 間接チェックポイントでは、自動チェックポイントに比べて、システム障害時の復旧時間が短く、予測可能です。
間接チェックポイントには次の利点があります。
間接チェックポイントは、ターゲットの復旧時間内でデータベースの回復が完了するように、ダーティ ページの数が特定のしきい値を下回るようにします。
復旧間隔構成オプションでは、ダーティ ページ数を使用する間接チェックポイントとは異なり、トランザクション数を使用して復旧時間を決定します。 DML 操作の受信数が多いデータベースで間接チェックポイントが有効な場合、バックグラウンド ライターは積極的にディスクにダーティ バッファーのフラッシュを開始し、回復を実行するのに必要な時間をデータベースのターゲット復旧時間内にすることができます。 これにより、ディスクのサブシステムが I/O のしきい値を超えて、または近くで動作する場合にパフォーマンス ボトルネックの原因となる、追加の I/O アクティビティが特定のシステムで発生する可能性があります。
間接チェックポイントを使用すると、REDO 時のランダム I/O のコストを計算に入れて、データベースの復旧時間を確実に制御できます。 このため、所定のデータベースで、復旧時にサーバー インスタンスが上限内に留まることができます (実行時間の長いトランザクションによって過度の UNDO 回数が生じる場合以外)。
間接チェックポイントでは、ディスクへのダーティ ページの書き込みがバックグラウンドで続けられるため、チェックポイントに関連する I/O の急上昇が少なくなります。
ただし、間接チェックポイントが構成されたデータベースでオンライン トランザクション ワークロードが生じると、パフォーマンスが低下することがあります。 これは、間接チェックポイントで使用されるバックグラウンド ライターによって、サーバー インスタンスの合計書き込みロードが増える場合があるためです。
重要
間接チェックポイントは、model や tempdb データベースを含む SQL Server 2016 (13.x) で作成された新しいデータベースの既定の動作です。
インプレース アップグレードまたは以前のバージョンの SQL Server から復元されたデータベースは、間接チェックポイントを使用するように明示的に変更しない限り、以前の自動チェックポイントを使用します。
間接チェックポイントのスケーラビリティの向上
SQL Server 2019 (15.x) より前のバージョンでは、tempdb のように多数のダーティ ページを生成するデータベースがあると、応答停止スケジューラ エラーが発生することがあります。 SQL Server 2019 (15.x) では間接チェックポイントの向上したスケーラビリティが導入されており、UPDATE/INSERT のワークロードが大きいデータベースでのエラー回避に役立ちます。
内部チェックポイント
内部チェックポイントは、ディスク イメージがログの現在の状態と一致することを保証するために、さまざまなサーバー コンポーネントによって生成されます。 内部チェックポイントは、次のイベントに応答して生成されます。
ALTER DATABASE を使用して、データベース ファイルが追加または削除された場合。
データベースのバックアップが作成された場合。
DBCC CHECKDB で明示的または内部的に、データベース スナップショットが作成された場合。
データベースのシャットダウンが必要な動作が実行された場合。 たとえば、AUTO_CLOSE が ON に設定されていて、データベースへの最後のユーザー接続が終了した場合、またはデータベースの再起動が必要なデータベース オプションが変更された場合です。
SQL Server (MSSQLSERVER) サービスを停止して、SQL Server のインスタンスが停止された場合。 このアクションにより、SQL Server のインスタンスの各データベースにチェックポイントが作成されます。
SQL Server フェールオーバー クラスター インスタンス (FCI) がオフラインになった場合。