How to view missing indexes and DMV usage in SQLSERVER

When you find that the database query performance is very slow, you will think of adding indexes to optimize the database query performance. However, in the face of a complicated SQL statement, finding an optimized index combination is for the human brain, it's really not a simple task.

Fortunately, SQLSERVER provides two "automatic" functions. We recommend that you adjust the index.

The first is to use DMV.

Second, database engine tuning advisor

This article focuses on the first

SlaveSQL2005In the future, when SQLSERVER compiles any statement, it will evaluate it,

Is there any index support missing in this sentence? If he thinks it is, he will predict that if there is such an index

How much can his performance be improved?

SQLSERVER has several dynamic management views

Sys. dm_db_missing_index_details

Sys. dm_db_missing_index_groups

Sys. dm_db_missing_index_group_stats

Sys. dm_db_missing_index_columns (index_handle)

Sys. dm_db_missing_index_details

This DMV records all the missing index information in the current database. It targets all SQL Server statements that have been running since startup,

Instead of a query. DBA can see which tables SQL Server has the most "Opinions" on him.

The following describes the fields of the DMV:

1. index_handle: identifies a specific missing index. This identifier is unique on the server. Index_handle is the key of this table.

2. database_id: identifies the database where the table with missing indexes resides.

3. object_id: identifies the table with missing indexes.

4. equality_columns: comma-separated list of columns that constitute equal predicates. That is, if an index is missing, the column is listed here (in simple words, the filter field after where ),

The predicate format is as follows: table. column = constant_value

5. inequality_columns: A comma-separated list of columns that constitute unequal predicates. For example, any comparison operators other than table. column> constant_value "=" indicate not equal.

6. included_columns: A comma-separated list of covered columns used for query (simply, the fields following the select statement ).

7. statement: name of the table with missing Indexes

For example, the following query result

You should create such an index.

Copy codeThe Code is as follows: create index idx_SalesOrderDetail_test_ProductID_IncludeIndex ON SalesOrderDetail_test (ProductID) INCLUDE (SalesOrderID)

Create an index on ProductID. The SalesOrderID is used as the index of the contained column.

Note:

The information returned by sys. dm_db_missing_index_details is updated when the query optimizer optimizes the query, so it is not persistent.

The missing index information is retained only until SQL Server is restarted. If the database administrator wants to retain the missing index information after the server recovers,

Backup copies with missing index information should be created on a regular basis.

Sys. dm_db_missing_index_columns (index_handle)

Returns information about the table columns in the database that lack an index (excluding a spatial index). sys. dm_db_missing_index_columns is a dynamic management function.

Field description

Index_handle: the integer that uniquely identifies the missing index.

Sys. dm_db_missing_index_groups

Returns information about missing indexes (excluding spatial indexes) in a specific missing index group.

Sys. dm_db_missing_index_group_stats

Returns the summary of the missing index group, excluding the spatial index.

To put it bluntly, this view is to estimate the performance of such an index.

One field is important:

Avg_user_impact: average percentage of earnings that a user query may obtain after the missing index group is implemented. This value indicates that if this missing index group is implemented, the query cost will decrease on average by this percentage.

That is to say, if this missing index is added, the performance can be increased by a percentage.

The following is an example provided by MSDN. The missing index group handle is 2.

Copy codeThe Code is as follows: -- query the names of the databases, architectures, and tables that provide missing indexes. It also provides the name of the column that should be used for the index key
USE [AdventureWorks]
GO
SELECT migs. group_handle, mid .*
FROM sys. dm_db_missing_index_group_stats AS migs
Inner join sys. dm_db_missing_index_groups AS mig
ON (migs. group_handle = mig. index_group_handle)
Inner join sys. dm_db_missing_index_details AS mid
ON (mig. index_handle = mid. index_handle)
WHERE migs. group_handle = 2

Sample Code:

Copy codeThe Code is as follows: USE [AdventureWorks] -- to query the database with missing Indexes
GO
SELECT * FROM sys. [dm_db_missing_index_details]
SELECT * FROM sys. [dm_db_missing_index_groups]
SELECT * FROM sys. [dm_db_missing_index_group_stats]
SELECT * FROM sys. [dm_db_missing_index_columns] (1) -- 1: 1 is found based on dm_db_missing_index_details.

I guess XX Daxia's SQLSERVER index optimizer also uses"Sys. dm_db_missing_index_details "DMV

After reading this, it seems that there is a typo: Total Cost is not Totol Cost.

I don't know how to calculate the Total Cost and Improvement Measure.

Note:

At last, you should note that the suggestions provided by DMV are reasonable.

However, the DBA still needs to confirm the suggestion. This suggestion is based entirely on the statement itself,

We did not consider the impact on other statements or the cost of maintaining indexes.

Its accuracy should also be confirmed

The above DMV fields are explained. You can take a look at MSDN, which is very detailed.

Sys. dm_db_missing_index_group_stats
Msdn: http://msdn.microsoft.com/zh-cn/library/ms345421.aspx

Sys. dm_db_missing_index_groups
Msdn: http://msdn.microsoft.com/zh-cn/library/ms345407.aspx

Sys. dm_db_missing_index_columns ([SQL _handle])
Msdn: http://msdn.microsoft.com/zh-cn/library/ms345364.aspx

Sys. dm_db_missing_index_details
Msdn: http://msdn.microsoft.com/zh-cn/library/ms345434.aspx