Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Missing Indexes in SQL Server 2005

By Ranga Narasimhan, (first published: 2008/09/16)

There are several new features in SQL Server 2005. There are a few features to help find missing indexes, which are some of the very good ones. How great it will be if you know what indexes you need to create based on your workload? In SQL Server 2000, we had to use SQL Profiler trace files and Index tuning wizard. But with SQL Server 2005 DMVs, we can easily figure out what indexes we need to create which would benefit our application.

The following are the missing index DMVs ( From SQL Server 2005 BOL)

sys.dm_db_missing_index_group_stats Returns summary information about missing index groups, for example, the performance improvements that could be gained by implementing a specific group of missing indexes.
sys.dm_db_missing_index_groups Returns information about a specific group of missing indexes, such as the group identifier and the identifiers of all missing indexes that are contained in that group.
sys.dm_db_missing_index_details Returns detailed information about a missing index; for example, it returns the name and identifier of the table where the index is missing, and the columns and column types that should make up the missing index.
sys.dm_db_missing_index_columns Returns information about the database table columns that are missing an index.

Let’s see what indexes are there for table [Person.Address] table in AdventureWorks database by running this code:

use AdventureWorks; 
exec sp_helpindex [Person.Address]

Fig:1

Indexes

I don’t see an index for ModifiedDate column for [Person.Address] table. So, to get a entry in the “sys.dm_db_missing_index_details” DMV, lets run a query like this:

Query: 1

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

You may not see any results for the query above, but SQL Server internally recorded that a query was run and a index on “ModifiedDate” column would have been very useful.

Query: 2

select * from sys.dm_db_missing_index_details:

Fig: 2

Missing Index

In Fig: 2, see the “equality_columns” field, which implies that a index on the [Modified Date] column is missing ( or might be helpful)

Query: 3:

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'

Run Query 1 several times. Now, run Query: 3,

Fig: 3

New indexes

In Fig 3, notice the “user_seeks” column. So every time a query is run, for which an index might be useful, SQL Server keeps updating the missing index DMVs. This is very valuable information, based on this you can create indexes to support those queries. Isn’t this cool! Yes, SQL Server 2005 rocks!

The DMVs for missing indexes are great new features. I work with a Siebel CRM database where queries are built dynamically. So it is hard to design indexes in advance. The missing index feature helps to me create indexes for those queries that have high “user_seeks” for a particular column in a table.

For more information see “About the Missing Indexes Feature” in SQL Server 2005 Books Online.

Total article views: 31838 | Views in the last 30 days: 7
 
Related Articles
ARTICLE

Stairway to SQL Server Indexes: Level 5, Included Columns

Included columns enable nonclustered indexes to become covering indexes for a variety of queries, im...

FORUM

Single Column Indexes

Are signle column indexes useful at all if queries use multiple columns?

ARTICLE

Stairway to SQL Server Indexes: Level 9, Reading Query Plans

Determining how, and if, SQL Server is using your indexes.

FORUM

Index on BIT column

Index on BIT column

FORUM

Group by Query Error

Group by Query Error

Tags
indexing    
sql server 2005    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones