SQL に関する Q&A: 動的データと障害回復
今月の SQL Server を成功に導くソリューションは、tempdb の拡大や難しいクラスター化の問題から、不完全な障害回復の計画に至るまで、さまざまな課題を取り上げます。
Paul S. Randal
領域の増加
Q. 管理している運用サーバーの 1 台で問題が発生し、tempdb のサイズが数日おきに大きくなっています。これは比較的最近起こった問題です。サーバーへの接続数とメモリ使用量に変化は見られません。tempdb の領域を使用しているものを特定するには、どのように監視すればよいでしょうか。
A. tempdb で使用する領域が増えることについては、非常に多くの理由があります。
- スナップショット分離、オンラインのインデックス操作など、バージョン管理システムを使用すると、tempdb のバージョン ストアが大きくなります。
- 古い統計によってクエリ プランに変更が加えられると、クエリ プランの操作が発生し、tempdb で大量の記憶容量が使用されることになります。
- 一時テーブルを使用して、部分的に処理されたデータを格納する、新しいアプリケーション コードが配置された可能性があります。
理由は何であれ、状況を追跡する簡単な方法がいくつかあります。まず、動的管理ビュー (DMV) の sys.dm_db_file_space_usage を使用して、全体的な tempdb の使用領域を確認します。たとえば、この DMV の結果を 30 秒おきにキャプチャすると、使用領域の増加が、バージョン ストア、ユーザー オブジェクト、またはクエリ処理を支援するために作成されたオブジェクトのいずれによるものかを判断できます。
領域の増加の原因がバージョン ストアにある場合、DMV の sys.dm_tran_top_version_generators を使用して詳細を確認できます。実際に役立つ情報を取得するには、この DMV に加えて sys.partitions と sys.indexes を使用する必要があります。これで、最も多くのバージョンが生成されたテーブルを把握できます。
バージョン ストア以外のものが領域を占有している場合は、sys.dm_db_task_space_usage を使用して同じような頻度で結果をキャプチャして詳細を確認できます。また、DMV を sys.dm_exec_requests と組み合わせて使用すると、領域を占有している接続とクエリを特定できます。
領域を占有しているのが長いストアド プロシージャである場合は、問題があるプロシージャ ステートメントを突き止めることができるように、使用している tempdb の領域のサイズを定期的に出力するようにプロシージャを編集する必要があります。私が、この問題に直面したときは、この処理をクライアント システムで何度か行う必要がありました。
このような DMV の使用方法に関する詳細については、ホワイト ペーパー「SQL Server 2005 における tempdb の使用方法 (英語)」を参照してください (このホワイト ペーパーの内容は、SQL Server 2005 以降のバージョンにも対応しています)。
適切なクラスター
Q. 私は、新しいアプリケーションのデータを格納するデータベースのスキーマを設計するように依頼されています。テーブルに "適切な" クラスター化されたインデックス キーを選択することについて、さまざまなアドバイスを得ました。"適切な" クラスター化されたインデックス キーの条件について教えていただけませんか。また、これがこれほど重要視される理由は何でしょうか。
A. これは複雑な質問なので、このコラムですべてに回答するのは難しいですが、簡単に説明すると、"適切な" クラスター化されたインデックス キーとは、パフォーマンスの低下と領域の無駄を最小限に抑えるよう慎重に選択されたキーのことです。適切なクラスター化されたインデックス キーには、厳密、静的、一意、増え続けるという 4 つの特徴があります。
- 厳密 (できるだけ占有バイト数を少なくする): すべての非クラスター化インデックスには、クラスター化されたインデックス キーが含まれます。クラスター化されたインデックス キーが大きくなると、非クラスター化インデックスの重複する情報で占有する領域が多くなります。
- 静的 (変更されない): キー値の変更にはコストがかかります。SQL Server では、削除および挿入の操作によってキーの更新を行います (詳細については、私のブログの記事 (英語) を参照してください)。クラスター化されたインデックス キーを更新したら、非クラスター化インデックス内の対応する行もすべて更新する必要があります。また、キーを変更しても、インデックス内のそのキーの位置を再び使用しない場合は、データ ファイル ページに空の領域が作成されることになります。
- 一意: SQL Server で、重複したキーの値を "一意にする" ための非表示の 4 バイト列を追加しないようにします。これで、キーの幅が広くなります。
- 増え続ける: 新しいレコードの挿入パターンによって、クラスター化されたインデックスにランダムな挿入が行われ、コストのかかるページ分割操作が実行されることがあります。その結果、データ ファイル ページで論理的な断片化と無駄な領域が発生します。
適切にクラスター化されたインデックス キーの特徴を考えると、適合する自然キー (テーブル データから派生したキーなど) は存在しないことがほとんどなので、代理キー (人為的なテーブル列など) を使用する必要があります。bigint ID 列が代理キーの好例です。詳細な説明や理由については、Kimberly Tripp のブログの「Clustering Key (クラスター化キー、英語)」カテゴリを参照してください。
最悪の事態に備える
Q. 最近ニュージーランドや日本で発生した地震を受けて、障害回復の計画を検査したところ、古いものを採用していることが判明しました。会社側に計画を改良してテストすることを申し入れましたが、承認されませんでした。会社は、災害など起こり得ないと考えているのです。この話をどのようにして経営陣に持ちかければよいか、何かヒントをいただけませんか。
A. 最近の災害をきっかけに、自社の障害回復 (DR) 戦略を積極的に分析されていると聞いてうれしく思っています。多くの会社は、災害対策に無関心であり、メールに記載されているような態度を取っています。大規模な自然災害が発生するのは比較的まれですが、ビルの火災や停電などの局所的な問題はよくあることなので、企業は偶発的な問題を懸念する必要はないなどと考えるべきではありません。
経営陣の賛同が得られなくても、バックアップからデータベースのコピーを復元するなど、ご自身で実行できるテストはたくさんあります。このテストでは、バックアップの整合性とバックアップ戦略をテストできるので、復元時間が、特定のデータベースで許容できる最大のダウンタイムの要件を満たしているかどうかを確認できます。多くの場合、これは DR 戦略のテストで最初に見られる問題です。時間の経過と共にデータ ボリュームは大きくなり、復元時間も比例して増加します。
データベース ミラーリング パートナーやフェールオーバー クラスターのフェールオーバーなど、他の DR 戦略は、ご自身でテストを行うのは非常に困難です。このようなテストでは、(フェールオーバーとフェールバックの両方で) ある程度のアプリケーションのダウンタイムが必要になります。
経営陣の説得に関しては、すべてのスタッフによる回復を支援する態勢が整っている計画されたテストと最低限のスタッフしか待機していない祝日の午前 2 時に災害が実際に発生したときのどちらで、DR 戦略が機能しないことが判明した方がよいかをたずねてみてください。
DR 戦略が不十分だったために機能停止に陥った会社の事例が広く報道されることはよくあります。経営陣は自社がこのようなニュースで取り上げられることを望んでいるでしょうか。芝居がかっているように聞こえるかもしれませんが、これは十分に的を射ています。
障害回復は、結果として、会社やクライアントにかかるコストを最小限に抑えます。クライアントが、機能停止の影響を受けたり、会社の迅速な回復能力に対して不信感を持ったりすると、他の会社に乗り換える可能性があります。これは会社の最終的な収益にとって、間違いなく痛手となります。
私たち IT プロフェッショナルは、技術者として、会社の財務的な影響の観点から、経営陣に IT 障害について検討するように求める必要があります。個人的に、これは DR 戦略の改良とテストに時間と資金を投資するように経営陣を説得するうえで、効果的な方法だと考えています。詳細については、私の最新のブログ記事 (英語) をご覧ください。
圧縮に関するコスト
Q. SQL Server 2008 のデータ圧縮機能を使用して、ストレージ コストを削減したいと考えていますが、この機能はデータ ウェアハウスでしか使用できず、オンライン トランザクション処理 (OLTP) システムで使用すると大規模なパフォーマンス上の問題が発生するということを知りました。これは本当でしょうか。
A. データ圧縮機能が、本来、データ ウェアハウスで使用することを目的として作成されているというのは本当です。データ圧縮により、テーブルやインデックスのサイズを削減できます。つまり、8 KB のデータ ファイル ページにより多くのレコードを格納できるので、ディスクにデータを格納するのに必要なデータ ファイル ページ数が少なくなります。圧縮したデータを格納するデータベースで必要なディスク領域は少なくなり、結果として、必要なエンタープライズ クラスのストレージ領域が少なく済むので、大幅なコスト削減を実現できます。
もちろん、トレードオフとして、データを使用する前に圧縮解除する必要があります。データは SQL Server バッファー プール (データ ファイル ページのメモリ内キャッシュ) に読み込まれるときには圧縮解除されません。実際に、クエリを満たす必要がある場合にのみ圧縮解除されます。圧縮解除では CPU リソースを使用するので、CPU リソースの使用率が 1 つのトレードオフになります。
通常のデータ ウェアハウスには、大量のデータが格納されています (何百ギガバイトから数テラバイトと考えられます)。通常、このデータへのアクセス パターンでは、バッファー プールに大量のデータを読み込み、一度処理しますが、データがメモリに格納されている間に再び使用されることはありません。
このアクセス パターンでは、データを小さいサイズに圧縮して、読み取り I/O 操作の回数を最小限に抑えるのが合理的です。これで、データを格納する SQL Server データ ファイル ページ数が少なくなり、これらのページを読み取る I/O 操作の回数も減ります。通常はこれで、このような種類のクエリを迅速に完了できるようになります。そのため、もう 1 つのトレードオフは、(データを圧縮解除するための) CPU リソースに対するクエリ速度になります。
通常、OLTP ワークロードでは、データ ウェアハウスよりも、データが変動する可能性が非常に高くなります。つまり、データ圧縮を使用すると、読み取るデータの圧縮解除と挿入または更新するデータの圧縮を常時行うことが原因で、CPU コストが高くなります。OLTP データベースのデータ圧縮を検討する場合は、トレードオフについて慎重に検討する必要があります。
ご質問に話を戻しますが、データ圧縮は、本来、データ ウェアハウスを対象としていますが、多くの SQL Server ユーザーは、サーバーで CPU の領域に大量の "余裕" があることに気付きました。この CPU の余剰使用領域を使用して、クエリ実行時間が長くなっても問題なければ、データ圧縮の使用により、使用領域とストレージ コストを大幅に削減することができます。データ圧縮は OLTP 環境でも役立ちます。運用を開始する前に、ワークロードにおける使用領域の削減とパフォーマンス コストを評価するようにしてください。
使用領域の削減については、sp_estimate_data_compression_savings プロシージャを使用して、期待される削減の割合を把握できます。データ圧縮を有効 (または無効) にするには、リビルドが必要になるため、割合を把握するのは重要になります。この処理自体にコストがかかります。詳細については、ホワイト ペーパー「データ圧縮: キャパシティ プランニングとベスト プラクティス」を参照してください。
.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 (英語) でフォローできます。