利益SQL Server 2005 DMVs调优(英文)

It has been always challenging for database administrators to identity indexes that are missing on a table. In SQL Server 2005, Microsoft introduced Dynamic Management Views (DMVs). The role of DMVs is to return SQL Server state information; which can be used by database administrators and database developers to monitor the health of an SQL Server Instance and identify potential performance issues

Identify Missing Indexes Using SQL Server DMVs

By : eysion

Jan 11, 2009

Performance tuning is one of the major that database administrators need to perform when supporting very large databases. Analysis of present index usage is an initial stage of

performance tuning. In the previous article titled, how to check whether all the indexes on a table are used effectively or not was explained. The next task is how to analyse and fix index fragmentations, covered in the previous article titled

It has been always challenging for database administrators to identity indexes that are missing on a table. In SQL Server 2005, Microsoft introduced Dynamic Management Views (DMVs). The role of DMVs is to return SQL Server state information; which can be used by database administrators and database developers to monitor the health of an SQL Server Instance and identify potential performance issues. DMVs reflect all the activities on the instance of SQL Server since the last restart. Unfortunately in SQL Server editions prior to SQL Server 2005 there is no easy way to identify missing indexes on a table. In SQL Server 2000 the only way to identify if an index needs to be created is to capture a workload in SQL Profiler and then run it against the Index Tuning Wizard. However, in order to access the information stored in DMVs you need to have VIEW SERVER STATE permissions. Only members of the sysadmin fixed server role can grant VIEW SERVER STATE permissions to other users. In this article example demonstrating how to identify missing indexes on a table is provided.

The Dynamic Management Views (DMV) which can be used to identify missing indexes on a table are:

sys.dm_db_missing_index_details

sys.dm_db_missing_index_group_stats

sys.dm_db_missing_index_groups

sys.dm_db_missing_index_columns

Below is an example explaining how to analyse missing indexes on a table using Dynamic Management Views. This example uses the Sales.Store table which is available in AdventureWorks database.

Identify Existing Indexes on a Table

第1页下一页