优化添加索引

1.找到对应的sql语句，右键显示估计的执行计划

2.查看是否确实索引信息

3.缺少索引信息的话，右键查看详细的索引信息

4.按照sql server 提供的确实索引信息更改索引名称后执行到数据库

5.执行索引后检查是否还缺少索引信息

查找缺失索引的方法

SELECT TOP 100
database_name = db_name(details.database_id)
, schema_name = SCHEMA_NAME(tb.schema_id)
, object_name = tb.name
, dm_migs.avg_user_impact
, avg_estimated_impact = dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans)
, last_user_seek = dm_migs.last_user_seek
, create_index =
‘CREATE INDEX [IX_’ + OBJECT_NAME(details.OBJECT_ID,details.database_id) + ‘_’

• REPLACE(REPLACE(REPLACE(ISNULL(details.equality_columns,’’),’, ‘,’_’),’[‘,’’),’]’,’’)
• CASE
  WHEN details.equality_columns IS NOT NULL
  AND details.inequality_columns IS NOT NULL THEN ‘_’
  ELSE ‘’
  END
  • REPLACE(REPLACE(REPLACE(ISNULL(details.inequality_columns,’’),’, ‘,’_’),’[‘,’’),’]’,’’)
  • ‘]’
  • ‘ ON ‘ + details.statement
  • ‘ (‘ + ISNULL (details.equality_columns,’’)
  • CASE WHEN details.equality_columns IS NOT NULL AND details.inequality_columns
    IS NOT NULL THEN ‘,’ ELSE
    ‘’ END
  • ISNULL (details.inequality_columns, ‘’)
  • ‘)’
  • ISNULL (‘ INCLUDE (‘ + details.included_columns + ‘)’, ‘’)
    FROM sys.dm_db_missing_index_groups AS dm_mig WITH(NOLOCK)
    INNER JOIN sys.dm_db_missing_index_group_stats AS dm_migs WITH(NOLOCK)
    ON dm_migs.group_handle = dm_mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details AS details WITH(NOLOCK)
    ON dm_mig.index_handle = details.index_handle
    INNER JOIN sys.tables AS tb WITH(NOLOCK)
    ON details.object_id = tb.object_id
    WHERE details.database_ID = DB_ID()
    ORDER BY Avg_Estimated_Impact DESC
    查询结果为缺失索引的表
    执行CREATE_INDEX语句可重建索引
    avg_estimated_impact字段值超过10000均可重建索引