SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server 2012 のインダイレクトチェックポイントについて

leave a comment

SQL Server 2012 ではインダイレクトチェックポイント (Indirect Checkpoint : 間接的なチェックポイント) という機能が追加されています。

twitter でパスがあったので少しまとめてみたいと思います。

技術情報としては以下の情報が参考になります。
データベース チェックポイント (SQL Server)
Change the Target Recovery Time of a Database (SQL Server)

■チェックポイント発生頻度の制御


まずはチェックポイントのおさらいから。

SQL Server ではデータの変更を行った場合、ログファイルに変更内容を先行書き込みし、メモリ上のデータを変更します。
image

この状態ではディスク上のデータファイルは変更がされていません。
この状態でサーバーがシャットダウンされると、ログファイルには変更の内容が書き込まれており、データファイルには変更の内容が書き込まれていない状態となります。

この状態ではデータファイルと更新状況に不整合が発生しますので、次回の起動時のログファイルの内容を使用して、ロールフォワードが行われます。

メモリ / ログファイルにのみ変更がされている状態では、起動のたびにロールフォワードが必要になり、起動に時間がかかってしまいます。
また、メモリのサイズはディスクのサイズより小さいですので、変更された内容を永続的に格納しておくのは難しいです。

そのため、SQL Server ではバックグラウンドで定期的にチェックポイントを発生させて、メモリ上でのみ変更されておりデータファイルには反映されていないデータをデータファイルに書きだすという処理が行われています。
image

WinDbg で確認するとチェックポイント用のスレッドが常駐していることが確認できます。
image

sysprocess では、lastwaittype が [CHECKPOINT_QUEUE] になっているものがチェックポイント用のセッションになるかと。
# タイミングによっては、[ASYNCIO_COMPLETION] になっているかもしれません。
image

従来までの SQL Server (SQL Server 2008 R2 まで) ではこのチェックポイントを発生する方法として、

  1. 自動チェックポイント
    サーバーオプションの [recovery interval (min)] の設定内容 (GUI 上は復旧間隔) に応じて定期的に自動でチェックポイントを発生
    image
  2. 手動チェックポイント
    CHECKPOINT を手動で実行

の二種類の方法を使用して、チェックポイントを発生させることができました。

自動チェックポイントに関してはインスタンスレベルの設定になりますので、インスタンスに含まれる全データベースに対しての設定値となっていました。
そのため、個々のデータベースに対して、自動チェックポイントの発生間隔を制御するということができませんでした。
チェックポイントはメモリ上にのみ更新されているデータをデータファイルに書き戻すという処理が行われるため、ディスクの I/O が発生することになるのですが、従来までの SQL Server では自動チェックポイントの発生間隔を個々のデータベースで独自に設定するということができなかったため、データの更新が頻繁に行われるデータベースではチェックポイントの発生頻度を少なくするという設定をすることができませんでした。

 

■インダイレクトチェックポイントによるデータベース単位の自動チェックポイントの発生頻度の制御


SQL Server 2012 ではインダイレクトチェックポイントという機能が追加されました。
この設定をすることでデータベース単位で自動チェックポイントの発生頻度を調整することが可能になります。
# チェックポイント自体のアルゴリズムも通常の自動チェックポイントと違っているそうなのですが、この点はまだ勉強ができていません。

SQL Server 2012 では、データベースのプロパティに [ターゲットの復旧間隔 (秒)] (TARGET_RECOVERY_TIME) という設定が追加されています。
image

この設定がインダイレクトチェックポイントの設定になります。
デフォルトでは [0] が設定されており、0 の場合はインスタンスレベルの復旧間隔 (recovery interval) の設定値によって自動チェックポイントが発生します。

それでは実際に設定による影響を確認してみたいと思います。

まずは

  • 復旧間隔 (recovery interva) l = 0
  • DB1 / DB2 というデータベースを作成し、ターゲットの復旧時間 (TARGET_RECOVERY_TIME) = 0

という設定にしてあります。

この状態で各データベースで

INSERT INTO Table_1 VALUES (NEWID())
GO 1000

という単純なクエリを実行して各データベースで何回チェックポイントが発生するかを確認してみたいと思います。
image

拡張イベントを使用して、DB1 (DB_ID = 9) / DB2 (DB_ID = 10) で何回チェックポイントが発生するかを確認した内容が上の画像になります。
# 各データベースで同じクエリを流していたのでチェックポイントの発生回数が一致するかと思ったのですが、そうでもなかったです…。復旧モデルが単純相当だったからうまく動作を制御できていなかったのかもしれないですね。

各データベースで 10 回以上チェックポイントが発生しているのが確認できますね。

それでは、この状態で DB2 のターゲットの復旧時間を変更してみたいと思います。
今回は 3600 秒 = 1 時間を設定してみました。
image

先ほどと同じクエリを実行してチェックポイントの発生回数を見るとターゲットの復旧時間を変更した DB2 ではチェックポイントの発生回数が減少していることが確認できます。
image
これがターゲットの復旧時間を変更したことによる影響になるようです。

従来までの SQL Server ではインスタンスレベルでしか設定ができませんでしたので、データの更新が多いデータベースに関しては自動チェックポイントの発生頻度を抑えるというような細かな調整が行えませんでした。

SQL Server 2012 では、データの更新量を考慮して個々のデータベースに対して自動チェックポイントの発生頻度を調整するということができるようになるようです。

データの更新が頻繁に発生するデータベースをインスタンス内に複数持っている場合は、この設定を使用することで、チェックポイントによる I/O 調整が出来そうですね。

Written by masayuki.ozawa

1月 2nd, 2012 at 2:18 pm

Posted in SQL Server

Tagged with ,

Leave a Reply

*