SQL Server Utilities - Use SQL Server’s missing indexes feature

SQL Server exposes missing indexes metadata via the following 3 DMVs (Dynamic Management Views) sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups and sys.dm_db_missing_index_group_stats which combined in a simple query return valuable information of indexes that were detected as missing by the Optimizer and logged in those DMVs.

The information in these DMVs is updated by the execution of every query and an important fact to remember is that the information returned is based on data collected since the last sql server service startup as the DMVs do not preserve the information cross restarts.

The simplest way to get the last service start time is by querying sys.databases and see the create_date column for database_id 2 which is tempdb (since tempdb is recreated upon service restart).

The information returned from the missing indexes query is useful, and can be very practical and powerful, combined with your overall knowledge of the system you should be able to spot here key candidates and apply indexes that the system would benefit from. A poorly maintained system can get a significant boost in performance from just a day or 2 of DBA preforming an indexes tuning session.

It is important not to apply all the missing indexes automatically but to see a wider picture of the table you are looking at and the system.

Some key guidelines for consideration for missing index information returned by the query when creating indexes:

Verify there is no such index that already exists
Verify the new index to be applied cannot be merged/combined with an existing index by expanding the existing index. For example a missing index on logged on c1,c2,c3 can replace an existing index on c1,c2 (you can create the new index and then safely drop the old index or better take advantage of the drop_existing=on syntax which is an atomic operation instead of 2 separate commands). When modifying an existing index make sure to preserve its current properties such as UNIQUE etc..
Take into consideration the number of Seeks and Scans vs the number of Updates for the given uptime
Take into consideration the number of indexes already existing on the table. The more indexes the slower DML operations become
Take into consideration the table size and the expected new index size and consider creating the index compressed. Compressed indexes consume less storage which means less IO is done when reading and writing the index at the cost of more CPU

Here is the link to the script at my indexes project at GitHub.

Checkout this related post.

USE StackOverflow2010
SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
--SELECT DATEDIFF(DAY, create_date, CURRENT_TIMESTAMP)dif_dd, create_date FROM sys.databases WHERE database_id = 2

DECLARE @online bit; SELECT	@online = 0;  

SELECT TOP 50
  -- db_name(d.database_id)		AS [database]
	 SCHEMA_NAME(t.schema_id) [schema]
	,OBJECT_NAME(d.object_id) AS [object]
	,d.equality_columns                     
	,d.inequality_columns
	,d.included_columns
	,s.unique_compiles      
	,s.user_seeks           
	,s.user_scans           
	,s.last_user_seek          
	,s.last_user_scan          
	,s.avg_total_user_cost    
	,s.avg_user_impact    	
	,command = 'CREATE NONCLUSTERED INDEX IX_' + OBJECT_NAME(d.object_id) + '__' + REPLACE(REPLACE(REPLACE(ISNULL(d.equality_columns, ''), '[','') , ']',''), ', ','_') 
		+ CASE WHEN d.inequality_columns IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(d.inequality_columns,''), '[','') ,']','') ,', ','_')
		+ ' ON [' + SCHEMA_NAME(t.schema_id) + '].[' + OBJECT_NAME(d.object_id) + '] ('
		+ CASE WHEN d.equality_columns IS NOT NULL THEN d.equality_columns ELSE '' END 
		+ CASE WHEN d.inequality_columns IS NOT NULL AND d.equality_columns IS NOT NULL THEN ', ' + d.inequality_columns ELSE ISNULL(d.inequality_columns, '') END + ')'
		+ CASE WHEN d.included_columns IS NOT NULL THEN ' INCLUDE (' + d.included_columns + ')' ELSE '' END
		+ ' WITH (ONLINE = ' + CASE WHEN @online = 1 THEN 'ON);' ELSE 'OFF);' END
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
INNER JOIN sys.tables t ON t.object_id = d.[object_id]
WHERE 1=1
AND OBJECTPROPERTY(d.[object_id], 'IsMsShipped') = 0
AND t.is_memory_optimized <> 1 
AND d.database_id = DB_ID()
--AND OBJECT_NAME(d.object_id) NOT IN ( '','','','','','','','','','','','','','','','','','','','','','','','')
--AND OBJECT_NAME(d.object_id) LIKE ''
--AND avg_user_impact > 50
ORDER BY s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans) DESC;

--EXEC sp_helpindex2 @Table = '', @Schema = 'dbo', @IndexExtendedInfo = 0, @MissingIndexesInfo = 1, @ColumnsInfo = 1