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.


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

 

 

 

 

 

No comments:

Post a Comment