How to check missing indexes in SQLSERVER

SQLSERVER how to check missing indexes when you find that the database query performance is very slow, you will think of adding indexes to optimize the database query performance, but in the face of a complicated SQL statement, finding an optimized index combination is not very easy for the human brain. Fortunately, SQLSERVER provides two automatic functions. How can this problem be solved?

SQLSERVER how to check missing indexes when you find that the database query performance is very slow, you will think of adding indexes to optimize the database query performance, but in the face of a complicated SQL statement, finding an optimized index combination is not very easy for the human brain. Fortunately, SQLSERVER provides two automatic functions. How can this problem be solved?

How to check missing indexes 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 complex SQL statement, finding an optimized index combination is not very easy for the human brain to use virtual hosts.

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

After SQL2005, SQL Server will evaluate any statement compiled by SQL Server,

Is there a lack of indexing support for the website space? If he thinks it is, he will predict 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.

Idx_SalesOrderDetail_test_ProductID_IncludeIndex ON SalesOrderDetail_test (ProductID) INCLUDE (SalesOrderID), US space