Published on

July 6, 2010

New Features in SQL Server 2005: Missing Indexes

SQL Server 2005 introduced several new features that greatly improved the performance and efficiency of database queries. One of the standout features is the ability to find missing indexes, which can have a significant impact on query performance.

In previous versions of SQL Server, finding missing indexes required using SQL Profiler trace files and the Index Tuning Wizard. However, with the introduction of Dynamic Management Views (DMVs) in SQL Server 2005, it became much easier to identify the indexes that would benefit our applications.

There are several DMVs related to missing indexes:

  • sys.dm_db_missing_index_group_stats: Returns summary information about missing index groups, including the potential performance improvements that could be gained by implementing a specific group of missing indexes.
  • sys.dm_db_missing_index_groups: Provides information about a specific group of missing indexes, such as the group identifier and the identifiers of all missing indexes within that group.
  • sys.dm_db_missing_index_details: Returns detailed information about a missing index, including the name and identifier of the table where the index is missing, as well as the columns and column types that should make up the missing index.
  • sys.dm_db_missing_index_columns: Provides information about the database table columns that are missing an index.

Let’s take a look at an example to understand how these DMVs work. Suppose we have a table called [Person.Address] in the AdventureWorks database. By running the following code, we can see the existing indexes for this table:

use AdventureWorks;
exec sp_helpindex [Person.Address]

From the output, we can observe that there is no index for the ModifiedDate column in the [Person.Address] table. To generate an entry in the “sys.dm_db_missing_index_details” DMV, we can run a query like this:

select * from Person.Address where ModifiedDate = '01/01/2008'

Although this query may not return any results, SQL Server internally records that a query was executed and an index on the “ModifiedDate” column would have been beneficial. By querying the “sys.dm_db_missing_index_details” DMV, we can see the missing index information:

select * from sys.dm_db_missing_index_details

In the output, pay attention to the “equality_columns” field, which indicates that an index on the [Modified Date] column is missing or could be helpful.

To further explore the missing index information, we can run the following query:

select db_name(d.database_id) dbname, object_name(d.object_id) tablename, d.index_handle,
d.equality_columns, d.inequality_columns, d.included_columns, d.statement as fully_qualified_object, gs.*
from sys.dm_db_missing_index_groups g
join sys.dm_db_missing_index_group_stats gs on gs.group_handle = g.index_group_handle
join sys.dm_db_missing_index_details d on g.index_handle = d.index_handle
where d.database_id = d.database_id and d.object_id = d.object_id 
and object_name(d.object_id) = 'Address'

By running Query 1 multiple times and then Query 3, we can observe the “user_seeks” column in the output. This column indicates that every time a query is executed for which an index might be useful, SQL Server updates the missing index DMVs. This information is invaluable as it allows us to create indexes to support those queries.

The missing index feature is particularly useful in scenarios where queries are dynamically built, such as in a Siebel CRM database. In such cases, it can be challenging to design indexes in advance. However, with the help of the missing index feature, we can create indexes for queries that have a high “user_seeks” count for a particular column in a table.

SQL Server 2005’s DMVs for missing indexes are truly remarkable features that greatly enhance the performance optimization capabilities of the database engine. By leveraging these DMVs, database administrators and developers can identify and create indexes that significantly improve query performance.

For more information on the missing indexes feature, refer to the “About the Missing Indexes Feature” section in the SQL Server 2005 Books Online.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.