SQL SERVER – How to find missing Index

SQL Server : Missing Index

Introduction

If you are looking for a solution to find the missing index in your SQL Server database, I can assume you already know the impact of the missing index on your applications. Well organized Index strategy plays a vital role in your application performance. If your application interacts a lot with SQL Server database having a large number of records and you are facing slow down performance then there are high chances of the missing index because indexes are the one who always helps and makes sure that SQL server fetches data faster and serves to you. Missing Index can make your query execute very slowly. Personally, I have noticed a query took a few seconds to process the data after creating a missing index which earlier used to take more than expected time.  It can also increase CPU usage to around 90 to 100%.

What is the missing Index?

Whenever we execute any query in SQL Server, Query going to be processed by SQL Server query optimizer in order to find the best index for the execution, and if it doesn’t find it, the SQL Server query optimizer generates a plan for your query and returns the result set, and then after stores that information about missing index in the DMV (Dynamic Management View). Later we can query all the missing index using scripts. You’ll read about that incoming section of this article.  Keep reading!

Getting Ready

By now, I hope that you have a better understanding of the requirement of the index and its impact on performance. While developing a database table, it is not always easy for us to predict the right column for creating an index. So, we used to generate an index that might be helpful based on some initial prediction. It might be possible that sometimes it would not even be used, and sometimes we would have badly need of other indexes as well in order to boost the performance of query execution, apart from the index we had created initially. So, it is quite obvious that a question can hit your mind that how to find the indexes that are not even generated. How we can predict which indexes are missing and which we need to create in order to boost performance? Do not worry here you will read everything.

There are 3 primary DMV(Dynamic Management Views)  which provide information about the Missing Indexes in SQL Server –

This DMV query is all about to return of the missing index what you need to create.
SELECT * FROM sys.dm_db_missing_index_details

For More Info – sys.dm_db_missing_index_details (Transact-SQL)

This DMV query is all about to return information like what missing indexes are contained in a specific missing index group
SELECT * FROM sys.dm_db_missing_index_groups

For More Info – sys.dm_db_missing_index_groups (Transact-SQL)

This DMV query is all about to return summary information about groups of missing indexes
SELECT * FROM sys.dm_db_missing_index_group_stats

For More Info – sys.dm_db_missing_index_group_stats (Transact-SQL)

This DMV is all about giving you an overall idea about what columns are missing in Index. it is based on the Index_Handle
SELECT * FROM sys.dm_db_missing_index_columns

Note: These DMVs(Dynamic Management Views) can keep information for a maximum of 500 indexes, and the information is lost whenever SQL Services restarts.
For More Info – sys.dm_db_missing_index_columns (Transact-SQL)

Script to execute and find Missing Index

The above query will show all the missing index suggestions for the specified database. It can pulls all required information from the sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups, and sys.dm_db_missing_index_details DMV. You can also make it specific just by un-comment the commented line AND statement(AND OBJECT_NAME(id.object_id, db.database_id)) and specifying your desired table name. This script can also generate a CREATE INDEX script for each record, hence you can have a better understanding of the index and decide if you want to use it or not.

Note:
When SQL Server services or the whole server where SQL Server is installed restarts, all the information stored for this missing index would be lost completely. So, you can consider to let the server keep running for one week at least, or any other time period. Then, you can easily have a proper list of all missing indexes.

Points to be Noted

  • Blindly create an IndexNever blindly creates an index just for the sake of the above script suggests you. Assign this task to someone who knows your database very well. He/she must have a better understanding of all fields and query for that particular table while creating the index. The above script is just for guidance. Sometimes it can give you an accurate result sometimes it can not. No one can guaranty you 100% accuracy for missing index. So while creating index please have some common sense. It is always suggested that we should not create more than 5 to 10 indexes per table.
  • Automatically add indexes – You should not automatically add indexes. You should add indexes if you know that they will be used by the queries to fetch records from the table.
  • Static tables – Such tables that change very little or maybe no changes going to have happened can be more heavily indexed that dynamic tables. This never means you should have an index on every column. Only those columns that need an index should have them.
  • Add the same index more than once on a table – You should not add either by accident or by willingly same index more than one time on the same table. For example, It is quite obvious that your table automatically creates an index for a primary(clustered index)  as well as a unique key(non clustered index) column. You may want to create an index for the same column with different index name. SQL Server will never stop you to create the same index over and over, even when this is considered a bad idea. It can hamper the performance of query execution.
  • Drop unused indexes – It is always suggested that you should drop such indexes which is quite often or never used. You can use Query Optimizer in order to find out the unused index. Unused indexes can badly impact on performance as given below
    • It can cause slow data modifications,
    • It can also cause unnecessary I/O reads when reading pages,
    • It can also cause of waste space in your database,
    • It can increase the amount of time it takes to backup and restores databases

If you have any issues while executing scripts and getting required missing index data in SQL Server. Please do let us know by dropping a comment in the comment section. Thanks for reading.

About Ravi Ranjan Kumar 31 Articles
An Indian who Living, Loving & Learning Technology with different tastes and willing to share knowledge and thoughts.

Be the first to comment

Leave a Reply