Index in SQL Server

Indexes are the most important factor for identifying the relevant data rows quickly.

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  

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

Just Copy and run the Create Index Statement.

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