sql server性能分析--索引密度和碎片信息

最新推荐文章于 2024-07-29 08:00:00 发布

土豆131421

最新推荐文章于 2024-07-29 08:00:00 发布

阅读量1.1k

点赞数

分类专栏： SQL Server

本文链接：https://blog.csdn.net/tuzhen007/article/details/12916245

版权

SQL Server 专栏收录该内容

100 篇文章 0 订阅

订阅专栏

查询数据库中所有表的索引密度和碎片信息，以便为索引的重建和整理提供依据，也可以参考DBCC SHOWCONTIG，通常FRAGMENTATIOIN在30%以上建议重

建，否则建议整理

SELECT i.name                           AS indexname,
         o.name                           AS tablename,
         s.name                           AS schemaname,
         f.index_type_desc                AS indextype,
         f.avg_page_space_used_in_percent AS indexdensity,
         f.avg_fragmentation_in_percent   AS indexfragmentation,
         f.page_count                     AS pages
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') f
         INNER JOIN sys.objects o ON o.object_id = f.object_id
         INNER JOIN sys.schemas s ON o.schema_id = s.schema_id 
         INNER JOIN sys.indexes i ON f.object_id = i.object_id AND f.index_id = i.index_id
WHERE page_count > 50
     AND f.index_id > 0
ORDER BY o.name, i.index_id