Technical Article

Finding missing indexes in sql server 2005

,

the following scripts helps in finding top 5 missing indexes using the new feature of DMV available in SQl Server 2005

Select Top 5 mid.database_id, mid.object_id, mid.statement as table_name, mig.index_handle as index_handle
      from 
      (
      select 
      (user_seeks+user_scans) * avg_total_user_cost * (avg_user_impact * 0.01) as index_advantage, migs.*
      from sys.dm_db_missing_index_group_stats migs
      ) as migs_adv,
      sys.dm_db_missing_index_groups mig,
      sys.dm_db_missing_index_details mid
      where
      migs_adv.group_handle = mig.index_group_handle and
      mig.index_handle = mid.index_handle
      order by migs_adv.index_advantage DESC

Please use to find the columns that should be included in the indexes.
http://msdn2.microsoft.com/en-us/library/ms345405.aspx


we can run queries like 

SELECT * FROM sys.dm_db_missing_index_details where index_hanle = 28 

to find the columns on which we need to create the indexes.

Rate

(2)

You rated this post out of 5. Change rating

Share

Share

Rate

(2)

You rated this post out of 5. Change rating