Environment

All versions of Content Manager
SQL Server

Situation

Performance tuning is an important aspect of database tuning, and the index plays a vital role in it. A proper index can help you improve performance, while a poorly designed one can hinder it.

Resolution

To convert the information returned by sys.dm_db_missing_index_details into a CREATE INDEX statement for both memory-optimized and disk-based indexes, you should place equality columns before inequality columns. Together, they will form the key of the index. Add included columns to the CREATE INDEX statement using the INCLUDE clause. To determine the most effective order for the equality columns, order them based on selectivity: list the most selective columns first (leftmost in the column list). You can learn more in 'Tune nonclustered indexes with missing index suggestions,' including the limitations of the missing index feature.

The following example returns missing index suggestions for the current database. Missing index suggestions should be combined when possible with one another, and with existing indexes in the current database. 
 

SELECT CONVERT(VARCHAR(30), getdate(), 126) AS runtime
	,mig.index_group_handle
	,mid.index_handle
	,CONVERT(DECIMAL(28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure
	,'CREATE INDEX missing_index_' + CONVERT(VARCHAR, mig.index_group_handle) + '_' + CONVERT(VARCHAR, mid.index_handle) + ' 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 CONVERT(DECIMAL(28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (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;


You should pay attention to Avg_Estimated_Impact when you are going to create an index. The index creation script is also provided in the last column.