今回は [データベースの整合性確認の負荷] について振り返っていきたいと思います。
前回の振り返りでまとめていたデータベースの整合性確認 (DBCC CHECKDB) ですが、データベースの状態を確認するためには大事になる処理ですが、整合性を確認するためには負荷がかかります。
■内部データベース スナップショット
データベースの整合性確認ですが、SQL Server 2005 以降は、整合性を確認するデータベースに直接実行されるのではなく、基本的には内部データベース スナップショットを取得し、スナップショットのデータベースに対して実行されます。
# SQL Server 2000 では実 DB にスキーマロックをかけていたはずです。軽く試したところ、インデックス / テーブルにインテント共有ロックがかかっていたので、同時実行性を落とさないように考慮されたロックがかかっていました。
以下の条件の場合は、SQL Server 2005 以降でも実データベースに対して整合性チェックが行われます。
- master に対して実行する場合で、SQL Server インスタンスがシングル ユーザー モードで実行されている場合。
- master 以外のデータベースに対して実行する場合で、データベースが ALTER DATABASE ステートメントによってシングル ユーザー モードに設定されている場合。
- 読み取り専用データベースに対して実行する場合。
- ALTER DATABASE ステートメントによって緊急モードに設定されたデータベースに対して実行する場合。
- tempdb に対して実行する場合。この場合、内部的な制約のため、データベース スナップショットは作成できません。
- WITH TABLOCK オプションを使用する場合。この場合、DBCC ではデータベース スナップショットは作成されず、要求が受け入れられます。
SQL Server 2005 以降で実装された機能として [データベース スナップショット] という機能があります。
スパース ファイルを利用して、取得時点のデータベースの静的イメージを作成して、そのイメージを使用して検索 / そのタイミングに戻すことができる機能になります。
スナップショットの作成は T-SQL で行います。
|
CREATE DATABASE [TEST2_Snapshot] ON |
上記は、[TEST2] というデータベースのスナップショットを作成するクエリになります。
スナップショットを作成すると、SSMS のデータベース スナップショットに作成したスナップショットが表示されます。
作成したスナップショットは読み取りデータベースのように使用することができます。 
基本的な考えは、Hyper-V 等の差分ディスクと同じでスナップショットを取得した元データベースのデータが変更されたら、変更前のデータをスナップショット側に保存して取得時データを保障するという形になります。
そのため取得した直後の状態では、データは入っておらず元データベースのデータが変更されたタイミングでデータが押し出されてきます。
# 必ず親データベースが必要になりますので、バッチ実行前のバックアップといった瞬間的に利用するバックアップの代用にはなりますが恒久的なバックアップとしての利用は原則しないほうがよいです。
DBCC CHECKDB の内部 データベースショットも、このようなデータベース スナップショットがファイルとして取得されて実行されているのかというと、内部という名称がついているだけあり通常のデータベース スナップショットとは異なった動きをします。
# DBCC CHECKDB のコマンドのオプションにはスナップショットで使用するスパースファイルを指定するオプションはありません。
DBCC CHECKDB が事項されている際のデータページの状態を [sys.dm_os_buffer_descriptors] から確認してみたいと思います。
|
SELECT |
DBCC CHECKDB を実行する前は、1～6 までのデータベースが存在していることが確認できます。
# 32767 は [Resources] なので、以降は特に触れないでおきます。
DBCC CHECKDB を実行している最中に同様のクエリを実行してみます。 
今回は [TEST2] というデータベースに CHECKDB を実行しています。
SQL Server のデータベース名はインスタンス内で一意になる必要がありますが、CHECKDB を実行している最中は [TEST2] というデータベースが 2 個 (database_id 6 / 7 のデータベース) 存在しているのが確認できます。
同一の名称で新規に作成されたデータベースが、[内部データベース スナップショット] となります。
■内部データベース スナップショットの作成場所
[sys.dm_os_buffer_descriptors] の情報から内部データベース スナップショットが取得されているのが確認できましたので、今度はどこに作成されているかを確認してみたいと思います。
DBCC CHECKDB の実行中に [fn_virtualfilest
ats] を検索して、ファイル I/O が発生しているかを見てみたいと思います。
|
SELECT |
ファイルとしては存在していないようですね。
DBCC CHECKDB 実行前後の [TEST2] fn_virtualfilestats の情報も比較してみたいと思います。 
上が実行前、下が実行後になります。
読み取り (NumberReads / BytesRead) は増加していないですね。
続いて、パフォーマンスモニタでデータベースを格納しているドライブのディスク I/O も見てみたいと思います。 
fn_virtualfilestats の情報では TEST2 データベースを構成するファイルに対しての読み取りは見えませんでしたがデータファイルを格納しているドライブの I/O としてみるとかなりの読み取りが発生しているのが確認できますね。
内部データベース スナップショットといってもスナップショットもベースとなるデータは物理データファイルですので読み取り操作は発生します。
fn_virtualfilestats の読み取りとして上がってきていないのは、読み取りを実行している対象が実データベースではなく、内部データベース スナップショットとして取得したデータベースに対して実行しているからだと思います。
# この辺の動きが書かれているドキュメントを探してみたのですが見当たりませんでした…。
次に、メモリの状態を確認してみたいと思います。 
今回は 400MB を max server memory として設定しているため、トータルページは 400 MB になった状態で推移しています。
データページがデータベースのデータをキャッシュする用途で使用しているメモリ領域、ワークスペースページがソート等の一時領域として使用しているメモリ領域となります。
データページ / ワークスペースページが増減しているタイミングが DBCC CHECKDB を開始 / 終了したタイミングとなります。
DBCC CHECKDB を実行している最中は、データ / ワークスペースページを使用して処理を実行していますが、処理が終了するとフリーページとして使用していたメモリを解放していることが確認できます。
今回は 4GB のデータベースに対して、DBCC CHECKDB を実行しているのですが、意図的にメモリを 400MB に制限をしていました。
使用できるメモリが少ないため、ワークスペースページが 70MB 程度の値で推移していますが、4GB のデータで使用するにはかなり少ないかと思います。
これですが、メモリで足りない分は [tempdb] が使用されています。
先ほどのディスク使用状況に tempdb のドライブも追加してみたいと思います。
# 今回の環境は データ / ログ /tempdb という単位で独立したドライブを使用しています。
tempdb が使用されていることが確認できますね。
[fn_virtualfilestats] [sys.dm_db_file_space_usage] からも tempdb の使用状況を見てみたいと思います。 
これらの情報からも tempdb が使用されていることが確認できます。
tempdb では、[internal_object_reserved_page_count] (単一エクステントの内部オブジェクト) として格納されているようですね。
メモリを十分に割り当てられる場合は以下のようなグラフとなります。 
メモリが十分に使用できる場合は、tempdb は最後の方に使用がされているだけで、それ以外のタイミングでは書き込みは発生していません。
# ワークスペース領域の内容を最後に書きだしているのかと思いますが。
■Standard Edition でも使用できるか
データベース スナップショットは Enterprise Edition 以上で使用できる機能になります。
Standard Edition でデータベース スナップショットを取得しようとすると、以下のエラーが発生します。
|
メッセージ 1844、レベル 16、状態 1、行 1 |
このメッセージは通常のデータベース スナップショットに対してのメッセージで内部データベース スナップショットについては Standard Edition でも使用することが可能です。 
DBCC CHECKDB の負荷ですが、
- メモリ
- ディスク読み込み
- tempdb
の 3 箇所を考慮する必要があります。
データベースのサイズによって、全体的な負荷は変わりますので想定されるデータサイズにして一度試した方が良いかと。
負荷の状況によっては、DBCC CHECKDB を [PHYSICAL_ONLY] で実行、BACKUP DATABASE を [CHECKSUM オプション] をつけて実行等で整合性を確認するのもよさそうですね。
バックアップ / 整合性確認についてまとめてきましたが、この後にはリストアについてもお話がありました。
次の投稿では、リストアについてまとめていきたいと思います。