Anders Lybeckers Weblog!

Finding Missing Indexes with DMVsSome time ago I wrote written about easy index wins for SQL Server 2005.

SQL server maintains statistics about indexes you should consider creating. This time I’ll show you a DMV (Dynamic Management View) that lists index candidates. This method works for SQL Server 2005 SP2 and later versions.

The query is based on three DMVs and returns index candidates where the calculated improvement is more than 10%:

SELECT
  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure_pct,
  QUOTENAME(db_name(mid.database_id)) AS [database],
  QUOTENAME(OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id)) AS [schema],
  QUOTENAME(OBJECT_NAME(mid.object_id, mid.database_id)) AS [table],
  'CREATE INDEX [missing_index_' + CONVERT(varchar(64), NEWID()) + ']'
  + ' ON ' + mid.statement
  + ' (' + ISNULL (mid.equality_columns, '')
  + CASE
      WHEN mid.equality_columns IS NOT NULL
	    AND mid.inequality_columns IS NOT NULL THEN ','
      ELSE ''
    END
  + ISNULL(mid.inequality_columns, '')
  + ')'
  + ISNULL(' INCLUDE (' + mid.included_columns + ')', '')
	  AS create_index_statement,
  migs.*,
  mid.database_id,
  mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
  INNER JOIN sys.dm_db_missing_index_group_stats migs
	ON migs.group_handle = mig.index_group_handle
  INNER JOIN sys.dm_db_missing_index_details mid
	ON mig.index_handle = mid.index_handle
WHERE
	migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) *
		(migs.user_seeks + migs.user_scans) > 10
ORDER BY
	migs.avg_total_user_cost * migs.avg_user_impact *
		(migs.user_seeks + migs.user_scans) DESC

It is important to note, that these are index candidates are only candidates and the improvements are based on estimates. The estimated improvement does not take extra disk space requirements and the maintenance of the indexes during updates, inserts and deletes. Furthermore it does not make recommendation about usage of clustered or non-clustered indexes.

This blog post is inspired by Bart Duncan’s Are you using SQL’s missing index DMVs?

 Anders Lybecker is an chief architect at Avior A/S, a consultancy firm in Copenhagen, Denmark. He holds a degree in software engineering specializing in software development. His primary expertise are the Microsoft .Net framework and SQL Server which he has been working with since the start of this century! He enjoys discussing technical topics, teaching and speaking at conferences.

Related posts:

1. Easy win – index candidates on SQL Server 2005
2. Transferring SQL Server logins
3. Free SQL Server tools
4. Change Data Capture (CDC) in SQL Server 2008
5. SQL Server build version

Tags: DMVs, Perfornance, SQL Server 2005, SQL Server 2008

This entry was posted on Monday, May 10th, 2010 at 10:06 pm and is filed under SQL Server, Useful tools. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.