データベースチェックポイント (SQL Server)

2015-10-13

このトピックでは、SQL Server データベースのチェックポイントの概要について説明します。 チェックポイントは、予期しないシャットダウンまたはクラッシュ後に、SQL Server データベースエンジンが復旧中にログに含まれる変更の適用を開始できる既知の適切なポイントを作成します。

チェックポイントの概要

パフォーマンス上の理由から、データベースエンジンはバッファーキャッシュ内のメモリ内のデータベースページに対して変更を実行し、変更のたびにこれらのページをディスクに書き込むことはありません。代わりに、データベースエンジンは、各データベースに対してチェックポイントを定期的に発行します。 チェックポイントは、メモリ内で変更された現在のページ (ダーティページと呼ばれます) とトランザクションログ情報をメモリからディスクに書き込み、トランザクションログに関する情報も記録します。

データベースエンジンでは、自動、間接、手動、内部という複数の種類のチェックポイントがサポートされています。次の表は、チェックポイントの種類をまとめたものです。

名前	Transact-SQL インターフェイス	説明
自動	EXEC sp_configure *'`recovery interval`','`seconds`'*	`recovery interval` サーバー構成オプションで推奨される上限を満たすために、バックグラウンドで自動的に発行されます。自動チェックポイントは最後まで実行されます。自動チェックポイントは、未処理の書き込みの数と、データベースエンジンが 20 ミリ秒を超える書き込み待機時間の増加を検出したかどうかに基づいて調整されます。詳細については、「回復間隔サーバー構成オプションの構成」を参照してください。
間接	ALTER DATABASE ...SET TARGET_RECOVERY_TIME =target_recovery_time { SECONDS \|MINUTES }	特定のデータベースのユーザー指定のターゲット復旧時間を満たすためにバックグラウンドで発行されます。既定のターゲット復旧時間は 0 です。これにより、自動チェックポイントヒューリスティックがデータベースで使用されます。 ALTER DATABASE を使用してTARGET_RECOVERY_TIMEを >0 に設定した場合、サーバーインスタンスに指定された復旧間隔ではなく、この値が使用されます。詳細については、「データベースのターゲットの復旧時間の変更 (SQL Server)」を参照してください。
マニュアル	チェックポイント [ checkpoint_duration ]	Transact-SQL CHECKPOINT コマンドを実行するときに発行されます。手動チェックポイントは、接続の現在のデータベースで行われます。既定では、手動チェックポイントは完了まで実行されます。調整は、自動チェックポイントの場合と同じように機能します。必要に応じて、 checkpoint_duration パラメーターは、チェックポイントの完了に必要な時間を秒単位で指定します。詳細については、 CHECKPOINT (Transact-SQL) を参照してください。
国内	なし。	ディスクイメージがログの現在の状態と一致することを保証するために、バックアップやデータベーススナップショットの作成などのさまざまなサーバー操作によって発行されます。

注

-kSQL Server の詳細設定オプションを使用すると、データベース管理者は、一部の種類のチェックポイントの I/O サブシステムのスループットに基づいてチェックポイント I/O 動作を調整できます。 -kセットアップオプションは、自動チェックポイントと、制限がない場合の手動チェックポイントおよび内部チェックポイントに適用されます。

自動チェックポイント、手動チェックポイント、および内部チェックポイントの場合は、データベースの復旧中に最新のチェックポイントの後に行われた変更のみをロールフォワードする必要があります。これにより、データベースの復旧に必要な時間が短縮されます。

重要

実行時間の長い未コミットのトランザクションは、すべての種類のチェックポイントの復旧時間を長くします。

TARGET_RECOVERY_TIMEと '回復間隔' オプションの相互作用

次の表は、サーバー全体の sp_configure'recovery interval' 設定とデータベース固有の ALTER DATABASE ... TARGET_RECOVERY_TIME 設定の相互作用をまとめたものです。

目標回復時間	回復間隔	使用されるチェックポイントの種類
0	0	ターゲット復旧間隔が 1 分の自動チェックポイント。
0	>0	ユーザー定義の設定であるsp_configurerecovery間隔オプションによって目標回復間隔が指定されている自動チェックポイント。
>0	適用されません。	目標復旧時間がTARGET_RECOVERY_TIME設定によって決定される間接チェックポイント (秒単位)。

自動チェックポイント

自動チェックポイントは、ログレコードの数が、 recovery interval サーバー構成オプションで指定された時間内にデータベースエンジンが処理できる数に達するたびに発生します。ユーザー定義のターゲット復旧時間がないすべてのデータベースで、データベースエンジンによって自動チェックポイントが生成されます。自動チェックポイントの頻度は、 recovery interval 高度なサーバー構成オプションによって異なります。このオプションでは、システムの再起動中に特定のサーバーインスタンスがデータベースの復旧に使用する最大時間を指定します。データベースエンジンは、復旧間隔内で処理できるログレコードの最大数を見積もります。自動チェックポイントを使用しているデータベースがこのログレコードの最大数に達すると、データベースエンジンはデータベースにチェックポイントを発行します。自動チェックポイント間の時間間隔は、非常に可変にすることができます。トランザクションワークロードが多いデータベースには、主に読み取り専用操作に使用されるデータベースよりも頻繁なチェックポイントがあります。

また、単純復旧モデルでは、ログが70パーセントいっぱいになると、自動チェックポイントもキューに入れられます。

単純復旧モデルでは、何らかの要因によってログの切り捨てが遅れている場合を除き、自動チェックポイントによってトランザクションログの未使用のセクションが切り捨てられます。これに対し、完全復旧モデルと一括ログ復旧モデルでは、ログバックアップチェーンが確立されると、自動チェックポイントによってログが切り捨てられるわけではありません。詳細については、「トランザクションログ (SQL サーバー)」を参照してください。

システムのクラッシュ後、特定のデータベースの復旧に必要な時間の長さは、クラッシュ時にダーティであったページをやり直すために必要なランダム I/O の量に大きく依存します。つまり、 recovery interval の設定は信頼できません。正確な復旧期間を判断できません。さらに、自動チェックポイントが進行中の場合、データの一般的な I/O アクティビティは大幅に増加し、予測できません。

復旧間隔が復旧パフォーマンスに与える影響

短いトランザクションを使用するオンライントランザクション処理 (OLTP) システムの場合、回復時間を決定する主な要因は recovery interval です。ただし、 recovery interval オプションは、実行時間の長いトランザクションを元に戻すために必要な時間には影響しません。実行時間の長いトランザクションを含むデータベースの復旧には、 recovery interval オプションで指定した時間を大幅に超える時間がかかる場合があります。たとえば、実行時間の長いトランザクションがサーバーインスタンスを無効にする前に更新の実行に 2 時間かかった場合、実際の復旧には、長いトランザクションを復旧するために recovery interval 値よりもかなり長い時間がかかります。実行時間の長いトランザクションが復旧時間に与える影響の詳細については、「トランザクションログ (SQL Server)」を参照してください。

通常、既定値は最適な回復パフォーマンスを提供します。ただし、回復間隔を変更すると、次の状況でパフォーマンスが向上する可能性があります。

実行時間の長いトランザクションがロールバックされていないときに、復旧に 1 分を大幅に超える時間がかかる場合。
頻繁にチェックポイントがデータベースのパフォーマンスを損なうことに気付いた場合。

recovery interval設定を増やす場合は、少しずつ増やし、各増分の増加が回復パフォーマンスに及ぼす影響を評価することをお勧めします。この方法は重要です。 recovery interval 設定が増えると、データベースの復旧が完了するまでに時間がかかるためです。たとえば、 recovery interval 10 に変更した場合、復旧の所要時間は、 recovery interval が 0 に設定されている場合よりも約 10 倍長くなります。

間接チェックポイント

SQL Server 2012 の新機能である間接チェックポイントは、自動チェックポイントの代わりに構成可能なデータベースレベルを提供します。システムがクラッシュした場合、間接チェックポイントは、自動チェックポイントよりも速く、より予測可能な復旧時間を提供する可能性があります。間接チェックポイントには、次の利点があります。

間接チェックポイント用に構成されているデータベース上のオンライントランザクションワークロードでは、パフォーマンスが低下する可能性があります。間接チェックポイントを使用すると、ダーティページの数が特定のしきい値を下回り、データベースの復旧がターゲット復旧時間内に完了します。回復間隔の構成オプションでは、ダーティページの数を使用する間接チェックポイントではなく、トランザクションの数を使用して復旧時間を決定します。多数の DML 操作を受け取るデータベースで間接チェックポイントが有効になっている場合、バックグラウンドライターはダーティバッファーのディスクへのフラッシュを積極的に開始して、復旧を実行するために必要な時間がデータベースのターゲット復旧時間セット内にあることを確認できます。これにより、特定のシステムで追加の I/O アクティビティが発生する可能性があり、ディスクサブシステムが I/O しきい値を超えて動作している場合や、I/O しきい値に近い場合にパフォーマンスのボトルネックに寄与する可能性があります。
間接チェックポイントを使用すると、REDO 中のランダム I/O のコストを考慮して、データベースの復旧時間を確実に制御できます。これにより、サーバーインスタンスは、特定のデータベースの復旧時間に上限内に留まるようにできます (実行時間の長いトランザクションで UNDO 時間が過剰になる場合を除きます)。
間接チェックポイントは、バックグラウンドでダーティページをディスクに継続的に書き込むことで、チェックポイント関連の I/O スパイクを減らします。

ただし、間接チェックポイント用に構成されたデータベース上のオンライントランザクションワークロードでは、パフォーマンスが低下する可能性があります。これは、間接チェックポイントで使用されるバックグラウンドライターによって、サーバーインスタンスの書き込み負荷の合計が増加する場合があるためです。

内部チェックポイント

内部チェックポイントは、ディスクイメージがログの現在の状態と一致することを保証するために、さまざまなサーバーコンポーネントによって生成されます。内部チェックポイントは、次のイベントに応答して生成されます。

ALTER DATABASE を使用して、データベースファイルが追加または削除されました。
データベースバックアップが作成されます。
データベーススナップショットは、DBCC CHECK に対して明示的または内部的に作成されます。
データベースのシャットダウンを必要とするアクティビティが実行されます。たとえば、AUTO_CLOSEが ON で、データベースへの最後のユーザー接続が閉じられたり、データベースの再起動が必要なデータベースオプションの変更が行われたりします。
SQL Server のインスタンスは、SQL Server (MSSQLSERVER) サービスを停止することによって停止します。どちらのアクションでも、SQL Server のインスタンス内の各データベースにチェックポイントが発生します。
SQL Server フェールオーバークラスターインスタンス (FCI) をオフラインにする。

こちらもご覧ください

トランザクションログ (SQL Server)

次の方法で共有

データベース チェックポイント (SQL Server)