主页 / dba / 问题 / 31635
Accepted
DamagedGoods
Asked: 2013-01-10 06:06:56 +0800 CST

如果 DMVS 中没有统计数据，可以删除 FK 的索引

  • 772

我使用了下面来自 Kevin Kline 的众所周知的查询来检查未使用的索引。在外键上创建的几个索引不返回读取统计信息，仅返回写入。

您是否 100% 安全地删除这些索引？或者它们可以被优化器用于插入或删除并且不会在 DMV 中注册任何统计信息？如果是这样，如何判断它们可以 100% 安全地移除？

我已经让服务器运行了 2 个月，所以我确信我已经涵盖了每月的工作负载周期。

    SELECT o.name
    , indexname=i.name
    , i.index_id   
    , reads=user_seeks + user_scans + user_lookups   
    , writes =  user_updates   
    , rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
    , CASE
        WHEN s.user_updates < 1 THEN 100
        ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
      END AS reads_per_write
    , 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'
    FROM sys.dm_db_index_usage_stats s  
    INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id   
    INNER JOIN sys.objects o on s.object_id = o.object_id
    INNER JOIN sys.schemas c on o.schema_id = c.schema_id
    WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
    AND s.database_id = DB_ID()   
    AND i.type_desc = 'nonclustered'
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
    AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 2000
    ORDER BY  name, reads
index sql-server-2012

1 个回答

  1. Best Answer

    只要您确定服务器在整个时间内都处于运行状态，并且没有人无意中清除了 DMV 统计信息。如果数据库分离+重新附加/恢复/离线+在线/自动关闭+在线，或者如果索引已被明确删除/重新创建（DMV不受禁用/重建/重组的影响，除了 SQL Server 2012 的情况，重建当前会清除统计信息——我怀疑这将得到修复——感谢@MartinSmith）。

    您应该预料到，如果 DML 活动出于某种原因（例如，对其他表执行更新）使用读取容量中的索引，这将注册为读取活动，而不是写入。您看到的所有写入都是索引维护。

    顺便说一句，这里有一个稍微更有效的方法来编写这个查询。我删除了两次引用的相关子查询，并删除了不必要的连接sys.objects和sys.schemas。我还修复了一些小的语法问题，例如在适当的地方用别名为所有列添加前缀，将保留字放在方括号中，以及删除AS 'column alias'语法。我发现它令人困惑，在列表的中途SELECT你从alias = expression语法更改为expression AS alias语法 - 你应该选择一个并保持一致恕我直言。我为查询中的多数更改了这些，也根据我的个人喜好更改了这些。:-)

    SELECT name = OBJECT_NAME(s.[object_id])
    , indexname = i.name
    , i.index_id   
    , reads = s.user_seeks + s.user_scans + s.user_lookups   
    , writes = s.user_updates   
    , g.[rows]
    , reads_per_write = CASE
        WHEN s.user_updates < 1 THEN 100.0
        ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
      END
    , [drop statement] = 'DROP INDEX ' + QUOTENAME(i.name) 
      + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(s.[object_id])) 
      + '.'    + QUOTENAME(OBJECT_NAME(s.[object_id]))
    FROM sys.dm_db_index_usage_stats AS s  
    INNER JOIN sys.indexes AS i 
      ON i.index_id = s.index_id 
      AND s.object_id = i.object_id   
    INNER JOIN 
    (
      SELECT [object_id], index_id, [rows] = SUM([rows]) 
        FROM sys.partitions GROUP BY [object_id], index_id
        HAVING SUM([rows]) > 2000
    ) AS g
      ON i.[object_id] = g.[object_id] 
      AND i.index_id = g.index_id
    WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
    AND s.database_id = DB_ID()   
    AND i.type_desc = 'nonclustered'
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
    ORDER BY name, reads;
    • 5

相关问题