How SQL Server looks for missing indexes

Transferred from: http://www.cnblogs.com/lyhabc/archive/2013/02/10/2909761.html

When we find that database query performance is very slow, everyone will think of Gazzo to optimize database query performance,

But in the face of a complex SQL statement, finding an optimized index combination is really not a very simple thing for the human brain.

Fortunately, SQL Server provides two "automatic" features that give you advice on how to adjust the index

The first is the use of a DMV

The second is Database Engine Tuning Advisor using the DTA (Tuning Advisor)

This article is mainly about the first kind of

Since SQL2005 , when SQL Server compiles any sentence, it will evaluate it.

This sentence is not missing any index support, and if he thinks it is, he will also estimate if there is such an index

How much of his performance can be improved?

SQL Server 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, and he is targeting all statements that SQL Server has run since it was started.

Instead of targeting a single query. DBAs can look at what forms SQL Server has the most "opinion" about him

The following is an explanation of the various fields of this DMV:

1. Index_handle: Identifies a specific missing index. The identifier is unique within the server. Index_handle is the key for this table

2. DATABASE_ID: Identifies the database where the table with the missing index resides

3. object_id: A table that identifies the missing index

4. Equality_columns: A comma-separated list of the columns that make up the equality predicate, which field is missing the index is listed here (Simply the filter field after the where),

The predicate is in the following form: Table.column =constant_value

5. Inequality_columns: A comma-separated list of columns that make up unequal predicates, such as predicates in the form of: table.column > constant_value "=" Any comparison operator that represents inequality.

6. Included_columns: A comma-separated list of the covered columns used for the query (in simple terms, the field behind the Select).

7. Statement: Name of table with missing index

For example, the following query results

Then you should create such an index

Create an index on ProductID, SalesOrderID as an index of an inclusive column

Precautions:

The information returned by Sys.dm_db_missing_index_details is updated when the query optimizer optimizes the query and is therefore not persisted.

The missing index information remains only until you restart SQL Server. If the database administrator wants to keep the missing index information after the server is recycled,

You should make a backup copy of the missing index information periodically

Sys.dm_db_missing_index_columns (Index_handle)

Returns information about a database table column that is missing an index (excluding a spatial index), Sys.dm_db_missing_index_columns is a dynamic management function

Field interpretation

Index_handle: An integer that uniquely identifies the missing index.

Sys.dm_db_missing_index_groups

Returns information about missing indexes (excluding spatial indexes) contained in a particular missing index group

Sys.dm_db_missing_index_group_stats

Returns summary information for missing index groups, excluding spatial indexes

This view is, in a word, an estimate of how much performance can be improved by this index.

One field is more important:

Avg_user_impact: When this missing index group is implemented, the average percent gain that a user query may receive. This value indicates that if this missing index group is implemented, the query cost will decrease by this percentage on average.

That is, the percentage of performance can be increased by adding this missing index

The following is an example of MSDN, with a missing index group handle of 2

The following is an example of MSDN, with a missing index group handle of 2

1--Queries provide the names of databases, schemas, and tables that have missing indexes. It also provides the name of the column that should be used for the index key2Use[AdventureWorks]3GO4SELECT Migs.group_handle, Mid.*5From Sys.dm_db_missing_index_group_statsAsMiGs6INNERjoin sys.dm_db_missing_index_groups as Mig 7 on (Migs.group_handle = Mig.index_group_handle)  8 inner join sys.dm_db_ Missing_index_details as mid  9 on (mig.index_handle  Mid.index_handle) 10 = 2   

Example code:

1Use[AdventureWorks]--To query for databases with missing indexes2GO3SELECT*From Sys.[Dm_db_missing_index_details]4SELECT*from sys. [dm_db_missing_index_groups ]5 select * from sys. [dm_db_missing_index_group_stats]6 select * from sys. [dm_db_missing_index_columns  "(1) 1:1 is based on dm_db_missing_index_details      

I estimate that the SQL Server index optimizer of the XX hero also uses the "sys.dm_db_missing_index_details" this DMV

Just looked, seems to have a typo: Total cost is not totol cost

For the time being, I don't know how total cost is calculated with improvement measure.

The above several DMV fields are explained, you can look at MSDN, 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

Attention:

Finally, we need to pay attention to this, although the DMV gives the advice is relatively reasonable.

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

The impact on other statements is not considered, and the cost of maintaining the index is not considered, so it is very one-sided.

Its accuracy, but also to confirm

How SQL Server looks for missing indexes