SQL Server How to view index deletions and DMV usage Introduction _mssql

When you find that the database query performance is very slow, everyone will think of Gazzo to optimize the database query performance, but the face of a complex SQL statement, to find an optimized index combination of the human brain, is really not a very simple thing.

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

The first is the use of the DMV

The second is the use of DTA (Database Engine Tuning Advisor) DB Engine Tuning Advisor

This article mainly talks about the first kind

After SQL2005 , when SQL Server compiles any sentence, it evaluates

This remark is not missing the support of any index, 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 under the current database, and he is for all the statements that SQL Server has run since it started.

Rather than for a particular query. The DBA can see which forms SQL Server has the most "opinion" about him.

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

1, Index_handle: Identify a specific missing index. The identifier is unique on the server. Index_handle is the key to this table

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

3, OBJECT_ID: Identify index missing table

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

The predicate forms the following: Table.column =constant_value

5. Inequality_columns: A comma-delimited list of columns that form unequal predicates, such as the following predicate: Any comparison operator outside the table.column > constant_value "=" represents inequality.

6. Included_columns: A comma-delimited list of the covered columns for the query (simply the fields following the Select).

7, statement: The name of the table missing the index

For example, the following query results

Then you should create such an index

Copy Code code as follows:

CREATE INDEX Idx_salesorderdetail_test_productid_includeindex on Salesorderdetail_test (ProductID) INCLUDE ( SalesOrderID)

Create an index on the ProductID, SalesOrderID as an index of the included column

Precautions:

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

Missing index information is retained only until you restart SQL Server. If the database administrator wants to retain the missing index information after the server is reclaimed,

You should periodically make a backup copy of missing index information

Sys.dm_db_missing_index_columns (Index_handle)

Returns information about database table columns that are missing indexes (excluding spatial indexes), Sys.dm_db_missing_index_columns is a dynamic management function

Field explanation

Index_handle: An integer that uniquely identifies the missing index.

Sys.dm_db_missing_index_groups

Returns information about missing indexes (excluding spatial indexes) that are contained in a specific missing index group

Sys.dm_db_missing_index_group_stats

Returns summary information for missing index groups, excluding spatial indexes

This view is simply an estimate of how much performance can be improved by such an index.

One field is more important:

Avg_user_impact: After implementing this missing index group, the average percentage gain that the user queries may gain. This value indicates that if the missing index group is implemented, the query cost will be averaged down by that percentage.

That is, adding this missing index, the percentage of performance can increase

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

Copy Code code as follows:

--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 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 Code code 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 based on Dm_db_missing_index_details.

I reckon the SQL Server index optimizer is also using the "sys.dm_db_missing_index_details" DMV

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

I don't know how total cost and improvement measure work out at the moment.

Attention:

Finally, we need to pay attention, although the recommendations of the DMV are more reasonable.

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

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

Its accuracy, but also to confirm

The above several DMV field explanations, 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