SupportSQL

This query uses the dynamic management queries in SQL Server 2005. It analyzes and calculates a “measured_improvement” factor that is generally the best bang-for-buck when deciding which index recommendation to implement. So I would normally do a sort like this:

SELECT * FROM vwIndexing_CreateStatementsFromDMQueries v
ORDER BY v.DBName, v.improvement_measure desc

This is by database name and then the highest measured_improvement. Usually needing to bring performance relief to a specific application/database I therefore choose and create the ones that will fix the problem first, then rollout any other highly needed indexes identified.

The best part here is that the sql commands needed to create the indexes are all created by this view. So its as easy as query the view, copy the created_statement column, paste in SSMS, and execute.

Here is the view:

CREATE VIEW [dbo].[vwIndexing_CreateStatementsFromDMQueries]
AS 
—creates index statements from the dynamic management index queries, with database and object names      
SELECT TOP 100 PERCENT
    mi.index_group_handle
   ,midet.index_handle
   ,convert (decimal(28, 1), migrps.avg_total_user_cost * migrps.avg_user_impact * (migrps.user_seeks + migrps.user_scans)) AS improvement_measure
   ,’CREATE INDEX missing_index_’ + convert (varchar, mi.index_group_handle)
        + ‘_’ + convert (varchar, midet.index_handle) + ’ ON ’
        + midet.statement + ’ (’ + isnull(midet.equality_columns, ”)
        + case WHEN midet.equality_columns IS NOT NULL
                    AND midet.inequality_columns IS NOT NULL THEN ‘,’
               ELSE ”
          END + isnull(midet.inequality_columns, ”) + ‘)’ + isnull(’ INCLUDE (’ + midet.included_columns + ‘)’, ”) AS create_index_statement
   ,migrps.*
   ,midet.database_id
   ,db_name(midet.database_id) AS DBName
   ,midet.[object_id]
   ,object_name(midet.[object_id], midet.database_id) AS ObjectName
FROM
    sys.dm_db_missing_index_groups mi
INNER JOIN sys.dm_db_missing_index_group_stats migrps ON migrps.group_handle = mi.index_group_handle
INNER JOIN sys.dm_db_missing_index_details midet ON mi.index_handle = midet.index_handle
WHERE
    convert (decimal(28, 1), migrps.avg_total_user_cost * migrps.avg_user_impact * (migrps.user_seeks + migrps.user_scans)) > 1000 —constrain to a minimum
ORDER BY
    convert (decimal(28, 1), migrps.avg_total_user_cost * migrps.avg_user_impact * (migrps.user_seeks + migrps.user_scans)) DESC —by improvement_measure

Here I do some sorting in Excel and determine the best to implement, and create the process document for auditors. Here’s the data, the chart of this is below:

This pie chart shows the databases most able to benefit from the proposed queries. Inversely is that these databases are experiencing high levels of traffic, where optimizations can be applied. If the server is showing signs of any bottleneck in i/o or cpu then this is a direct hit for an immediate performance improvement.

One mention is due regarding the type of system this is, mostly read and calculate, or is it mostly write fast gathering transactions? The latter could be negatively effected as more, and large indexes slow the updates and inserts into the tables, possibly holding longer locks to do so, and in turn causing blocking and lock timeouts to proliferate. It’s always a good idea to monitor wait stats (see our wait stats analysis series) and locking behavior before and after the indexes are applied.

Also, this procedure has been assembled from other resources and modified by me. I cannot take full credit here.