#Optimization TIP: Finding the Missing Indexes in your Sql Database.

Following Query will help you find the missing indexes in your database along with Total Costing of that Index. 

PS: Adding new Indexes is a tricky job. It may vary from scenario to scenario. You can add Indexes having highest Total costing and having least or none included columns. Complex Composite Indexes should be avoided.

SELECT *
FROM (
 SELECT OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) AS [TableName]
  ,ISNULL(dm_mid.equality_columns, '') + CASE 
   WHEN dm_mid.equality_columns IS NOT NULL
    AND dm_mid.inequality_columns IS NOT NULL
    THEN ','
   ELSE ''
   END + ISNULL(dm_mid.inequality_columns, '') [Column]
  ,dm_mid.included_columns
  ,ROUND(dm_migs.avg_total_user_cost * dm_migs.avg_user_impact * (dm_migs.user_seeks + dm_migs.user_scans), 0) AS [Total Cost]
  ,'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '') + CASE 
   WHEN dm_mid.equality_columns IS NOT NULL
    AND dm_mid.inequality_columns IS NOT NULL
    THEN '_'
   ELSE ''
   END + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns, ''), ', ', '_'), '[', ''), ']', '') + ']' + ' ON ' + dm_mid.statement + ' (' + ISNULL(dm_mid.equality_columns, '') + CASE 
   WHEN dm_mid.equality_columns IS NOT NULL
    AND dm_mid.inequality_columns IS NOT NULL
    THEN ','
   ELSE ''
   END + ISNULL(dm_mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
 FROM sys.dm_db_missing_index_groups dm_mig
 INNER JOIN sys.dm_db_missing_index_group_stats dm_migs ON dm_migs.group_handle = dm_mig.index_group_handle
 INNER JOIN sys.dm_db_missing_index_details dm_mid ON dm_mig.index_handle = dm_mid.index_handle
 WHERE dm_mid.database_ID = DB_ID()
 ) x
ORDER BY x.[Total Cost] DESC
GO

Find the list of indexes in sql database.

Tip #5 Find List of Indexes in Sql Database.

I am posting some of the most useful queries which help a dba/ developer to optimize database performance. Please reference to different Tips in other blogs as well.

Following query will help in getting the list of all indexes in your database. Run the following query in your sql server query window:

SELECT *
INTO #tmp
FROM (
 SELECT DISTINCT T.[name] AS [table_name]
  ,I.[name] AS [index_name]
  ,AC.[name] AS [column_name]
  ,I.[type_desc]
  ,I.[is_unique]
  ,I.[data_space_id]
  ,I.[ignore_dup_key]
  ,I.[is_primary_key]
  ,I.[is_unique_constraint]
  ,I.[fill_factor]
  ,I.[is_padded]
  ,I.[is_disabled]
  ,I.[is_hypothetical]
  ,I.[allow_row_locks]
  ,I.[allow_page_locks]
  ,IC.[is_descending_key]
  ,IC.[is_included_column]
 FROM sys.[tables] AS T
 INNER JOIN sys.[indexes] I
  ON T.[object_id] = I.[object_id]
 INNER JOIN sys.[index_columns] IC
  ON I.[object_id] = IC.[object_id]
 INNER JOIN sys.[all_columns] AC
  ON T.[object_id] = AC.[object_id]
   AND IC.[column_id] = AC.[column_id]
 WHERE T.[is_ms_shipped] = 0
  AND I.[type_desc] <> 'HEAP'
 ) m
ORDER BY m.table_name
 ,m.index_name

SELECT * FROM #tmp