Server的索引维护其实主要围绕下面三个问题进行

作者: www.9159.com  发布:2019-11-05

 

以下文章非原创,仅为分享、学习!!!

阅读原文请点击:
摘要: 索引维护是数据库日常维护中一项重要的任务,SQL Server的索引维护其实主要围绕下面三个问题进行展开。 索引过多 索引不足 索引碎片率 本文同样从这3个角度出发,介绍一些实用的日常维护方法和工具。

在SQL Server中,索引是优化SQL性能的一大法宝。但是由于各种原因,索引会被当做“银弹”滥用,一方面有些开发人员(甚至是部分数据库管理员)有一些陋习,不管三七二十一,总是根据所谓的"感觉"或“经验”先增加一些索引,而不管这些索引是否未被使用或是否合理。另外一方面在数据库的生命周期中,需求总是在变化,业务也在变化,有些当初创建的有效索引可能已经变成了unused index了。变成了数据库性能的累赘; 另外,部分数据库管理员其实很少清理索引(冗余索引,重复索引,未使用索引)。其实不管是出于性能考虑,还是数据库维护管理的需要,数据库中的未使用索引(unused index)都需要定期清理,因为这些未使用索引(unused index)不但不会提高查询性能,还会影响DML操作的性能、浪费存储空间等等。本文主要总结一下,如何找到识别、查找哪些未使用的索引(unused index)

 

索引维护是数据库日常维护中一项重要的任务,SQL Server的索引维护其实主要围绕下面三个问题进行展开。
索引过多
索引不足
索引碎片率

 

当大家发现数据库查询性能很慢的时候,大家都会想到加索引来优化数据库查询性能,

本文同样从这3个角度出发,介绍一些实用的日常维护方法和工具。
索引过多
索引过多是指每个表上面的非聚集索引很多,并且有些非聚集索引很少用到。 过多的索引,会导致增删数据的效率降低,数据库体积变大,索引以及统计信息的维护成本增加等负面影响,建议定期检查类似的索引,每个表上面的索引最好不要超过10个。

 

但是面对一个复杂的SQL语句,找到一个优化的索引组合对人脑来讲,真的不是一件很简单的事。

通过下面两个DMV,定期检查索引使用率,通过使用率决定是否需要该索引。sys.dm_db_index_operational_stats这个函数可以给出某个索引上面的insert,update和delete的操作情况。sys.dm_db_index_usage_stats这个视图可以给出访问索引的所有方法的操作概览。
--sys.dm_db_index_operational_stats
SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],
       I.[NAME] AS [INDEX NAME],
       A.LEAF_INSERT_COUNT,
       A.LEAF_UPDATE_COUNT,
       A.LEAF_DELETE_COUNT
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
       INNER JOIN SYS.INDEXES AS I
         ON I.[OBJECT_ID] = A.[OBJECT_ID]
            AND I.INDEX_ID = A.INDEX_ID
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1
1

   如何找到未使用索引呢? 在ORACLE数据库中提供了监控索引使用情况的功能。虽然在SQL Server中没有提供此类功能,但是提供了DMV视图sys.dm_db_index_usage_stats ,关于这个视图,详细信息可以参考官方文档,下面仅仅介绍需要用到的几个字段

 

--sys.dm_db_index_usage_stats
SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
         I.[NAME] AS [INDEX NAME],
         USER_SEEKS,
         USER_SCANS,
         USER_LOOKUPS,
         USER_UPDATES
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S
         INNER JOIN SYS.INDEXES AS I
           ON I.[OBJECT_ID] = S.[OBJECT_ID]
              AND I.INDEX_ID = S.INDEX_ID
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
2

 

好在SQLSERVER提供了两种“自动”功能,给你建议,该怎么调整索引

上述结果中,可以看到,CountryRegionCurrency和AddressType表中,有两个索引,没有使用过。 如果多次检查,这两个索引都还是没有使用过的话,建议将其删除。
索引不足
索引不足是指,要么缺少索引,要么有索引,但是没有覆盖所需的列,查询效果不好。 后者其实也可以归纳到索引不合适中。那么我们来看下,如何才能找到缺失的索引。

user_scans      用户查询执行的扫描次数。

 

SQL Server提供下面4个DMV以供查询missing index的情况。SQL Server重启后,系统视图中的内容就会更新,需要定期的将该信息保存下来。
sys.dm_db_missing_index_details 返回缺失的索引的详细信息。
sys.dm_db_missing_index_group_stats 返回缺失索引组的概要信息。
sys.dm_db_missing_index_groups 返回缺失索引组中有哪些缺失的索引。
sys.dm_db_missing_index_columns 返回表中缺失索引的列。
如何通过检测出来的缺失索引去新建索引,方法参考Using Missing Index Information to Write CREATE INDEX Statements 。

user_seeks      用户查询执行的搜索次数。

第一种是使用DMV

下面语句,在每个库上面执行下面的查询,查看推荐建立的索引,包括创建语句。不过在创建索引前,需要综合考量表中已有的索引,是否有可以合并的情况。
Use DB
SELECT
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_www.9159.com ,ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'

user_lookups    用户查询执行的书签查找次数。

 

  • 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()
    ORDER BY Avg_Estimated_Impact DESC
    GO
    3

user_updates    通过用户查询执行的更新次数。这表示插入、 删除,更新的次数,而不是受影响的实际行数。

第二种是使用DTA (database engine tuning advisor) 数据库引擎优化顾问

创建index时,推荐按照下述顺序进行。

                例如,如果你删除在一个语句中的 1000行,此计数递增 1

 

将相等数据行列在最前
将不相等的数据行列在相等的数据行后
将include数据行列在create index语句的include子句中
若要决定相等数据行的顺序,依据选择性排列这些数据行,将选择性最高的数据行排在最前
索引碎片率
新增、删除和修改数据时,数据库会自动维护索引。但时间长了之后,这些操作会造成数据不连续。这会对查找性能产生影响。

                Number of updates by user queries. This includes Insert, Delete, and Updates representing

这篇文章主要讲第一种

首先,观察索引碎片的严重程度。

                number of operations done not the actual rows affected. For example, if you delete 1000

 

内部不连续(Internal Fragmentation):数据页中有很多空闲空间;

                rows in one statement, this count increments by 1


外部不连续(External Fragmentation):

 

 

硬盘中摆放的分页或区不连续,也就是数据表或索引散落在多个范围中,以及存放数据表或者索引的页不是按照实例连续存放的。
逻辑数据顺序和实例在硬盘中的顺序不同。

我们可以使用下面SQL语句查找当前数据库中的未使用索引(unused index):

SQL2005以后,在SQLSERVER对任何一句语句做编译的时候,都会去评估一下,

  1. 用DBCC SHOWCONTIG观察数据不连续
    create index idCreditCard on CreditCard(CreditCardID)  with drop_existing
    DBCC showcontig(CreditCard,idCreditCard)
    阅读原文请点击:

 

 

SELECT 'SQL Server Instance Start with ' + CONVERT(VARCHAR(16),create_date,120)  FROM sys.databases

WHERE database_id =2;

    

SELECT  DB_NAME(diu.database_id)                  AS DatabaseName ,

        s.name +'.' +QUOTENAME(o.name)            AS TableName    ,

        i.index_id                                AS IndexID   ,

        i.name                                    AS IndexName        ,

        CASE WHEN i.is_unique =1 THEN 'UNIQUE INDEX'

           ELSE 'NOT UNIQUE INDEX'    END         AS IS_UNIQUE,

        CASE WHEN i.is_disabled=1 THEN 'DISABLE'

           ELSE 'ENABLE'            END           AS IndexStatus,

        o.create_date                             AS IndexCreated,

        STATS_DATE(o.object_id,i.index_id)        AS StatisticsUpdateDate,

        diu.user_seeks                            AS UserSeek ,

        diu.user_scans                            AS UserScans ,

        diu.user_lookups                          AS UserLookups ,

        diu.user_updates                          AS UserUpdates ,

        p.TableRows ,

        'DROP INDEX ' + QUOTENAME(i.name) 

        + ' ON ' + QUOTENAME(s.name) + '.'

        + QUOTENAME(OBJECT_NAME(diu.object_id)) +';' AS 'Drop Index Statement'

FROM    sys.dm_db_index_usage_stats diu

        INNER JOIN sys.indexes i ON i.index_id = diu.index_id

                                    AND diu.object_id = i.object_id

        INNER JOIN sys.objects o ON diu.object_id = o.object_id

        INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

        INNER JOIN ( SELECT SUM(p.rows) TableRows ,

                            p.index_id ,

                            p.object_id

                     FROM   sys.partitions p

                     GROUP BY p.index_id ,

                            p.object_id

                   ) p ON p.index_id = diu.index_id

                          AND diu.object_id = p.object_id

WHERE   OBJECTPROPERTY(diu.object_id, 'IsUserTable') = 1

        AND diu.database_id = DB_ID()

        AND i.is_primary_key = 0        --排除主键索引

        AND i.is_unique_constraint = 0         --排除唯一索引

        AND diu.user_updates <> 0              --排除没有数据变化的索引

        AND diu.user_lookups = 0

        AND diu.user_seeks = 0

        AND diu.user_scans = 0

        AND i.name IS NOT NULL                 --排除那些没有任何索引的堆表

ORDER BY ( diu.user_seeks + diu.user_scans + diu.user_lookups ) ASC,diu.user_updates DESC;

GO

这句话是不是缺少什么索引的支持,如果他认为是,他还会预估,如果有这麽一个索引

 

 

 

他的性能能提高多少

需要注意的几点:

 

 

 

1:sys.dm_db_index_usage_stats返回索引的被使用的信息,但是这个DMV视图中的数据是自数据库服务启动以来累计收集的数据(只要重启SQL Server服务,该视图的计数器就初始化为空。 而且,当分离或关闭数据库时(例如,由于 AUTO_CLOSE 设置为 ON),便会删除与该数据库关联的所有记录。),所以,如果数据库只运行了几天,那么这个视图的数据有可能不是特别准确(例如,有些OLAP的批处理或作业,一个月才运行一次)。所以在判断分析前,一定要查看数据库服务已经运行多长时间了。一般合适的时间是一个月以上,最好是两个月以上。

 

 

SQLSERVER有几个动态管理视图

2:sys.dm_db_index_usage_stats不返回有关内存列存储索引的信息

 

 

sys.dm_db_missing_index_details

3:注意字段IndexCreated,如果索引是最近几天创建的,也要谨慎分析,不要急于删除。

 

 

sys.dm_db_missing_index_groups

4:注意条件里面有些字段过滤条件,其实都是包含一定业务意义的。

 

 

sys.dm_db_missing_index_group_stats

 

 

另外,上面脚本只能查询当前数据库的未使用索引,如果需要查询当前实例下的所有数据库,那么可以使用下面脚本

sys.dm_db_missing_index_columns(index_handle)

 

 

 

 

EXEC sp_MSforeachdb 'USE [?] ; 

SELECT  DB_NAME(diu.database_id)                  AS DatabaseName ,

        s.name +''.'' +QUOTENAME(o.name)          AS TableName    ,

        i.index_id                                AS IndexID        ,

        i.name                                    AS IndexName    ,

        CASE WHEN i.is_unique =1 THEN ''UNIQUE INDEX''

           ELSE ''NOT UNIQUE INDEX''    END       AS IS_UNIQUE,

        CASE WHEN i.is_disabled=1 THEN ''DISABLE''

           ELSE ''ENABLE''            END         AS IndexStatus,

        o.create_date                             AS IndexCreated,

        STATS_DATE(o.object_id,i.index_id)        AS StatisticsUpdateDate,

        diu.user_seeks                            AS UserSeek ,

        diu.user_scans                            AS UserScans ,

        diu.user_lookups                          AS UserLookups ,

        diu.user_updates                          AS UserUpdates ,

        p.TableRows ,

        ''DROP INDEX '' + QUOTENAME(i.name) 

        + '' ON '' + QUOTENAME(s.name) + ''.''

        + QUOTENAME(OBJECT_NAME(diu.object_id)) +'';'' AS ''Drop Index Statement''

FROM    sys.dm_db_index_usage_stats diu

        INNER JOIN sys.indexes i ON i.index_id = diu.index_id

                                    AND diu.object_id = i.object_id

        INNER JOIN sys.objects o ON diu.object_id = o.object_id

        INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

        INNER JOIN ( SELECT SUM(p.rows) TableRows ,

                            p.index_id ,

                            p.object_id

                     FROM   sys.partitions p

                     GROUP BY p.index_id ,

                            p.object_id

                   ) p ON p.index_id = diu.index_id

                          AND diu.object_id = p.object_id

WHERE   OBJECTPROPERTY(diu.object_id, ''IsUserTable'') = 1

        AND diu.database_id = DB_ID()

        AND i.is_primary_key = 0        --排除主键索引

        AND i.is_unique_constraint = 0         --排除唯一索引

        AND diu.user_updates <> 0              --排除没有数据变化的索引

        AND diu.user_lookups = 0

        AND diu.user_seeks = 0

        AND diu.user_scans = 0

        AND i.name is not null

ORDER BY ( diu.user_seeks + diu.user_scans + diu.user_lookups ) ASC,diu.user_updates DESC;

'

 

 

 

 


 

 

另外,出于谨慎考虑,在删除索引前,必须先保留那些即将删除的索引的脚本,以防误删索引时(当然这种情况极少见),能够回滚,及时补救。所以可以使用下面脚本生成那些unused idnex的创建脚本。

 

 

 

SELECT 'SQL Server Instance Start with ' + CONVERT(VARCHAR(16),create_date,120)  FROM sys.databases

WHERE database_id =2;

 

IF  EXISTS(SELECT * FROM  tempdb.dbo.sysobjects WHERE  id=OBJECT_ID('tempdb.dbo.#index_stat'))

BEGIN

    DROP TABLE  #index_stat;

END

GO

SELECT  DB_NAME(diu.database_id)                AS DatabaseName ,

        o.object_id                                AS object_id    ,

        s.name +'.' +QUOTENAME(o.name)            AS TableName    ,

        i.index_id                                AS IndexID   ,

        i.name                                    AS IndexName        ,

        CASE WHEN i.is_unique =1 THEN 'UNIQUE INDEX'

           ELSE 'NOT UNIQUE INDEX'    END             AS IS_UNIQUE,

        CASE WHEN i.is_disabled=1 THEN 'DISABLE'

           ELSE 'ENABLE'            END             AS IndexStatus,

        o.create_date                             AS IndexCreated,

        STATS_DATE(o.object_id,i.index_id)         AS StatisticsUpdateDate,

        diu.user_seeks                             AS UserSeek ,

        diu.user_scans                             AS UserScans ,

        diu.user_lookups                         AS UserLookups ,

        diu.user_updates                         AS UserUpdates ,

        p.TableRows ,

        'DROP INDEX ' + QUOTENAME(i.name) 

        + ' ON ' + QUOTENAME(s.name) + '.'

        + QUOTENAME(OBJECT_NAME(diu.object_id)) +';' AS 'Drop Index Statement' INTO #index_stat

FROM    sys.dm_db_index_usage_stats diu

        INNER JOIN sys.indexes i ON i.index_id = diu.index_id

                                    AND diu.object_id = i.object_id

        INNER JOIN sys.objects o ON diu.object_id = o.object_id

        INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

        INNER JOIN ( SELECT SUM(p.rows) TableRows ,

                            p.index_id ,

                            p.object_id

                     FROM   sys.partitions p

                     GROUP BY p.index_id ,

                            p.object_id

                   ) p ON p.index_id = diu.index_id

                          AND diu.object_id = p.object_id

WHERE   OBJECTPROPERTY(diu.object_id, 'IsUserTable') = 1

        AND diu.database_id = DB_ID()

        AND i.is_primary_key = 0        --排除主键索引

        AND i.is_unique_constraint = 0  --排除唯一索引

        AND diu.user_updates <> 0        --排除没有数据变化的索引

        AND diu.user_lookups = 0

        AND diu.user_seeks = 0

        AND diu.user_scans = 0

        AND i.name IS NOT NULL

ORDER BY ( diu.user_seeks + diu.user_scans + diu.user_lookups ) ASC,diu.user_updates DESC;

GO

 

 

SELECT * FROM #index_stat WHERE IndexName IS NOT NULL ORDER BY TableName, IndexID;

 

SELECT ' CREATE ' +  

    CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END  +   

    I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' +    

    I.name  + ' ON '  +   

    Schema_name(T.Schema_id)+'.'+T.name + ' ( ' +  

    KeyColumns + ' )  ' +  

    ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') +  

    ISNULL(' WHERE  '+I.Filter_definition,'') + ' WITH ( ' +  

    CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ','  +  

    'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ','  +  

    -- default value  

    'SORT_IN_TEMPDB = OFF '  + ','  +  

    CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ','  +  

    CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ','  +  

    -- default value   

    ' DROP_EXISTING = ON '  + ','  +  

    -- default value   

    ' ONLINE = OFF '  + ','  +  

   CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ','  +  

   CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END  + ' ) ON [' +  

   DS.name + ' ] '  [CreateIndexScript]  

FROM sys.indexes I    

 JOIN sys.tables T ON T.Object_id = I.Object_id     

 JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid    

 JOIN (SELECT * FROM (   

    SELECT IC2.object_id , IC2.index_id ,   

        STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END 

    FROM sys.index_columns IC1   

    JOIN Sys.columns C    

       ON C.object_id = IC1.object_id    

       AND C.column_id = IC1.column_id    

       AND IC1.is_included_column = 0   

    WHERE IC1.object_id = IC2.object_id    

       AND IC1.index_id = IC2.index_id    

    GROUP BY IC1.object_id,C.name,index_id   

    ORDER BY MAX(IC1.key_ordinal)   

       FOR XML PATH('')), 1, 2, '') KeyColumns    

    FROM sys.index_columns IC2      

    GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4    

  ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id   

 JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id    

 JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id    

 JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id    

 LEFT JOIN (SELECT * FROM (    

    SELECT IC2.object_id , IC2.index_id ,    

        STUFF((SELECT ' , ' + C.name  

    FROM sys.index_columns IC1    

    JOIN Sys.columns C     

       ON C.object_id = IC1.object_id     

       AND C.column_id = IC1.column_id     

       AND IC1.is_included_column = 1    

    WHERE IC1.object_id = IC2.object_id     

       AND IC1.index_id = IC2.index_id     

    GROUP BY IC1.object_id,C.name,index_id    

       FOR XML PATH('')), 1, 2, '') IncludedColumns     

   FROM sys.index_columns IC2     

   GROUP BY IC2.object_id ,IC2.index_id) tmp1    

   WHERE IncludedColumns IS NOT NULL ) tmp2     

ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id    

WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0  

    AND EXISTS( SELECT 1 FROM #index_stat dx WHERE  dx.IndexID = i.index_id AND dx.object_id = i.object_id)

sys.dm_db_missing_index_details

 

 

 

这个DMV记录了当前数据库下所有的missing index的信息,他针对的是SQLSERVER从启动以来所有运行的语句,

最后在删除索引过后,需要监控一段时间,通过监控工具对比、监控索引删除后的性能情况。有时候可能也没有显著的性能提高,主要监控是否出现由于误删索引,导致数据库性能出现异常的情况。

 

 

而不是针对某一个查询。DBA可以看看,哪些表格SQLSERVER对他是最有“意见”的

 

 

 

以下是这个DMV的各个字段的解释:

参考资料:

 

 

1、index_handle:标识特定的缺失索引。该标识符在服务器中是唯一的。index_handle 是此表的密钥

 

 

 

2、database_id :标识带有缺失索引的表所驻留的数据库

 

 

3、object_id :标识索引缺失的表

 

4、equality_columns:构成相等谓词的列的逗号分隔列表 即哪个字段缺失了索引会在这里列出来(简单来讲就是where 后面的筛选字段),

 

谓词的形式如下:table.column =constant_value

 

5、inequality_columns :构成不等谓词的列的逗号分隔列表,例如以下形式的谓词:table.column > constant_value “=”之外的任何比较运算符都表示不相等。

 

6、included_columns:用于查询的涵盖列的逗号分隔列表(简单来讲就是 select 后面的字段)。

 

7、statement:索引缺失的表的名称

 

比如下面这个查询结果

 

www.9159.com 1

 

那么应该创建这样的索引

 

1 CREATE INDEX idx_SalesOrderDetail_test_ProductID_IncludeIndex ON SalesOrderDetail_test(ProductID) INCLUDE(SalesOrderID) 

 

在ProductID上创建索引,SalesOrderID作为包含性列的索引

 

 

 

注意事项:

 

由 sys.dm_db_missing_index_details 返回的信息会在查询优化器优化查询时更新,因而不是持久化的。

 

缺失索引信息只保留到重新启动 SQL Server 前。如果数据库管理员要在服务器回收后保留缺失索引信息,

 

则应定期制作缺失索引信息的备份副本

 

 

 


 

 

 

sys.dm_db_missing_index_columns(index_handle)

 

返回与缺少索引(不包括空间索引)的数据库表列有关的信息,sys.dm_db_missing_index_columns 是一个动态管理函数

 

字段解释

 

index_handle:唯一地标识缺失索引的整数。

 

 

 


 

 

 

sys.dm_db_missing_index_groups

 

 

 

返回有关特定缺失索引组中包含的缺失索引(不包括空间索引)的信息

 

 

 


 

sys.dm_db_missing_index_group_stats

 

返回缺失索引组的摘要信息,不包括空间索引

 

这个视图说白了就是预估有这麽一个索引,他的性能能提高多少

 

有一个字段比较重要:

 

avg_user_impact: 实现此缺失索引组后,用户查询可能获得的平均百分比收益。该值表示如果实现此缺失索引组,则查询成本将按此百分比平均下降。

 

就是说,增加了这个缺失索引,性能可以提高的百分比

 

 

 

下面是MSDN给出的示例,缺失索引组句柄为 2

 

 1 --查询提供缺失索引的数据库、架构和表的名称。它还提供应该用于索引键的列的名称
 2 USE [AdventureWorks]
 3 GO
 4 SELECT migs.group_handle, mid.*
 5 FROM sys.dm_db_missing_index_group_stats AS migs  6 INNER JOIN sys.dm_db_missing_index_groups AS mig  7     ON (migs.group_handle = mig.index_group_handle)  8 INNER JOIN sys.dm_db_missing_index_details AS mid  9     ON (mig.index_handle = mid.index_handle) 10 WHERE migs.group_handle = 2

 

www.9159.com 2

 

 

 

示例代码:

 

1 USE [AdventureWorks] --要查询索引缺失的数据库
2 GO
3 SELECT * FROM sys.[dm_db_missing_index_details]
4 SELECT * FROM sys.[dm_db_missing_index_groups]
5 SELECT * FROM sys.[dm_db_missing_index_group_stats]
6 SELECT * FROM sys.[dm_db_missing_index_columns](1) --1 :1是根据dm_db_missing_index_details查出来的

 

www.9159.com 3

 

 

 

我估计XX大侠做的SQLSERVER索引优化器也使用了"**sys.dm_db_missing_index_details" 这个DMV**

 

www.9159.com 4

 

刚才看了一下,好像有错别字:Total Cost不是Totol Cost

 

暂时不知道Total Cost跟Improvement Measure怎麽算出来的

 

 

 

注意:

 

最后大家还需要注意一下,虽然这些DMV给出的建议还是比较合理的。

 

但是,DBA还是需要去确认一下建议。因为这个建议完全是根据语句本身给出的,

 

没有考虑对其他语句的影响,也没有考虑维护索引的成本,所以是很片面的。

 

其准确性,也要再确认一下

 

 

 

 

 

**上面几个DMV的字段解释,大家可以看一下MSDN,非常详细**

 

sys.dm_db_missing_index_group_stats

msdn:

 

sys.dm_db_missing_index_groups

msdn:

 

sys.dm_db_missing_index_columns([sql_handle])

msdn:http://msdn.microsoft.com/zh-cn/library/ms345364.aspx

 

sys.dm_db_missing_index_details

msdn:

本文由9159.com发布于www.9159.com,转载请注明出处:Server的索引维护其实主要围绕下面三个问题进行

关键词:

上一篇:转为小写Ctrl+U,边用边积累
下一篇:没有了