Systems Engineering and RDBMS

SQL Server 2005: Missing Indexes Script

Posted by decipherinfosys on May 4, 2008

Here is a script to help identify the missing indexes in the databases – it is based off three DMV’s:

select *
sys.dm_db_missing_index_group_stats as DDMIGS
inner join sys.dm_db_missing_index_groups as DDMIG
on DDMIGS.group_handle = DDMIG.index_group_handle
inner join sys.dm_db_missing_index_details as DDMID
on DDMIG.index_handle = DDMID.index_handle
order by (user_seeks * avg_total_user_cost * avg_user_impact)

The columns database_id and object_id are for the DB and the table.  One can use DB_NAME() and the object_name() functions to get the actual names.  The columns that might be of an interest for indexing are in the columns: equality_columns, inequality_columns and included_columns.  The seek and scan counts mean that those are the number of seek or scans that would have been done if we had an index.  Average query execution cost as well as utilization timestamp columns are other additional columns to monitor.  The missing index would be made up of equality columns, followed by inequality and then the included columns.  I have seen through trial and error that SQL Server does not suggest indexes for the order by clauses (in conjunction with the filter criterias in the where clause) – it considers seek operations and secondary indexes only.

Please do note that the information that is presented by these DMVs is only for the queries that have executed or are executing at the time of running this SQL.  If one only compiles a query and does not execute it, that missing index information can be seen in the XML showplan but the counters in the above DMVs do not record it.

Sorry, the comment form is closed at this time.

%d bloggers like this: