SQL DBA Tools: Index Recommendations

Here's a t-sql query one can use to get a list of all table index recommendations in either SQL Server or Azure SQL.  The improvement measure column provides how much of an improvement that specific index will have on your database.  The user seeks and last_user_seek column provide details on the amount of and last use scenario of the particular index was supplied.

SELECT
     improvement_measure =  migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans)

     ,  create_index_statement = 'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
          + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
          + ' 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 + ')', '')

     , 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 migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (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