sys.dm_db_missing_index_details (Transact-SQL)

[アーティクル]
07/05/2024

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance

不足しているインデックスに関する詳細情報を返します。

Azure SQL Database では、動的管理ビューでは、データベースの包含に影響する情報を公開したり、ユーザーがアクセスできる他のデータベースに関する情報を公開したりすることはできません。この情報が公開されないようにするために、接続されているテナントに属していないデータを含む行はすべてフィルタリングされます。

列名	データ型	説明
index_handle	int	特定の欠落インデックスの識別子。識別子はサーバー全体で一意です。 `index_handle` は、このテーブルのキーです。
database_id	smallint	インデックスがないテーブルが存在するデータベースを識別します。 Azure SQL Database では、値は 1 つのデータベースまたは Elastic Pool 内で一意ですが、論理サーバー内では一意ではありません。
object_id	int	インデックスが欠落しているテーブルの識別子。
equality_columns	nvarchar (4000)	次の形式の等値述語に使用できる列のコンマ区切り一覧。 table.column = constant_value
inequality_columns	nvarchar (4000)	次の形式のような不等値述語に使用できる列のコンマ区切り一覧。 table.column>constant_value "=" 以外の比較演算子は、不等値を表します。
included_columns	nvarchar (4000)	クエリの対象となる列として必要な列のコンマ区切りのリスト。カバー列または含まれる列の詳細については、「組み込み列を使用してインデックスを作成する」を参照してください。メモリ最適化インデックス (ハッシュとメモリ最適化非クラスター化の両方) の場合は、 `included_columns`を無視します。テーブルのすべての列は、すべてのメモリ最適化インデックスに含まれます。
statement	nvarchar (4000)	インデックスが欠落しているテーブルの名前。

解説

sys.dm_db_missing_index_detailsによって返される情報は、クエリオプティマイザーによってクエリが最適化され、永続化されていない場合に更新されます。不足しているインデックス情報は、データベースエンジンが再起動されるまでのみ保持されます。データベース管理者は、サーバーのリサイクル後に保持する必要がある場合は、不足しているインデックス情報のバックアップコピーを定期的に作成する必要があります。データベースエンジンが最後に起動された時刻を調べるには、sys.dm_os_sys_info の sqlserver_start_time を使用します。

特定の欠落しているインデックスが含まれている不足しているインデックスグループを特定するには、index_handle列に基づいてsys.dm_db_missing_index_detailsと結合することで、動的管理ビューsys.dm_db_missing_index_groupsクエリを実行できます。

Note

この DMV の結果セットは 600 行に制限されています。各行には、不足しているインデックスが 1 つ含まれています。不足しているインデックスが 600 個を超える場合は、新しいインデックスを表示できるように、既存の不足しているインデックスに対処する必要があります。

CREATE INDEX ステートメントで不足しているインデックス情報を使用する

sys.dm_db_missing_index_detailsによって返された情報を、メモリ最適化インデックスとディスクベースインデックスの両方の CREATE INDEX ステートメントに変換するには、等値列を非等値列の前に配置し、一緒にインデックスのキーを作成する必要があります。付加列は、INCLUDE 句を使用して CREATE INDEX ステートメントに追加します。等値列の有効な順序を決定するには、選択度に基づいて列を並べ替えます。最も選択的な列を最初に一覧表示します (列リストの左端)。インデックス候補が見つからない非クラスター化インデックスのチューニングの詳細については不足しているインデックス機能の制限など。

メモリ最適化インデックスの詳細については、「メモリ最適化テーブルのインデックスを参照してください。

トランザクションの一貫性

トランザクションでテーブルを作成または削除する場合、削除されたオブジェクトに関する欠落インデックス情報を含む行は、トランザクションの一貫性を保持するためこの動的管理オブジェクトから削除されます。不足しているインデックス機能の制限について説明します。

アクセス許可

SQL Server と SQL Managed Instance では、VIEW SERVER STATE アクセス許可が必要です。

SQL Database の Basic、S0、S1 サービス対象、および Elastic Pool のデータベースの場合、サーバー管理者アカウント、Microsoft Entra 管理者アカウント、または ##MS_ServerStateReader##サーバーロールのメンバーシップが必要です。他のすべての SQL Database サービス目標では、データベースに対する VIEW DATABASE STATE アクセス許可または ##MS_ServerStateReader## サーバーロールのメンバーシップのいずれかが必要です。

SQL Server 2022 以降でのアクセス許可

サーバーに対する VIEW SERVER PERFORMANCE STATE アクセス許可が必要です。

例

次の例では、現在のデータベースのインデックス候補が見つからない場合を返します。不足しているインデックス候補は、可能な場合は互いに、および現在のデータベース内の既存のインデックスと組み合わせる必要があります。インデックス候補が見つからない非クラスター化インデックスにこれらの候補を適用する方法について説明します。

SELECT
  CONVERT (varchar(30), getdate(), 126) AS runtime,  mig.index_group_handle,  mid.index_handle,
  CONVERT (decimal (28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) ) AS improvement_measure,
  'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns, '') + CASE
    WHEN mid.equality_columns IS NOT NULL
    AND mid.inequality_columns IS NOT NULL THEN ','
    ELSE ''
  END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
	INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
	INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28, 1),migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Note

Microsoft の Tiger ツールボックスの Index-Creation スクリプトでは、不足しているインデックス DMV を調べ、冗長なインデックス候補を自動的に削除し、影響の少ないインデックスを解析して、確認用のインデックス作成スクリプトを生成します。上記のクエリと同様に、インデックス作成コマンドは実行されません。 Index-Creation スクリプトは、SQL Server および Azure SQL Managed Instance に適しています。 Azure SQL Database の場合は、自動インデックスチューニングの実装を検討してください。

次のステップ

不足しているインデックス機能の詳細については、次の記事を参照してください。

次の方法で共有