How to find Missing non clustered Indexes

Yes, by rolling up your sleeves to some hard work and analyse slow-running queries and see what can be done with them. Query Store is a tremendous asset here, and if you have not enabled it for your database, do so first thing. When it has been on for a week, you can start digging.

The process is, by the way, not any different from identifying missing non-clustered indexes. Yeah, in that case there is a DMV that suggests non-clustered index, but I rarely look at the DMV. It has a fondness to suggest covering indexes. While such indexes can help to improve performance, following those recommendations blindly can result in a number of redundant indexes. And a problem is that what is a covering index this week, may not be next week when a new column is added to the SELECT list to meet business requirements.

If you were asking for SQL Server making recommendations about clustered indexes: No, there are no such recommendations.

a table can only have one clustered index. a clustered index is only useful if the query and order by can use the index and the query returns a large number of rows. if you often read the the whole table (all rows all columns) with an order by, then a clustered index will help performance.

you can also create a covered index (index contains all columns used in the query), which is just a performant as a clustered index.

in short, to detect missing or incorrect selection of clustered indexes, you need to do an analysis of your queries.

You can query SQL Server’s Dynamic Management Views to find missing non-clustered indexes for a particular database. You can try this approach: 

SELECT   
    DB_NAME(mid.database_id) AS DatabaseName,   
    OBJECT_NAME(mid.object_id, mid.database_id) AS TableName,   
    migs.unique_compiles,   
    migs.user_seeks,   
    migs.user_scans,   
    mid.equality_columns,   
    mid.inequality_columns,   
    mid.included_columns,   
    migs.avg_total_user_cost * migs.avg_user_impact AS ImprovementMeasure   
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   
WHERE mid.database_id = DB_ID()   
ORDER BY ImprovementMeasure DESC; 

Notes:

• This only reports indexes SQL Server thinks could help, based on query execution history. 
• Always review suggested indexes, some may be redundant or unnecessary. 
• After adding indexes, monitor usage with sys.dm_db_index_usage_stats. 

For a full guide on index types, creation, and maintenance, check out: 

SQL Index and Management Guide – dbForge Blog 

Hope it helps. 

Best, 
VG