遅いクエリー文を実行してインデックスの使用を監視

クエリーのパフォーマンスに影響を与えるもう1つの要因は、DMLクエリーが削除され、データが挿入され、変更されたクエリーに関連しています.指定したテーブルに作成されたインデックスが多ければ多いほど、データを変更する際に使用するリソースが多くなります.トランザクション間にロックの組合せが存在する場合、長い変更操作は同時性を破壊します.その結果、アプリケーションがタイムアウトして使用されるインデックスを知ることが重要になります.次に、データベース内の構造に使用されたことのないインデックスが存在するかどうかを示すことができます.
SQL Server 2008はsysを提供する.dm_db_usage_stats DMVは、どのインデックスが使用されているか、およびユーザー・クエリーによって使用されているか、またはシステム・オペレーションのみで使用されているかを示します.クエリの実行ごとに、この動的管理が戻そうとするカラムは、クエリ文で使用される実行計画の増加に伴って増加します.データはSQL Serverの実行時に収集されます.このdmvのデータはメモリにのみ格納され、永続的に保存されません.したがって、SQL Serverインスタンスが停止すると、データも失われます.これらの情報を間欠的に取得して保存することができ、後で分析するのに便利です.
インデックス上の操作は、ユーザータイプとシステムタイプの2つに分けられます.ユーザタイプはSELECT,INSERT,DELETEおよびUPDATE操作,または統計情報の更新を行う傾向がある.2つのカテゴリの文を列で比較すると、次のように表示されます.
インデックス上の検索(seek)操作(user_seeks or system_seeks).
インデックス上のルックアップ操作(user_lookups or system_lookups).
インデックス上の遍歴操作(Scan)(user_scans or system_scans).
インデックスの更新(Update)操作(user_updates or system_updates).
インデックス上のこれらの各操作について、最後のアクセスには前のタイムスタンプがマークされます.インデックス自体は3つのカラムで識別されます:database_id,object_id,index_id.index_id=0はスタックタイプのテーブルを表し、index_id=1はクラスタインデックスを表し、index_id>1は、非集約インデックスを表す.
アプリケーションはデータベース上で持続的に適用するsys.dm_db_index_usage_statsがアクセスできるインデックスのリストも増加します.
SQL Server上のscan、seek、lookup作業の定義とルールは次のとおりです.
Seek:データへのアクセス中にBツリー構造が何回使用されたかを示します.Bツリー構造が、各階層から複数のページを読み出して1つのデータ行のみを取得するために使用されるか、またはインデックスページの半分近くが読み込まれ、ベーステーブルから数Gのデータが読み込まれるかにかかわらず.したがって、対応するインデックスのすべての検索がこのカテゴリに含まれることを期待することができます.
Scan:データ層がBツリー構造を使用してデータを取得しない場合のテーブルへのアクセス数を示します.インデックスが定義されていないテーブルでは、このscanの場合があります.テーブルにインデックスが定義されている場合、クエリーがテーブルに作成されたインデックスを使用しない場合、scanも発生します.
Lookup:非集計インデックスポインタが集計インデックスを指すことによる集計インデックスによるデータの検索を表します.このシーンはSQL Server 2000以降でブックマークルックアップ(Bookmark lookup)と呼ばれています.テーブルにアクセスするために非集計インデックスが使用され、selectクエリのすべてのカラムが含まれておらず、where述語で定義されているシーンを示します.SQL Serverは非集計インデックスにuser_を追加します.seeksという列の値に集計インデックスのエントリであるuser_を加えるlookups列の値.テーブルに多くの非集計インデックスが定義されている場合、この和の値は高くなります.ユーザーがテーブルのインデックスにseekをする回数が高い場合、ユーザーlookupsの回数も高くなり、ユーザーが非集計インデックスにアクセスする回数も高い場合は、集計インデックスの数の多い使用を解決するために非集計インデックスを支援することを考慮する必要があります.
次のDMVクエリは、すべてのデータベース上のすべてのオブジェクトのインデックス使用情報を取得するために使用できます.

select object_id, index_id, user_seeks, user_scans, user_lookups 
from sys.dm_db_index_usage_stats 
order by object_id, index_id

以下の出力を参考にすることができます

object_id       index_id    user_seeks    user_scans    user_lookups 
--------------------------------------------------------------------
521690298        1          0             251           123
521690298        2          123           0             0

現在、インデックスは1つも使用されず、クエリに対応する実行直接アクセスデータ層内のテーブルが251個存在します.クエリの実行に対応する123個の非集計インデックス・アクセス・テーブルがあり、selectのカラムもwhere述語で指定されたカラムも含まれていません.123個のlookupが集計インデックスの上に存在することを見たからです.
　　sys.dm_db_index_usage_statsで最も興味深い列はuser_を含むユーザータイプを表示するための列です.seeksとuser_scans.システム使用カラム(system_など)seeksは、既存のインデックスの結果とみなすことができる.インデックスが存在しない場合は、統計を強制的に更新する必要がなく、一貫性をチェックする必要もありません.したがって、分析作業では、インスタント・クエリーとユーザー・アプリケーションのインデックスの使用状況を示す4つのカラムに注目する必要があります.
指定したテーブルがSQL Serverの最後の起動後に使用されなかったインデックスの情報を取得するには、このオブジェクトを持つデータベースで次のクエリを実行します.
select
i.name

from sys.indexes i 
where i.object_id=object_id('') and
    i.index_id NOT IN  (select s.index_id 
                        from sys.dm_db_index_usage_stats s 
                        where s.object_id=i.object_id and     
                        i.index_id=s.index_id and
                        database_id = <dbid> )

使用されていないすべてのインデックスは、次の文で取得できます.

select object_name(object_id), i.name 
from sys.indexes i 
where  i.index_id NOT IN (select s.index_id 
                          from sys.dm_db_index_usage_stats s 
                          where s.object_id=i.object_id and 
                          i.index_id=s.index_id and 
                          database_id = <dbid> )
order by object_name(object_id) asc

現在の場合、テーブル名およびインデックス名はテーブル名で格納されます.
このDMVの真の目的は,長時間実行されるインデックスの使用状況を観察することである.ビューに対するスナップショットまたは結果に対するスナップショットを作成して格納することが望ましい場合があります.このようにして、毎日1回実行し、互いの変化を比較します.どのインデックスが何ヶ月も使用されていないか、または使用されていないかを明確に認識できれば、最終的にデータベースから削除することができます.