In this post, I provide one script to find missing indexes in SQL Server.
When you execute any SQL query, internally SQL Server also checks for requires indexes and prepare statistics. But still DBA has to take a decision based on query execute planner. SQL Server stores this different stats into three main Dynamic views which is used in below query.
SELECT TOP 25
ROUND(DMIGS.avg_total_user_cost * DMIGS.avg_user_impact * (DMIGS.user_seeks + DMIGS.user_scans),0) AS TotalCost
,DMID.[statement] AS TableName
FROM sys.dm_db_missing_index_groups AS DMIG
INNER JOIN sys.dm_db_missing_index_group_stats AS DMIGS
ON DMIGS.group_handle = DMIG.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS DMID
ON DMID.index_handle = DMIG.index_handle
ORDER BY 1 DESC
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.