Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.dm_db_missing_index_details

 

This is a view in the mssqlsystemresource database.

Returns detailed information about missing indexes, excluding spatial indexes. Information returned by sys.dm_db_missing_index_details is updated when a query is optimized by the query optimizer, and is not persisted. Missing index information is kept only until SQL Server is restarted. Database administrators should periodically make backup copies of the missing index information if they want to keep it after server recycling. To determine which missing index groups a particular missing index is part of, you can query the sys.dm_db_missing_index_groups dynamic management view by equijoining it with sys.dm_db_missing_index_details based on the index_handle column.
To convert the information returned by sys.dm_db_missing_index_details into a CREATE INDEX statement, equality columns should be put before the inequality columns, and together they should make the key of the index. Included columns should be added to the CREATE INDEX statement using the INCLUDE clause. To determine an effective order for the equality columns, order them based on their selectivity: list the most selective columns first (leftmost in the column list).

Column name Data type Description

index_handle

int

Identifies a particular missing index. The identifier is unique across the server. index_handle is the key of this table.

database_id

smallint

Identifies the database where the table with the missing index resides.

object_id

int

Identifies the table where the index is missing.

equality_columns

nvarchar(4000)

Comma-separated list of columns that contribute to equality predicates of the form:

table.column = constant_value

inequality_columns

nvarchar(4000)

Comma-separated list of columns that contribute to inequality predicates, for example, predicates of the form:

table.column > constant_value

Any comparison operator other than "=" expresses inequality.

included_columns

nvarchar(4000)

Comma-separated list of columns needed as covering columns for the query.

statement

nvarchar(4000)

Name of the table where the index is missing.

 Statement to see the most needed query

SELECT statement AS [database.scheme.table], column_id , column_name, column_usage, migs.user_seeks, migs.user_scans, migs.last_user_seek, migs.avg_total_user_cost, migs.avg_user_impact FROM sys.dm_db_missing_index_details AS mid CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle) INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle INNER JOIN sys.dm_db_missing_index_group_stats AS migs ON mig.index_group_handle=migs.group_handle ORDER BY mig.index_group_handle, mig.index_handle, column_id GO

See also Create all your missing indexes for a generation of statements of the missing indexes




Syntax
CREATE VIEW sys.dm_db_missing_index_details AS
	SELECT *
	FROM OpenRowset(TABLE MISSING_IDX_DETAILS)

 
Last revision 2008RTM
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash