SQL に関する Q&A: データベースの圧縮、拡張、および再設計など
一言で SQL Server のデータベースと言っても、形、サイズ、スキーマは千差万別です。今月のコラムでは、SQL Server の専門家が、データベースの圧縮、拡張、および再設計について役立つ情報を提供します。
Paul S. Randal
データベースを大幅に圧縮する
Q. ディスクの空き容量が不足しているため、パフォーマンス上の問題を引き起こす可能性があるとわかっていても、データベースを圧縮せざるを得ない場合があります。圧縮後に、インデックスの断片化に対処しています。データベースによっては、サイズが同じくらいであるにもかかわらず、他のデータベースよりも、圧縮にかかる時間が大幅に長いと感じる理由を教えていただけませんか。
A. データベースの圧縮を実行することによる副作用をご存じで、安心しています。また、私も、データベースを圧縮せざるを得ない場合があることは理解しています。
データベースの同時操作とデータベースに含まれるテーブルのスキーマは、データベースの圧縮操作の実行時間に影響を与えることがあります。つまり、同じサイズであってもスキーマが異なる 2 つのデータベースでは、圧縮にかかる時間が大きく異なります。
圧縮を実行するには、データ ファイル ページを移動して、ファイルの最後に空き領域をまとめます。(データ ファイルのサイズを縮小することで得た) この空き領域は、ファイル システムに返されます。データ ファイル ページを移動するには、SQL Server では、ページ上で排他ロックをかける必要があります。つまり、他のユーザーは、そのページに対してロックや書き込みを行うことができません。データベースで、ロックを伴う同時操作が発生した場合、圧縮操作はブロックされ、ロックをかけられるようになるまで待機する必要があります。このような状況が発生すると、圧縮に必要な時間は、データベースで他の操作が行われていない場合よりも長くなります。
圧縮時にデータ ファイル ページを移動する際のもう 1 つの考慮事項としては、他のデータベース構造に、移動するページ上のデータへの物理的なポインターが含まれている場合、この物理的なポインターを新しいページの場所に更新する必要があります。これは問題ではありませんが、テーブルがヒープの場合 (クラスター化インデックスを含まない場合)、または行外 (テーブル データ レコードとは別の場所) に格納されているラージ オブジェクト (LOB) 列が 1 つ以上テーブルに含まれる場合、あるいはその両方の場合は例外です。
テーブルがヒープの場合、ヒープに格納されているすべての非クラスター化インデックスには、テーブル データ レコードへの物理的なポインターが含まれます。圧縮により、テーブルからデータ ページが移動した場合は、非クラスター化インデックスを更新する必要があります。SQL Server ではクエリ プロセッサを呼び出し、一度に 100 行の非クラスター化インデックスに対してインデックス メンテナンスを実行して、インデックスを更新します。
テーブルに行外に格納されている LOB データが含まれる場合、データ レコードは行外の LOB データをポイントします。ただし、LOB データからデータ レコードへのバック ポインターはありません。つまり、圧縮により、(行外の LOB データを含む) テキスト ページが移動したら、そのページ上にある LOB データをポイントする、すべてのデータ レコードを更新する必要があります。バック ポインターがないので、テーブル スキャンを実行して、更新する適切なデータ レコードを特定する必要があります。ご想像どおり、多数の LOB データを含むテーブルでは、このプロセスは非常に低速になります。
圧縮には時間がかかることもありますが、SQL Server 2005 以降では、sys.dm_exec_requests 動的管理ビューの percent_complete 列で、進行状況レポートが提供されるようになりました。また、Databases パフォーマンス オブジェクトの Shrink Data Movement Bytes/sec パフォーマンス カウンターを監視して、圧縮の処理速度を確認することもできます。
自動拡張を実行する
Q. 私は新米のデータベース管理者で、データベース設定のベスト プラクティスについて、オンラインでたくさんの資料を読んでいるところです。自動拡張は有効にすべきであるかどうかについて、相反する見解に困惑しています。問題を起こさずに、自動拡張を無効にできますか。
A. 簡単にお答えすると、自動拡張は有効にする必要がありますが、この機能に依存しないようにしてください。通常は、データ ファイルとトランザクション ログ ファイルのサイズと使用状況を監視して、これらを事前に拡張します (または、予期しない突然の拡張が行われた場合は、その原因を調査します)。ファイルのサイズと使用状況を管理している担当者が、すぐにファイルを管理できる状況ではないという緊急時に備えて自動拡張を有効にします。
トランザクション ログ ファイルに対して自動拡張が有効になっていない場合に、ファイルがいっぱいになると、トランザクション ログで使用可能な領域が確保されるまで、データベースに対する書き込み操作は実行できなくなります。また、データ ファイルに対して自動拡張が有効になっていない場合、挿入操作やインデックスの再構築などのデータベース メンテナンス操作は、失敗することがあります。
難しいのは、自動拡張の設定を理解することです。SQL Server 2005 以降では、トランザクション ログ ファイルの既定の自動拡張はファイルの 10%、データ ファイルの既定の自動拡張は 1 MB に設定されています。ただし、割合ベースの自動拡張では、ファイルが拡張されると、自動拡張される領域も増加することになります。つまり、ファイルの瞬時初期化が有効になっていないと、自動拡張にかかる時間も増加します。そのため、どちらのファイルでもサイズを指定した自動拡張を利用することで、自動拡張の動作が予測可能になります。
かなり大規模な自動拡張または割合ベースの自動拡張を使用すると、特に、トランザクション ログ ファイルで問題が発生する可能性があります。ここではファイルの瞬時初期化は使用できないので、新しく割り当てられたファイル領域を、ゼロで初期化する必要があります。領域をゼロで初期化している間、トランザクション ログに対するすべての書き込み操作はブロックされます。そのため、トランザクション ログ ファイルの自動拡張のバランスを取る必要があります。つまり、操作がしばらく続行できる程度に拡張し、操作の流れが長い間中断されてしまうほど、大幅な拡張は避けるようにします。
データ ファイルに対して 1 MB の自動拡張を設定するのは、どう考えても少なすぎますが、適切な値を判断するのは容易ではありません。これは、自動拡張を応急処置として使用するのか、データ ファイルのサイズを手動で管理するように置き換えるのかによって異なります。また、データベースに挿入されるデータに対応するのに日々必要な新しい領域の量によっても異なります。つまり、結論は、自動拡張は有効にして、割合ではなく、適切な値を設定する必要があるということになります。
ストレージ スキーマ
Q. クエリを効率的に実行するために、データベース スキーマを再設計しています。テーブルには、たくさんの文字データが格納されているものもあり、最も効率的な方法で、この文字データを格納できるようにしたいと考えています。ガイドラインまたはベスト プラクティスを教えていただけませんか。
A. LOB データを格納する方法は、クエリのパフォーマンスに大きく影響することがあるため、適切な方法を選択することは、きわめて重要です。すべてのオプションについて詳しく分析すると、このコラムで扱う範囲を超えてしまうので、ガイドラインをいくつか紹介します。
第 1 に、データのサイズは常に 8,000 バイトよりも小さいですか。小さい場合は、(n)char または (n)varchar のデータ型を使用して、どうしても必要にならない限り、XML、(n)varchar(max)、varbinary(max)、(n)text、image など、LOB データ型は使用しないようにします。データ サイズが原因で、LOB データ型が必要な場合も、(n)text と image は、SQL Server 2005 で廃止されたので使用しないでください。これらのデータ型は、他の新しい LOB データ型ほど機能的ではありません。
第 2 に、LOB データ型が必要な場合、データを行内 (テーブル内の他の列と同じテーブルのデータ レコード) に格納するか、行外に格納するか (テーブルのデータ レコードのリンクを含む、個別のデータ ファイル ページに格納するか) を検討します。LOB データを頻繁に使用する場合、クエリで効率的に取得できるので、行内に格納することをお勧めします。LOB データを頻繁に使用しない場合は、通常、行外に格納する方が適切です。場合によっては、LOB データを取得するのに、クエリにかかるコストが若干に増えることがありますが、データ レコードは小さくなるので、高密度なデータ ストレージと全体的なクエリ パフォーマンスの向上を実現できます。行内には最大で 8,000 バイトの LOB データしか格納できないことに注意してください。また、データ レコードの他の列を指定すると、自動的に行外に格納されるので、物理的に可能な範囲で格納できるデータ量に制限はなくなります。
第 3 に、テーブルに LOB 列が含まれる場合、LOB 列を含むインデックスでは、オンラインのインデックス操作を実行できません。当然、これはテーブルのクラスター化インデックスに影響があります。そのため、ユーザーによっては、LOB データをまったく別のテーブルに格納して (この LOB 列を列分割して)、LOB データがクエリで要求された場合に、メイン テーブルと LOB テーブルの間で JOIN 操作を実行します。JOIN 操作は複雑なので、この処理で必要な領域が少し増えますが、インデックス メンテナンス戦略の選択肢は増えます。
また、固定幅または可変長のデータ型の使用を検討したり、データへの高速なストリーム アクセスが必要になることもあります。この場合は、SQL Server 2008 の FILESTREAM データ型の使用を検討する必要があります。LOB データ ストレージのすべての種類に関する詳細な分析については、私のブログの記事「Importance of choosing the right LOB storage technique (適切な LOB ストレージ手法を選択することの重要性、英語)」を参照してください。
重要な確認と調整
Q. 会社のデータベース メンテナンス業務の見直しを行っており、重要なデータベースに対して DBCC チェックを実行するつもりです。各データベースでは、どのくらいの頻度でチェックを実行する必要がありますか。
A. 広範なデータベース メンテナンス プランにおいて、事前に整合性チェックを行うのは、ユーザーとシステム データベースにとって重要です。また、ページ検証手法を使用するのも重要です。SQL Server 2005 以降のバージョンのデータベースでは、ページのチェックサムを有効にします。SQL Server 2000 のデータベースでは、破損ページ検出を使用します。
整合性チェックの実行頻度について絶対に正しい答えを出すのは容易ではありません。通常は、少なくとも 1 週間に 1 回、できるだけ頻繁に実行することをお勧めします。今回のご質問のケースの場合に適切な整合性チェックの頻度は、よくある回答ですが "場合によりけり" ということになります。
考慮すべき要素を 2 点挙げましょう。
第 1 に、保守ィンドウはどのように設定されていますか。整合性チェックでは、大量の CPU、メモリ、および I/O リソースを消費するので、これらのリソースを確保できる保守ウィンドウが、すべての整合性チェックを実行するのにかかる時間よりも短い場合、一度にすべてのデータベースをチェックできない可能性があります。1 週間にわたって整合性チェックを調整したり、(バックアップを復元し、復元したデータベースで整合性チェックを実行することで) 運用システム以外に整合性チェックをオフロードしたりする必要があるかもしれません。
第 2 に、データベースを格納している I/O サブシステムは、どれくらい安定していますか。I/O サブシステムに問題がある場合は、なるべく早い段階で破損の兆候を突き止めるために、できる限り頻繁に整合性チェックを実行することをお勧めします。私の経験では、破損に気付かない期間が長いほど、破損の範囲は広がり、目標復旧時点と目標復旧時間を満たしながらデータベースを復旧するのが困難になります。
つまり、整合性チェックを実行する頻度は、ユーザー次第で、ユーザーの求める安心感によって異なります。2009 年 8 月にブログで調査を行ったところ、276 人の回答者のうち、37% が整合性チェックを週 1 回実行しており、25% は毎日実行していることがわかりました。調査のすべての結果とチェックの頻度に関する詳細情報については、www.sqlskills.com/BLOGS/PAUL/post/Importance-of-running-regular-consistency-checks.aspx (英語) を参照してください。
今月のコラムの技術校閲者を務めてくれた SQLskills.com の Kimberly L. Tripp に感謝します。
.jpg)
Paul S. Randal は SQLskills.com の代表取締役であり、Microsoft Regional Director でもあり、SQL Server MVP でもあります。1999 年から 2007 年までは、マイクロソフトの SQL Server ストレージ エンジン チームに所属していました。また、SQL Server 2005 では DBCC CHECKDB/repair コードを記述し、SQL Server 2008 の開発時にはコア ストレージ エンジンを担当していました。Randal は障害回復、高可用性、およびデータベース メンテナンスの専門家であり、世界中のカンファレンスで定期的に講演を行っています。彼のブログは、SQLskills.com/blogs/paul (英語) で公開しており、Twitter は Twitter.com/PaulRandal (英語) でフォローできます。