SQL Server Missing Index Analysis

We get little confused, when the word INDEX comes into the picture? We ask so many questions to our-self before creating a non-cluster index, as far as the performance is concern.

As we are aware that the non-clustered indexes plays a very vital role in performance. Indexed keys, order of indexed keys, included columns, order of included columns, fill factor etc are the most important factors for an index. Index has a thumb rule of 50-50. Some indexes will make you feel like in heaven and some will give you a reverse feel of hell.

SQL Server 10.0 made it very easy for us by providing DMVs. In earlier versions of SQL Server, profiler was the only option to analyze and find the missing indexes.

What is a missing Index?

SQL server is intelligent enough to analyze our query before executing them on the server. You might have seen some green color texts (Missing Index Details) pops up in execution plans, when there is an index suggestion from SQL server. SQL server gives hint that, this index could have been used to perform the query better.

Note: Missing index suggestion can only be seen, if you have the execution plan enabled. It’s very difficult to check the execution plans for every queries being fired to the DB from application in a prod environment unless with the help of profiler. Now the question is who saves these details and where to find these details?

Answer: sys.dm_db_missing_index_details DMV.

missing index analysis (pic 1.0)
missing index analysis (pic 1.0)

Looks like the above query is doing a clustered index scan on Sales.Customer table. see we had the actual execution plan ON and that’s why we are able to get the missing index detail. This missing index has a performance impact of 91.98% on the whole batch. You can right click and go to ‘Missing Index Details.. ‘ to get the whole script of missing index on a new query window.

Now, we got a little idea about missing indexes. Next, how can we query these information from our DB?

You can do a select * on sys.dm_db_missing_index_details DMV to get missing index details but here We will tweak little bit to generate missing index scripts dynamically with some good analytic information, which will help us to decide whether a new index will be really helpful for the table or not?

missing index analysis (pic 1.1)

[DB Name]: Data base name on which you fired the given query for missing indexes.
[Table Name]: Non clustered index needs to be created on this table.

[Missing Index SQL Script]: Auto generated script for missing index.
[User Seek]: How many times SQL server found this missing index or how many times the query being fired.
[Estimated Impact]: How much performance impact will be gained on the cost of the missing index.
[Last User Seek]: Last time the query was fired or last time SQL server suggested us about the missing index.

[NC Index]: How many non-clustered indexes are already available on the table?
[Is Clust Index]: Is there a clustered index available on the table or not?
[Total Index Size]: Total index size in KB on datafile/disk including clustered index.

missing index analysis (pic 1.2)

Wow! we got the index script without really coding it!!

The above index script is generated in [Missing Index SQL Script] column. I have gone ahead and created the same non-clustered index on Sales.Customer table by this script. Now lets analyze the index in detail.

Index key is created on TerritoryID which is a equality column (=) in where clause. Index keys can be also created on non-equality columns like >, <, != etc in where clause.

Included columns are our select list with proper order.

Check this out, after a non clustered index on Sales.Customer table.

missing index analysis (pic 1.3)

Wow Index seek!! The same query is performing healthier than previous. We see the index seek on the non clustered index, we just created. That’s a good sign however!

Now compare (Pic 1.0) with (1.3). check the sub-tree cost and operator cost gone down drastically (0.0999411 to 0.010185) That means we got a huge performance improvement.

Non-clustered index plays a very big role when performance is in consideration. BTW, this little script helped my friend NG, to get missing indexes on his DB project. Normally developers do not think about indexes deeply. If you don’t know which index needs to be created to tune your DB, then this is the best option for you to start ahead. Once again these indexes are in result of SQL Server’s own analysis!!!

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

3 thoughts on “SQL Server Missing Index Analysis

  • September 2, 2014 at 12:24 pm

    NIce work. Deviprasad. 🙂

    • September 2, 2014 at 11:54 pm

      Thank you Sunil.


Leave a Reply

%d bloggers like this: