Identifying Missing Indexes in SQL Server


Having an effective Database Indexing Strategy is a key to your application performance. When we think about optimizing SQL query performance, generally the first thing which comes to our mind are ‘Indexes’. And rightly so, because Indexes tremendously help in fetching your data faster from SQL Server.

Do you have slow running queries as a result of missing indexes in your tables?

I consider running the Missing Index DMV as an easy win. It is generally a helpful indicator about the indexes which are required by the SQL Server Engine to process the workload faster.

Dynamic management views (DMVs) return server state information and can be used to monitor the health of your database server instance and diagnose problems. It gives great insight about what is going on inside SQL Server.

You can identify missing indexes in your SQL queries by 3 ways primarily —

  • Running the Database Engine Tuning Advisor
  • Executing Missing Index Dynamic Management Views
  • SQL Server Engine prompts missing indexes when you generate Execution Plans in SSMS

There are 3 primary DMVs which provide information about the Missing Indexes in SQL Server —

Permission required to run the Missing Index DMV

You need to have VIEW SERVER STATE permission to be able to execute DMVs.

Query to identify Missing Indexes 

The above query has worked great for me. Execute the query against your database and any time the Index Advantage for a particular index is above 1000, start evaluating whether the particular index will be beneficial or not.

Word of Caution

Remember that like other DMVs, the information stored by the Missing Index DMV gets deleted when the SQL Server is restarted.

Do not blindly create new Indexes in your database based on the results of the Missing Index DMV or the suggestion from Database Engine Tuning Advisor.

The line is a thin one – you need enough indexes to speed up your SELECT queries and at the same time you should not create redundant indexes which could potentially slow down your UPDATE/DELETE operations.

Always keep in mind the flip side of the coin — Having redundant indexes can decrease the performance of your data manipulation queries – INSERT/UPDATE/DELETE. And not to forget the additional disk space that is needed to store them. 

Reference

Tune Your Indexing Strategy with SQL Server DMVs

One thought on “Identifying Missing Indexes in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s