缺失索引

系统最初运行时，我们不可能一次将所需的索引全部创建。往往需要让系统运行一段时间，再从相应的系统视图(sys.dm_db_missing_index_group_stats)去判断是否缺失索引。
具有最高用户查询预期提高的缺失索引

-- 缺失索引语句
USE YourDB
GO
SELECT sys.objects.name     table_name
      ,mid.statement        full_name
      ,migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks+migs.user_scans) AS Impact
      ,migs.avg_user_impact * (migs.user_seeks+migs.user_scans) AS Avg_Estimated_Impact
      ,'CREATE NONCLUSTERED INDEX IDX_'+sys.objects.name+'_N ON ' 
      +sys.objects.name COLLATE DATABASE_DEFAULT 
      +' ( '+ISNULL(mid.equality_columns ,'') 
      +CASE 
            WHEN mid.inequality_columns IS NULL THEN ''
            ELSE CASE 
                      WHEN mid.equality_columns IS NULL THEN ''
                      ELSE ','
                 END+mid.inequality_columns
       END+' ) ' 
      +CASE 
            WHEN mid.included_columns IS NULL THEN ''
            ELSE 'INCLUDE ('+mid.included_columns+')'
       END 
      +';' AS               CreateIndexStatement
      ,mid.equality_columns
      ,mid.inequality_columns
      ,mid.included_columns
      ,migs.user_seeks,migs.user_scans
      ,migs.last_user_seek,migs.last_user_scan
FROM   sys.dm_db_missing_index_group_stats AS migs
       INNER JOIN sys.dm_db_missing_index_groups AS mig
            ON  migs.group_handle = mig.index_group_handle
       INNER JOIN sys.dm_db_missing_index_details AS mid
            ON  mig.index_handle = mid.index_handle
                AND mid.database_id = DB_ID()
       INNER JOIN sys.objects WITH (NOLOCK)
            ON  mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE  migs.group_handle IN (SELECT TOP(500) group_handle
                             FROM   sys.dm_db_missing_index_group_stats WITH (NOLOCK)
                             ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks+user_scans) DESC)
       AND OBJECTPROPERTY(sys.objects.OBJECT_ID ,'isusertable') = 1
       --AND sys.objects.name='AppAccessTokenRecord'
ORDER BY
       [Impact] DESC
      ,[full_name] DESC

View Code

以上语句可以得出缺失索引创建语句，依据实际情况确定是否创建。

转载于:https://www.cnblogs.com/Uest/p/5190575.html