• [アーティクル]

SQL に関する Q & Aデータベースの整合性、一時テーブルなど

Paul S. Randal

Q SQL Server 2005 データベースの 1 つでとても奇妙な動作が行われているのに気付きました。夜間のデータベース メンテナンスの一環として、データベースで DBCC CHECKDB を実行しており、DBCC からデータ破損エラーが報告される場合があります。奇妙なのは、その前日にはページ チェックサムのエラーはなく、メンテナンスでエラーが発生した翌朝に手動で DBCC CHECKDB を実行すると、破損エラーが発生しないことです。何がどうなっているのか、教えていただけませんか。かれこれ 1 か月この状態で、DBCC CHECKDB の結果を信頼できず困っています。

A DBCC CHECKDB を定期的なメンテナンスの一環として実行し、ページ チェックサムを有効にしているのは良いことです。お知らせいただいたような現象が発生すると、DBCC CHECKDB から誤った結果が返されているのではと非常に当惑することでしょう。しかしながら、この現象はかなり頻繁に発生するものです。実行時には破損があることは通知されず、DBCC CHECKDB で破損が検出されたにもかかわらず、その数時間後に再度 DBCC CHECKDB を実行すると破損が回復されているという現象です。この理由を順を追ってご説明しましょう。

まず、一般的なデータベースの操作を行っているときには報告されない破損が、DBCC CHECKDB で報告されることは珍しくありません。ページ チェックサムは I/O サブシステムに起因する破損を検出するには有効な手段ですが、これは、実際に破損が発生した後に SQL Server でデータ ページが読み取られた場合にのみ有効です。

データ チェックサムが適用されているデータ ページで、I/O サブシステムによって破損が発生した場合を考えてみましょう。ページ チェックサムで破損を検出することはできますが、データ ページが SQL Server でメモリ内に読み取られるまで、ページ チェックサムは検証されないので、その時点まで破損が検出されることはありません。つまり、破損が発生した後に、当該ページが SQL Server で読み取られなければ、破損が検出されることはありません。そのため、ページ チェックサムを有効にして定期的に整合性チェックを行うことが不可欠です。整合性チェックでは、データベースの全ページを読み取り、ページ チェックサムを検証して、可及的早期に破損を検出します。

ご質問のケースでは、データの破損は、通常のデータベース操作で読み取られなかったデータ ページで発生したもので、その破損したページが DBCC CHECKDB で読み取られるまで破損が検出されなかったと考えられます。ページ チェックサムで破損が検出されなかったように見えるかもしれませんが、そういうわけではありません。

次に、破損エラーが解消されるという現象は DBCC CHECKDB を連続して実行するとよく発生するものですが、この現象が発生するのは、連続して DBCC を実行した場合、かつ 1 回目と 2 回目の実行の間に変更が加えられたデータベースに限られます。たとえば、あるページが実際に破損していて、DBCC CHECKDB から、そのページに破損があるという結果が返されたとしましょう。ところが、このページが、空になったなどの理由でテーブルから割り当てを解除されると、2 回目の DBCC CHECKDB の実行時には、このページが読み取られることはないので、破損が報告されることもありません。DBCC CHECKDB では、割り当て済みのページ (つまり使用されているページ) のみを読み取ります。ご質問のケースでは、夜間メンテナンス ジョブで、インデックスの再構築や再編成も行われているのではないかと推測しています。これらの処理では、特定のオブジェクトやインデックスに割り当てられている一連のページが大幅に変更されることがあります。これがお使いの環境で発生している現象です。インデックスの再構築では、その一環として破損したページの割り当てが解除されるので、2 回目の DBCC CHECKDB 実行時には、破損エラーが報告されません。

破損したページを検出するには、DBCC CHECKDB を実行中にエラーが発生した場合には、処理を停止するようにメンテナンス ジョブを変更することです。こうすれば、手動で破損を確認して、さらなる措置を講じることができます。

Q SQL Server 2000 から SQL Server 2008 に直接アップグレードする予定ですが、懸案事項は tempdb です。短期間しか存在しない一時テーブルが多数あるため、現在使用中の SQL Server 2000 でも tempdb が問題になっています。SQL Server 2005 と SQL Server 2008 のどちらでも tempdb の使用頻度が格段に高くなっているため、アップグレード後にパフォーマンスが低下するのを回避するため特別なプロビジョニングが必要だということは把握しています。このようなプロビジョニングが必要な理由と、それに必要な手順を教えてください。

A ご質問の内容から、1 つのプロセッサに対して 1 つのファイルという tempdb のアーキテクチャを実装しているのではないかと推測しています。このアーキテクチャは、複数のデータベース接続によって多数の短期間しか存在しない一時テーブルが生成される場合に必要となることが多いものです。また、トレース フラグ 1118 を有効にしなければならなかったのではないかということも推測しています (詳細については、「tempdb データベースの同時実行制御の強化」を参照してください)。

SQL Server 2005 以降では確かに tempdb の使用頻度が高くなる可能性がありますが、tempdb に依存する機能を使用しなければ、この限りではありません。SQL Server 2000 で tempdb のパフォーマンスに問題があるソリューションを、同じハードウエアを使用する SQL Server 2008 にアップグレードすると、SQL Server 2005 の SQL Server ストレージ エンジンで tempdb に対して行われた変更により、パフォーマンス上の問題が多少軽減されることがよくあります。

SQL Server 2005 と SQL Server 2008 において tempdb を多用する機能は、以下の 4 つです。

  • オンライン インデックスの操作
  • DML トリガ
  • 複数のアクティブな結果セット (MARS)
  • スナップショット分離 (トランザクション レベルとステートメント レベルの両方)

この 4 つの機能では、特定の時点におけるデータ レコードのバージョンを保存できる、バージョン管理と呼ばれる基盤となるテクノロジを使用しています。簡単に言えば、このデータ レコードのバージョンは、すべてのユーザーが共有している tempdb の同じバージョン ストアに格納されます。これらの機能を使うほどに、バージョン ストアと tempdb の使用頻度が高くなり、パフォーマンスへの影響も大きくなります。

アップグレードを成功させる秘訣は、運用環境にテスト システムを直接移行する場合は、新しいスキーマを使用したテスト システムに、運用時の標準的な負荷を実装して、パフォーマンスを測定して、運用環境にシステムを移行したときに想定外の事態が発生しないようにすることです。

残念ながら、本コラムの範疇でこの手法を詳細に解説することはできませんが、以下にお勧めのリソースを紹介します。

Q インデックス パフォーマンスを向上させる処理も含めたデータベースの夜間メンテナンス プランを実行しています。インデックスに FILL FACTOR オプションを設定すると、インデックスをメンテナンスする必要がなくなるという話を聞きました。これは本当でしょうか。データベースには、断片化されているインデックスもあれば、断片化されないものもあるようです。データベースに、すべてのインデックスに適用される既定の FILL FACTOR 値を設定する必要はありますか。また、その必要がある場合は、推奨値を教えてください。

A FILL FACTOR を設定することでインデックス メンテナンスの必要性を低減させることはできますが、その必要性を完全に排除できることはめったにありません。手短に言うと、インデックスが作成または再構成される際、FILL FACTOR の設定によって、ストレージ エンジンに対し、クラスタ化インデックスのページと非クラスタ化インデックスのページに一定の空き領域を残すように指示されます (ただし、FILL FACTOR の設定は、通常の挿入、更新、および削除の操作では維持されない点に注意してください)。たとえば、FILL FACTOR の値が 90 であれば、10% の空き領域を残すことになります。FILL FACTOR の値が 0 または 100 である場合は、空き領域は確保されません (これが大きな混乱の種になっています)。

ページの空き領域は、コストがかさむだけでなく断片化の原因にもなるページ分割と呼ばれる操作を行わずに、ページのレコードを拡張したり、ページに新しいレコードを挿入したりできるようにするために確保しています。空き領域の割合を指定すると、次のインデックス メンテナンス操作が行われるまで、ページの使用領域を一定に保つことができます (次のインデックス メンテナンス操作では、FILL FACTOR の設定がリセットされます)。ここでポイントとなるのは、次のインデックス メンテナンスが行われるまでの間に発生するページ分割操作を最小限に抑えられる割合を指定することです。

OLTP (オンライン トランザクション処理) データベースについては、推奨値はなく、試行錯誤しながら各インデックスに適した FILL FACTOR の値を特定する必要があります。データ ウェアハウスでは、インデックスは変化しないので、FILL FACTOR の値は 100% に設定します (つまり、ページ上に空き領域を確保しません)。通常、インデックスによって FILL FACTOR の最適な値は異なるので、データベースに設定されている FILL FACTOR の値が既定値である 100% から変更されることは珍しいことではありません。詳細については SQL Server 2008 オンライン ブックの「FILL FACTOR」を参照してください。

別の選択肢として、ページ分割が行われないようにインデックスを変更することもできます。この方法では、ランダムな挿入処理が行われないようにインデックス キーを変更する (ランダムな GUID 主キーを使用しないなど) か、可変長列のサイズを変更する操作を禁止することが必要になる場合があります。

Q SP1 がリリースされ次第、SQL Server 2008 に移行することを考えていますが、その機能の中で特に期待しているのが、2 GB という列の値の制限を取り払う FILESTREAM です。FILESTREAM データ型を使用するための新しいスキーマを設計する前に、運用時に問題を引き起こす可能性がある欠点や難点があれば教えてください。

A 新しい機能を新しいスキーマやアプリケーションに組み込む前に、その特徴をすべて把握することは賢明な考えです。FILESTREAM のように SQL Server に属さないテクノロジを使用した機能の場合は、なおさらです。私が SQL Server チームに依頼されて執筆したホワイト ペーパー「SQL Server 2008 の FILESTREAM ストレージ」に必要な情報の大半が記載されていますので、詳しい情報を得るには、こちらを参照することをお勧めします。ですが、このコラムでは、主な懸案事項を紹介します。

第 1 に、FILESTREAM データ型のデータは、SQL Server データ ファイルではなく、NTFS ファイル システムに格納されるという点です。単一のディレクトリに多数のファイルが格納されている場合に NTFS ファイル システムが正常に動作することを保証するためには、さまざまな構成手順が必要になります。たとえば、8.3 形式のファイル名を無効にしたり、NTFS ファイル システムのクラスタ サイズを適正に設定したり、さらには FILESTREAM データを他のデータと分けて別の物理ディスク上に保存したりすることが必要になる場合もあります。

第 2 に、FILESTREAM を使用してデータを格納すると、その平均サイズは 1 MB 以上になります。ある調査によると、データ サイズが 256 KB 以下または 256 KB ～ 1 MB の範囲である場合、FILESTREAM のようなメカニズムを使用するよりも SQL Server データ ファイルに直接データを保存した方が、パフォーマンスが高くなることが明らかになっています。

第 3 に、FILESTREAM データに対して行われる操作について考慮する必要があります。FILESTREAM データでは部分的な更新はサポートされていないので、200 MB のデータの中の 1 バイトだけを更新した場合も、200 MB のデータがまるごと新規作成されます。これは、コストがかさむ操作であるだけでなく、NTFS レベルでの断片化につながり、その結果、パフォーマンスがさらに低下する可能性があります。アプリケーションで部分的な更新を頻繁に行う場合は、単一の FILESTREAM データが何度も更新されるのを回避するためのバッチ処理メカニズムが必要になります。

最後は、FILESTREAM と他の高可用性テクノロジの機能の互換性についてです。FILESTREAM では、バックアップと復元操作 (特定の時点への復元を含む)、ログ配布、レプリケーションを完全にサポートしています。しかし、SQL Server 2008 のデータベース ミラーリングとはまったく互換性がありません (次のバージョンの SQL Server ではこの問題が解決されるそうです)。

ここで紹介したのは考慮事項の概要です。全体像を把握するためにもホワイト ペーパーを参照することをお勧めします。どのような新機能についても同じことが言えますが、新機能を使用したアプリケーションを設計する前には、必ず広範なテストを行って、その機能がニーズを満たしているかどうかを確認する必要があります。FILESTREAM には NTFS ストレージが含まれていますが、実際の運用環境で足元をすくわれることがないように、運用環境に導入する前にパフォーマンス テストと障害復旧テストも行うのが賢明です。

Paul S. Randal は SQLskills.com の代表取締役であり、SQL Server MVP でもあります。1999 年から 2007 年までは、マイクロソフトの SQL Server ストレージ エンジン チームに所属していました。また、『DBCC CHECKDB/repair for SQL Server 2005』を執筆し、SQL Server 2008 の開発時にはコア ストレージ エンジンを担当していました。Paul は障害回復、高可用性、およびデータベース メンテナンスの専門家であり、世界中のカンファレンスで定期的に発表を行っています。彼のブログは SQLskills.com/blogs/paul で公開されています。