SQL Server: データベースとインデックス

  • [アーティクル]

インデックスを管理して、適切なインデックスを配置することは、SQL Server のワークロード全体を管理するうえで重要です。

出典: 『SQL Server DMV Starter Pack』(Red Gate Books、2010 年)

Glenn Berry、Louis Davidson、Tim Ford

マイクロソフトでは、分散管理オブジェクト (DMO) を、データベースまたはファイルレベルで、論理的に次の 2 つのカテゴリに分けています。

  • データベース関連: このカテゴリには、特定のデータベースのテーブル、インデックス ページ、行数のほか、ファイル レベルのページの割り当てを調査できる動的管理ビュー (DMV) が含まれます。DMV の中には、TempDB データベースの使用状況の調査のみに使用するものもあります。
  • インデックス関連: このカテゴリには、インデックス、インデックスの特性、およびインデックスが使用される方法に特に関連している DMV が含まれます。また、ワークロードに役立つ可能性のあるインデックスを特定するのに役立つ DMV も含まれます。

この 2 つのカテゴリに含まれるすべてのビューの名前は、"sys.dm_db_" で始まります。この種類の DMV は、効果的なインデックス戦略を策定するのに役立ちます。というのも、これらの DMV を使用することは、非常に重要で頻繁に実行されるクエリが論理的で順序付けられた方式で必要なデータを読み取れるようにして、不必要な I/O を回避する最適な方法の 1 つだからです。インデックスの適切なバランスを見つけることと、"適切な" インデックスを配置することは、SQL Server のパフォーマンスを最大限に高めるうえで、きわめて重要です。

また、TempDB データベースを監視する必要もあります。TempDB は、ユーザーの一時データや、特定の SQL Server インスタンスに接続しているすべてのユーザーの内部オブジェクトを格納するグローバル リソースです。たとえば、カーソルからの結果を格納するのに使用される内部作業テーブルのほか、一時テーブルやテーブル変数などのユーザー オブジェクトが TempDB に含まれます。

不足しているインデックスを見つける

特定のデータベースで不足している可能性のあるインデックスを見つけるには、3 つの密接に関連した DMV を使用する必要があります。1 つ目の DMV は sys.dm_db_missing_index_group_stats で、次のように定義されています。

"空間インデックスを除く、欠落インデックス グループに関する概要を返します。sys.dm_db_missing_index_group_stats で返される情報は、クエリがコンパイルまたは再コンパイルされるたびに更新されるのではなく、クエリが実行されるたびに更新されます。使用状況の統計は保存されません。統計が保持されるのは、SQL Server の再起動までです。使用状況の統計をサーバーの再利用後も保持する場合は、データベース管理者が欠落インデックスの情報のバックアップ コピーを定期的に作成する必要があります。"

2 つ目の DMV は sys.dm_db_missing_index_groups で、次のように定義されています。

"空間インデックスを除く、特定の欠落インデックス グループに含まれている欠落インデックスに関する情報を返します。"

これは基本的に、sys.dm_db_missing_index_group_stats と 3 つ目の DMV (sys.dm_db_missing_index_details) の間の単なる結合テーブルです。sys.dm_db_missing_index_details は、次のように定義されています。

"空間インデックスを除く、欠落インデックスに関する詳細情報を返します。"

これら 3 つの DMV を組み合わせると、不足しているインデックスを特定する便利なクエリを実行できます (図 1 参照)。

図 1 役立つ可能性のあるインデックスを特定するクエリ

-- Missing Indexes in current database by Index Advantage SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) AS [index_advantage] , migs.last_user_seek , mid.[statement] AS [Database.Schema.Table] , mid.equality_columns , mid.inequality_columns , mid.included_columns , migs.unique_compiles , migs.user_seeks , migs.avg_total_user_cost , migs.avg_user_impact FROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK ) INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK ) ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK ) ON mig.index_handle = mid.index_handle WHERE mid.database_id = DB_ID() ORDER BY index_advantage DESC ;

このクエリでは、特定のインデックスを追加することで得られる可能性のあるメリットを計算するために、特定のテーブルのデータ アクセス パターンの統計を使用しています。index_advantage の値が高いインデックスは、クエリ コストの削減と計画されるインデックスの使用回数に基づいて、SQL Server によって、ワークロードの削減に非常に良い影響を及ぼすものと見なされます。

特定のテーブルのインデックスを変更すると、そのテーブルで不足しているインデックスの統計は、すべて削除され、徐々に再設定されることに注意してください。上記のクエリをインデックスの変更後すぐに実行した場合、おそらく、このテーブルで不足しているインデックスはないという誤った情報が返されます。

このクエリには、考慮しなければならない制限がいくつかあります。まず、このクエリでは、インデックスの最適な列順序が常に提示されるわけではありません。equality_columns または inequality_columns の下に複数の列が表示される場合、equality と inequality の結果を確認して、新しいインデックスの最適な列順序を判断することをお勧めします。また、このクエリでは、SQL Server 2008 で新しく導入されたフィルター選択されたインデックスは考慮されず、一般的に、付加列と新しいインデックスを提案します。

特にオンライン トランザクション処理 (OLTP) ワークロードがある場合は、このクエリで提案されるすべてのインデックスをやみくもに追加しないようにしてください。その代わりに、クエリの結果を注意深く確認して、通常のワークロードに関係のない結果を手動で取り除く必要があります。

まず、last_user_seek 列を確認してください。last_user_seek 列の時間が数日前または数週間前のものである場合、SQL Server でインデックスを要求したクエリはおそらく、ランダムなアドホック クエリか、まれに実行されるレポート クエリの一部であることが予想されます。一方、last_user_seek 列の時間が数秒前または数分前のものである場合は、おそらく通常のワークロードの一部なので、不足しているインデックスをより注意深く確認する必要があります。

このクエリで推奨されるものが何であっても、変更を加える前に、テーブルの既存のインデックス (使用状況の統計も含む) を必ず確認します。変動しやすいテーブルは、静的なテーブルよりも、インデックスの数が少ないのが一般的です。テーブルに、5 ～ 6 個以上効果的なインデックスが存在している場合、(OLTP ワークロードの) テーブルに新しいインデックスを追加することは躊躇すべきです。

システム ストアド プロシージャの sp_helpindex では、付加列情報が表示されないことに注意してください。つまり、代わりのものを使用するか、単に既存のインデックスについて CREATE INDEX ステートメントをスクリプト化する必要があります。

インデックスの使用状況を問い合わせる

インデックス カテゴリで最も便利な DMV の 1 つは sys.dm_db_index_usage_stats で、次のように定義されています。

"さまざまな種類のインデックス操作の数と、各種の操作が前回実行された時刻を返します。指定したインデックスに対し、1 回のクエリ実行でシーク、スキャン、参照、または更新が行われるたび、その操作はインデックスの使用としてカウントされ、このビュー内の対応するカウンターが 1 増えます。情報は、ユーザーが送信したクエリによる操作と、統計収集のスキャンなど内部生成されたクエリによる操作の両方についてレポートされます。"

DMV では、読み取りと書き込みの両方について、インデックスが頻繁に使用されているか、どのくらいの頻度で使用されているかに関する貴重な情報が提供されます。この DMV では、次の情報を問い合わせることもできます。

  • 定義済みのインデックス間のワークロードの分散
  • ワークロードによってアクセスされない、削除の最有力候補となるインデックス
  • 多数の書き込みがあり、読み取りがまったくないか少しだけあるインデックス (さらに調査を行えば、削除の候補となるインデックス)

この 3 つのスクリプトの 1 つ目 (図 2 参照) は、ヒープ テーブル、クラスター化インデックス、非クラスター化インデックスと共に、各インデックスの読み取りの回数、書き込みの回数、および fill factor の数をすべて一覧表示します。

図 2 インデックスがどのように使用されているか判断できるクエリ

--- Index Read/Write stats (all tables in current DB) SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName] , i.name AS [IndexName] , i.index_id , user_seeks + user_scans + user_lookups AS [Reads] , user_updates AS [Writes] , i.type_desc AS [IndexType] , i.fill_factor AS [FillFactor] FROM sys.dm_db_index_usage_stats AS s INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] WHERE OBJECTPROPERTY(s.[object_id], ‘IsUserTable’) = 1 AND i.index_id = s.index_id AND s.database_id = DB_ID() ORDER BY OBJECT_NAME(s.[object_id]) , writes DESC , reads DESC ;

このクエリは、ワークロードを詳細に理解するのに便利です。特定のインデックスの変動性や、読み取りと書き込みの比率を判断できるので、インデックス戦略を改良したり調整したりすることができます。たとえば、かなり静的なテーブルがある場合 (インデックスにほとんど書き込みが行われない場合)、不足しているインデックス クエリとして提示されたインデックスを安心して追加できます。

SQL Server 2008 Enterprise Edition を使用している場合、このクエリは、(PAGE か ROW かにかかわらず) データの圧縮を有効にするのが適切かどうかを判断するのに役立ちます。書き込みがほとんど行われないインデックスは、変動しやすいインデックスよりもデータ圧縮候補として適しています。

次のスクリプト (図 3 参照) では、sys.indexes と sys.objects を使用して、sys.dm_db_index_usage_stats で表示されない、現在のデータベースのテーブルとインデックスを見つけます。つまり、このスクリプトで検出されたインデックスでは、SQL Server が最後に起動されてから、または現在のデータベースが閉じられるかデタッチ (いずれか短い方が該当) されてから、読み取りまたは書き込みが行われていないということになります。

図 3 使用されていないインデックスを見つけるクエリ

-- List unused indexes SELECT OBJECT_NAME(i.[object_id]) AS [Table Name] , i.name FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id] WHERE i.index_id NOT IN ( SELECT s.index_id FROM sys.dm_db_index_usage_stats AS s WHERE s.[object_id] = i.[object_id] AND i.index_id = s.index_id AND database_id = DB_ID() ) AND o.[type] = ‘U’ ORDER BY OBJECT_NAME(i.[object_id]) ASC ;

完全で典型的なワークロードが存在するくらい長く SQL Server が実行されている場合は、これらの使用されていないインデックス (およびテーブル) が "消滅している" 可能性は十分にあります。つまり、データベースでは、このようなインデックスを既に使用しておらず、さらに調査を行えば、このようなインデックスは削除できる可能性があるということです。

最後に、sys.dm_db_index_usage_stats クエリでは、現在のデータベースでフィルター処理を行います (図 4 参照)。これには、非クラスター化インデックスのみが含まれます。このクエリは、特定のインデックスのメンテナンス コストが、そのインデックスを配置するメリットを上回るかどうかを判断するのに役立ちます。

図 4 めったに使用されないインデックスを見つけるクエリ

-- Possible Bad NC Indexes (writes > reads) SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] , i.name AS [Index Name] , i.index_id , user_updates AS [Total Writes] , user_seeks + user_scans + user_lookups AS [Total Reads] , user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference] FROM sys.dm_db_index_usage_stats AS s WITH ( NOLOCK ) INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON s.[object_id] = i.[object_id] AND i.index_id = s.index_id WHERE OBJECTPROPERTY(s.[object_id], ‘IsUserTable’) = 1 AND s.database_id = DB_ID() AND user_updates > ( user_seeks + user_scans + user_lookups ) AND i.index_id > 1 ORDER BY [Difference] DESC , [Total Writes] DESC , [Total Reads] ASC ;

このクエリでは、書き込みを多く行い、読み取りをまったく行わないインデックスを探します。このカテゴリに分類されるインデックスは、(完全な調査を行った後) すべて削除の有力な候補となります。ただし、完全で典型的なワークロードが存在するくらいに SQL Server インスタンスが十分長く実行されている必要があります。

日常的なワークロードでは見られない場合がある、定期的なレポートのワークロードについて忘れないようにしてください。このようなワークロードを促進するインデックスの使用頻度は高くありませんが、これらのインデックスの存在は重要です。

また、書き込みが多数あり、読み取りが少ない行についても注目する必要があります。このようなインデックスを削除するかどうかは、関連するテーブルやワークロードに関する知識などに応じて、個人の判断に委ねられます。

Glenn Berry

Louis Davidson

Tim Ford

Glenn Berry は、米国コロラド州デンバーの NewsGator Technologies でデータベース アーキテクトを務めています。彼は SQL Server の MVP ですが、MCITP、MCDBA、MCSE、MCSD、MCAD、MCTS など全種類のマイクロソフト認定資格を保有しており、試験好きであることの証明になっています。

Louis Davidson は、データベースの社内開発者およびアーキテクトとして、IT 業界に 16 年間携わっています。彼は、SQL Server の MVP を 6 年連続で受賞しており、データベース設計についての書籍を 4 冊執筆しています。現在は、米国のバージニア州バージニア ビーチとテネシー州ナッシュビルにある企業をサポートする Christian Broadcasting Network のデータ アーキテクトとして働いていますが、データベース管理者として任務に当たることもあります。

Timothy Ford は、SQL Server の MVP で、SQL Server に 10 年以上携わっています。彼は、Spectrum Health の SQL Server プラットフォームにおける主力データベース管理者で、非常に詳しい知識を持っています。また、2007 年からさまざまな Web サイトでテクノロジについての記事を執筆しており、自身のブログ (thesqlagentman.com、英語) では、SQL Server、在宅勤務、および高度な開発のトピックを扱っています。

『SQL Server DMV Starter Pack』の詳細については、red-gate.com/our-company/about/book-store/ (英語) を参照してください。

関連コンテンツ