Most companies will have a fair amount of SQL databases and its likely that most of those databases are performing sub-optimally due to missing indexes. We can debate (for a long time) the pros and cons of indexes, but the undeniable reality is that having missing indexes on large tables create a lot of issues in production environments (including, slowness, over spend on hardware and even outages). So how do you get a sense of how good or bad a database is? As luck would have it, Microsoft have a number of dynamic views that store the data you are looking for.
View missing index suggestions in DMVs
You can retrieve information about missing indexes by querying the dynamic management objects (DMVs). The following query uses the missing index DMVs to generate a series of CREATE INDEX statements. The index creation statements can be used to help you run the relevant DDL, once you have review all the output.
SELECT TOP 30 CONVERT (varchar(30), getdate(), 126) AS runtime, CONVERT (decimal (28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) ) AS estimated_improvement, '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 FROM sys.dm_db_missing_index_groups mig JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle ORDER BY estimated_improvement DESC; GO