Wednesday, 4 April 2012

Missing Index



All of we know how important the index is. In this article I am trying to illustrate about the missing index and how we find the missing index.

Index is to improve the performance of the query. For the causes of missing index it takes long delay of performance of the query. I personally fall such conditions where a query takes more than 1 and ½ hour to complete the executions. So we understand that what the impacts of a missing index.

How we find the missing index

We can see the missing index by using the DMVs.  Use these SQL Statements to find the missing index.



SELECT  ROUND(s.avg_total_user_cost * s.avg_user_impact

        * (s.user_seeks + s.user_scans),0) AS [Total Cost],

        d.[statement] AS [Table Name], equality_columns,           

        inequality_columns, included_columns

FROM    sys.dm_db_missing_index_groups g

        INNER JOIN sys.dm_db_missing_index_group_stats s

                   ON s.group_handle = g.index_group_handle

        INNER JOIN sys.dm_db_missing_index_details d

                   ON d.index_handle = g.index_handle

ORDER BY [Total Cost] DESC

Here we use the TRANSACTION ISOLATION LEVEL READ UNCOMMITED. The purpose of this is to ensure the SQL that follows does not take out any locks and does not honour any locks.

The details of the related DMV are mentioned bellow.




Contains details of the 
the missing index relates to, together with how the index usage has been identified in queries (such as equality/inequality).


Contains details of how often the index would have been used, how it would be used (seek or scan), and a measure of its effectiveness.


This is a linking DMV, 
linking the previous two DMVs together.


Here the missing index gives the calculated columns name called "Total Cost". This uses a combination of the impact of the index, its cost, together with a measure of the number and type of index accesses to calculate its importance.

Finding Important Missing Index

It is not a good idea to blindly implement the suggested missing indexes, since indexes have a cost associated with them.

When data is inserted into a table, data may also need to be added to any associated indexes, this can increase the query duration. That said, some indexes may improve the performance of updates/deletes since these queries often have a WHERE clause. We can use a DMVs or the Statistics Time (or IO) command to measure the impact of adding an index.

Hope you like it.


Posted by: MR. JOYDEEP DAS






1 comment: