1. Using Dynamic Management Views:
Limitations: — It can lead to multiple recommendations that ends up with duplicate indexes; that means, it does not consider existing indexes. — It does not even consider existing recommendations for another recommendation as well. — It is based on individual Query, not on the workload of the system. — There is a limit of DMV entries per instance, so if you have lots of databases in a single instance, it may not give you the recommendations holistically. — DMV information will be lost/cleared when SQL Server restarts — This will not recommend – index type/partitioning/column store/unique constraints etc.
Select OBJECT_SCHEMA_NAME(mid.object_id,mid.database_id) AS Schema_Name, db_name(mid.database_id) As Database_Name, Object_name(mid.object_id,mid.database_id) As Object_Name, migs.avg_user_impact, mid.equality_columns, mid.inequality_columns, mid.included_columns From sys.dm_db_missing_index_groups mig Inner join sys.dm_db_missing_index_group_stats migs on mig.index_group_handle = migs.group_handle Inner join sys.dm_db_missing_index_details mid on mig.index_handle = mid.index_handle
2. Using Database Tuning adviser
3. Using Cached plans