Find Missing Indexes For All SQL Server Databases

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

I hope the script help you to align your indexes. Please let me know your thought on the script by  leaving a comment.

Thanks!

Leave a comment

Your email address will not be published. Required fields are marked *