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.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

 

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.

DMV	Description
sys.dm_db_ missing_index_details	Contains details of the  database/schema/table  the missing index relates to, together with how the index usage has been identified in queries (such as equality/inequality).
sys.dm_db_missing_ index_group_stats	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.
sys.dm_db _missing_index_groups	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