DMV (Database Management View) and DMO (Database Management Objects) were added in 2005. There have been huge improvements in each new edition of SQL Server. It provides us a lot of useful information about SQL Server like – expensive queries, wait type, missing index…., and the list goes on and on…
In this blog, I am going to discuss Missing Indexes for all databases. It can help you find missing indexes that are going to have a very high impact on your workload.
But before you create the missing indexes on the tables, I would strongly request you to consider the below mentioned essential points.
- Try to figure out which all kind of queries (SELECT, INSERT, UPDATE, or DELETE) are using the table. If the table got more and more DML operation, I want to you analyze the missing index impact more thoroughly before you create it on the table.
- You need to make sure that you are not going to end up with a duplicate index on the table by creating the missing index. The duplicate or unwanted indexes can kill your database performance. For more details, you can refer the blog over-indexing can hurt your SQL Server performance.
- If you find there is an existing index’s columns are pretty much matching with the suggested missing index’s columns. I would request you to leverage the existing index by making it a wider index instead of creating one more index on the table. Just as a warning – When I am saying to have a wider index, it doesn’t mean that I am saying you to add all column in the current index to make it wider.
Script to find Missing Indexes for all databases in SQL Server
SELECT migs.user_seeks as [Estimated Index Uses], migs.avg_user_impact [Estimated Index Impact %], migs.avg_total_user_cost[Estimated Avg Query Cost], db_name(mid.database_id) AS DatabaseID, OBJECT_SCHEMA_NAME (mid.OBJECT_ID,mid.database_id) AS [SchemaName], OBJECT_NAME(mid.OBJECT_ID,mid.database_id) AS [TableName], 'CREATE INDEX [IX_' + OBJECT_NAME(mid.OBJECT_ID,mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,''),', ','_'),'[',''),']','') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(mid.inequality_columns,''),', ','_'),'[',''),']','') + ']' + ' 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 + ') WITH (MAXDOP =?, FILLFACTOR=?, ONLINE=?, SORT_IN_TEMPDB=?);', '') AS [Create TSQL], mid.equality_columns, mid.inequality_columns, mid.included_columns, migs.unique_compiles, migs.last_user_seek FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK) INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle ORDER BY [Estimated Index Uses] DESC OPTION (RECOMPILE);
I hope the script help you to align your indexes. Please let me know your thought on the script by leaving a comment.
The post Find Missing Indexes For All SQL Server Databases appeared first on .