Indexes are the most important factor for identifying the relevant data rows quickly. They are used for both identifying modification and data retrieval. Missing Index can have impact on performance. When SQL SERVER runs the queries, it first examines the tables / views and determines which indexes are uses. If these indexes are available then uses them. But if not available then it mark it in cached plan in internal data structure that you can view by using DMV (Dynamic Management View).

To identifying most missing index use following DMVs

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 the effectiveness of the index.

sys.dm_db_missing_index_groups

This is a linking DMV, linking the previous two

DMVs together.

Following query indentify the missing index information.

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

Img-1.jpg

Description of the Query

Total Cost

1) avg_total_user_cost => Average cost of the user queries that could be reduced by the index in the group.

2) avg_user_impact => Average percentage benefit that user queries could experience if this missing index group was implemented. The value means that the query cost would on average drop by this percentage if this missing index group was implemented.

3) user_seeks => Number of seeks caused by user queries that the recommended index in the group could have been used for.

4) user_scans => Number of scans caused by user queries that the recommended index in the group could have been used for.

Table Name

Database Table Name

equality_columns

list of columns that contribute to equality predicates of the form: table.column =constant_value

inequality_columns

list of columns that contribute to inequality predicates, for example, predicates of the form: table.column > constant_value Any comparison operator other than "=" expresses inequality.

included_columns

list of columns needed as covering columns for the query.

Following Query Create Dynamic Missing Index Script which Total Cost more then 10

PRINT 'Missing Indexes: '
PRINT 'The "improvement_measure" column is an indicator of the (estimated) improvement that might '
PRINT 'be seen if the index was created. This is a unitless number, and has meaning only relative '


PRINT 'the same number for other indexes. The measure is a combination of the avg_total_user_cost, '
PRINT 'avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.'
PRINT ''
PRINT '-- Missing Indexes --'
SELECT CONVERT (varchar, getdate(), 126) AS runtime,
mig.index_group_handle, mid.index_handle,
CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS
improvement_measure,
'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL
(mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
PRINT ''
GO

Img-2.jpg

Just Copy and run the Create Index Statement.

CREATE INDEX missing_index_37_36 ON [CadilaReporting].[dbo].[EmpMst] ([dresigndate])
include ([vCompanyCode], [vEmpCode], [vFirstName])