返回
软件
分类

统计数据是在SQL Server运行的时候开始收集的,数据库中的未使用索引(unused

日期: 2020-04-07 06:12 浏览次数 : 62

 

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

(欣赏别人的,供以后深入了解)

 

  当我们明白了什么是索引,什么时间创建索引以后,我们就会想,我们创建的索引到底效率执行的怎么样?好不好?我们创建的对不对?

 

  首先我们来认识一下DMV,DMV (dynamic management view)动态管理视图和函数返回特定于实现的内部状态数据。推出SQL Server 2005时,微软介绍了许多被称为dmvs的系统视图,让您可以探测SQL Server 的健康状况,诊断问题,或查看SQL Server实例的运行信息。统计数据是在SQL Server运行的时候开始收集的,并且在SQL Server每次启动的时候,统计数据将会被重置。当你删除或者重新创建其组件时,某些dmv的统计数据也可以被重置,例如存储过程和表,而其它的dmv信息在运行dbcc命令时也可以被重置。

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

  当你使用一个dmv时,你需要紧记SQL Server收集这些信息有多长时间了,以确定这些从dmv返回的数据到底有多少可用性。如果SQL Server只运行了很短的一段时间,你可能不想去使用一些dmv统计数据,因为他们并不是一个能够代表SQL Server实例可能遇到的真实工作负载的样本。另一方面,SQL Server只能维持一定量的信息,有些信息在进行SQL Server性能管理活动的时候可能丢失,所以如果SQL Server已经运行了相当长的一段时间,一些统计数据就有可能已被覆盖。

 

  因此,任何时候你使用dmv,当你查看从SQL Server 2005的dmvs返回的相关资料时,请务必将以上的观点装在脑海中。只有当你确信从dmvs获得的信息是准确和完整的,你才能变更数据库或者应用程序代码。

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

下面就看一下dmv到底能带给我们那些好的功能呢?

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

1.51 :索引使用次数

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

我们下看一下下面两种查询方式返回的结果(这两种查询的查询用途一致)

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

①----

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

declare @dbid int

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

select @dbid = db_id()

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

select objectname=object_name(s.object_id), s.object_id, indexname=i.name, i.index_id

                rows in one statement, this count increments by 1

            , user_seeks, user_scans, user_lookups, user_updates

 

from sys.dm_db_index_usage_stats s,

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

            sys.indexes i

 

where database_id = @dbid and objectproperty(s.object_id,'IsUserTable') = 1

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

and i.object_id = s.object_id

 

and i.index_id = s.index_id

 

order by (user_seeks + user_scans + user_lookups + user_updates) asc

需要注意的几点:

返回查询结果

 

 必赢备用网址 1

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

 

 

②:使用多的索引排在前面

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

SELECT  objects.name ,

 

        databases.name ,

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

        indexes.name ,

 

        user_seeks ,

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

        user_scans ,

 

        user_lookups ,

 

        partition_stats.row_count

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

FROM    sys.dm_必赢备用网址 ,db_index_usage_stats stats

 

        LEFT JOIN sys.objects objects ON stats.object_id = objects.object_id

 

        LEFT JOIN sys.databases databases ON databases.database_id = stats.database_id

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;

'

 

 

        LEFT JOIN sys.indexes indexes ON indexes.index_id = stats.index_id

 

                                         AND stats.object_id = indexes.object_id

 

        LEFT  JOIN sys.dm_db_partition_stats partition_stats ON stats.object_id = partition_stats.object_id

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

                                                              AND indexes.index_id = partition_stats.index_id

 

WHERE   1 = 1

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 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 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)

--AND databases.database_id = 7

 

        AND objects.name IS NOT NULL

 

        AND indexes.name IS NOT NULL

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

        AND user_scans>0

 

ORDER BY user_scans DESC ,

 

        stats.object_id ,

 

        indexes.index_id

参考资料:

返回查询结果

 

 必赢备用网址 2

 

 

 

user_seeks : 通过用户查询执行的搜索次数。 
 个人理解: 此统计索引搜索的次数

 

user_scans: 通过用户查询执行的扫描次数。 
  个人理解:此统计表扫描的次数,无索引配合
user_lookups: 通过用户查询执行的查找次数。 
 个人理解:用户通过索引查找,在使用RID或聚集索引查找数据的次数,对于堆表或聚集表数据而言和索引配合使用次数
user_updates:  通过用户查询执行的更新次数。 
  个人理解:索引或表的更新次数

我们可以清晰的看到,那些索引用的多,那些索引没用过,大家可以根据查询出来的东西去分析自己的数据索引和表

1.52 :索引提高了多少性能

新建了索引到底增加了多少数据的效率呢?到底提高了多少性能呢?运行如下SQL可以返回连接缺失索引动态管理视图,发现最有用的索引和创建索引的方法: 

SELECT  

avg_user_impact AS average_improvement_percentage,  

avg_total_user_cost AS average_cost_of_query_without_missing_index,  

'CREATE INDEX ix_' + [statement] +  

ISNULL(equality_columns, '_') + 

ISNULL(inequality_columns, '_') + ' ON ' + [statement] +  

' (' + ISNULL(equality_columns, ' ') +  

ISNULL(inequality_columns, ' ') + ')' +  

ISNULL(' INCLUDE (' + included_columns + ')', '')  

AS create_missing_index_command 

FROM sys.dm_db_missing_index_details a INNER JOIN  

sys.dm_db_missing_index_groups b ON a.index_handle = b.index_handle 

INNER JOIN sys.dm_db_missing_index_group_stats c ON  

b.index_group_handle = c.group_handle 

WHERE avg_user_impact > = 40

 

返回结果

 必赢备用网址 3

 

虽然用户能够修改性能提高的百分比,但以上查询返回所有能够将性能提高40%或更高的索引。你可以清晰的看到每个索引提高的性能和效率了

1.53 :最占用CPU、执行时间最长命令

这个和索引无关,但是还是在这里提出来,因为他也属于DMV带给我们的功能吗,他可以让你轻松查询出,那些sql语句占用你的cpu最高

 

SELECT TOP 100 execution_count,

           total_logical_reads /execution_count AS [Avg Logical Reads],

           total_elapsed_time /execution_count AS [Avg Elapsed Time],

                db_name(st.dbid) as [database name],

           object_name(st.dbid) as [object name],

           object_name(st.objectid) as [object name 1],

           SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1, 

           ((CASE statement_end_offset WHEN - 1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) 

             / 2) + 1) AS statement_text

  FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

 WHERE execution_count > 100

 ORDER BY 1 DESC;

 

返回结果:

 必赢备用网址 4

 

执行时间最长的命令

SELECT TOP 10 COALESCE(DB_NAME(st.dbid),

DB_NAME(CAST(pa.value as int))+'*',

'Resource') AS DBNAME,

SUBSTRING(text,

-- starting value for substring

        CASE WHEN statement_start_offset = 0

OR statement_start_offset IS NULL

THEN 1

ELSE statement_start_offset/2 + 1 END,

-- ending value for substring

        CASE WHEN statement_end_offset = 0

OR statement_end_offset = -1

OR statement_end_offset IS NULL

THEN LEN(text)

ELSE statement_end_offset/2 END -

CASE WHEN statement_start_offset = 0

OR statement_start_offset IS NULL

THEN 1

ELSE statement_start_offset/2  END + 1

)  AS TSQL,

total_logical_reads/execution_count AS AVG_LOGICAL_READS

FROM sys.dm_exec_query_stats

CROSS APPLY sys.dm_exec_sql_text(sql_handle) st

OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa

WHERE attribute = 'dbid'

ORDER BY AVG_LOGICAL_READS DESC ;

 必赢备用网址 5

 

看到了吗?直接可以定位到你的sql语句,优化去吧。还等什么呢?

1.54:缺失索引

缺失索引就是帮你查找你的数据库缺少什么索引,告诉你那些字段需要加上索引,这样你就可以根据提示添加你数据库缺少的索引了

SELECT TOP 10

[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)

, avg_user_impact

, TableName = statement

, [EqualityUsage] = equality_columns

, [InequalityUsage] = inequality_columns

, [Include Cloumns] = 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;

查询结果如下:

 必赢备用网址 6