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,
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