Use SQL Server dynamic management view to confirm missing indexes)

With many new features, we can see that in SQL Server 2005 and SQL Server 2008, Microsoft has introduced some dynamic management views to help identify potential index candidates based on query history.

These dynamic management views are:

N sys. dm_db_missing_index_details-return detailed information about the missing index.

N sys. dm_db_missing_index_group_stats-return the summary of the missing index group.

N sys. dm_db_missing_index_groups-the missing index information of a specific group is returned.

N sys. dm_db_missing_index_columns (index_handle)-returns information about the database table columns missing in an index. This is a function that requires passing index_handle.

Similar to the tracking statistics of most dynamic management views, these operations are basically the same when the SQL server instance is restarted and the data is completely cleared. Therefore, if you work in a test environment and restart your SQL server instance, these views may not return data.

To start the instance, we will use an Instance obtained from the SQL Server 2005 online help. This requires that a table be queried from the adventureworks database and no index on stateprovinceid, as shown below:

　　　USE AdventureWorks;
GO
SELECT City, StateProvinceID, PostalCode
FROM Person.Address
WHERE StateProvinceID = 1;
GO

Once we run the preceding query, the data should be available in the dynamic management view. Let's take a quick look at each query.

The first query retrieves data from the SYS. dm_db_missing_index_details view. This may be the most useful query, because it shows object_id, equality_columns, and inequality_columns. In addition, we can obtain other specific information about the contained columns.

Select * From SYS. dm_db_missing_index_details

Figure 1

Therefore, from the query we executed above, we can see the following information:

N equality_columns = "stateprovinceid", because this field and an equal operator are used in the WHERE clause. So SQL Server tells us that this will be a good choice for indexes.

N inequality_columns = "null". If you use other operators such as not equal, this field will have data, but because we use equal signs, no field will be used here.

N required ded_columns = This is another field used when an index is created. Because this query only uses city, stateprovinceid, and postalcode, stateprovinceid is processed in the index. When the index is created, the other two fields may be used as embedded fields.

The next index retrieves data from SYS. dm_db_missing_index_group_stats. This query gives us a better understanding of other statistical data, such as compilation, user search, and user scanning. Therefore, we can know how long the query will be accessed. If we create a new index based on this information, this will help us determine how often we can use an index to obtain data.

Select * From SYS. dm_db_missing_index_group_stats

Since this query is executed only once, our unique_compiles = 1 and our user_seeks = 1. If we run this query again, our user_seeks will increase.

Figure 2

The next view SYS. dm_db_missing_index_groups will provide us with index_group_handle and index_handle information.

Select * From SYS. dm_db_missing_index_groups

Figure 3

The results obtained from the preceding query are basically used to obtain data from the SYS. dm_db_missing_index_columns function. The index_handle value is passed to the next query, as shown in.

Select * From SYS. dm_db_missing_index_columns (1)

Figure 4

To obtain all the data displayed in a result set, the following query from SQL Server 2005 online help will provide the data for us.

　　　SELECT mig.*, statement AS table_name,
column_id, column_name, column_usage
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
ORDER BY mig.index_group_handle, mig.index_handle, column_id;

Figure 5

Summary

N Based on this example, we can see that we can create a new index on the stateprovinceid field of the adventureworks. Person. Address Table, or including columns city and postalcode.

N note that when you add or delete an index in a table, all the statistics of the missing index will be completely cleared in this table.

N although this may not be perfect, there are some limitations, but it at least gives us some information we never knew before when using the SQL Server old version.