当前位置:首页 > 数据库 > Sql Server

目录碎片检测

优良自学吧提供目录碎片检测,索引碎片检测在索引碎片里我们解释了不同类型的碎片,还有它们如何影响查询性能。在这个文章里,我们会讨论下如何检测索引碎片。 内部碎片检测 内部碎片是关于页面饱和度的一切,可以用DETAILED模式的 sys.dm_db_index_physical_stats

索引碎片检测

索引碎片里我们解释了不同类型的碎片,还有它们如何影响查询性能。在这个文章里,我们会讨论下如何检测索引碎片。

内部碎片检测

内部碎片是关于页面饱和度的一切,可以用DETAILED模式的 sys.dm_db_index_physical_stats,avg_page_space_used_in_percent 列会给出索引的内部碎片,下面的查询会列出超过10个页面,且页面饱和度低于85%的索引。

 1 EXEC sp_configure 'show advanced options', 1
 2 GO
 3 RECONFIGURE WITH OVERRIDE
 4 GO
 5 DECLARE @DefaultFillFactor INT 
 6 DECLARE @Fillfactor TABLE
 7     (
 8       Name VARCHAR(100) ,
 9       Minimum INT ,
10       Maximum INT ,
11       config_value INT ,
12       run_value INT
13     )
14 INSERT  INTO @Fillfactor
15         EXEC sp_configure 'fill factor (%)'     
16 SELECT  @DefaultFillFactor = CASE WHEN run_value = 0 THEN 100
17                                   ELSE run_value
18                              END
19 FROM    @Fillfactor 
20 
21 SELECT  DB_NAME() AS DBname ,
22         QUOTENAME(s.name) AS CchemaName ,
23         QUOTENAME(o.name) AS TableName ,
24         i.name AS IndexName ,
25         stats.Index_type_desc AS IndexType ,
26         stats.page_count AS [PageCount] ,
27         stats.partition_number AS PartitionNumber ,
28         CASE WHEN i.fill_factor > 0 THEN i.fill_factor
29              ELSE @DefaultFillFactor
30         END AS [Fill Factor] ,
31         stats.avg_page_space_used_in_percent ,
32         CASE WHEN stats.index_level = 0 THEN 'Leaf Level'
33              ELSE 'Nonleaf Level'
34         END AS IndexLevel
35 FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
36         AS stats ,
37         sys.objects AS o ,
38         sys.schemas AS s ,
39         sys.indexes AS i
40 WHERE   o.OBJECT_ID = stats.OBJECT_ID
41         AND s.schema_id = o.schema_id
42         AND i.OBJECT_ID = stats.OBJECT_ID
43         AND i.index_id = stats.index_id
44         AND stats.avg_page_space_used_in_percent <= 85
45         AND stats.page_count >= 10
46         AND stats.index_id > 0
47 ORDER BY stats.avg_page_space_used_in_percent ASC ,
48         stats.page_count DESC

这里我在WHERE条件里指定了只列出超过10页,且页面饱和度低于85%的结果。这是基于我当前系统环境和一些文档的最佳实践。avg_page_space_used_in_percent 的低值,加上PageCount 的高值,会影响到系统性能。avg_page_space_used_in_percent 的低值会下列不同的原因:

  • 分页和记录删除:在这个情况下,我们需要对索引进行REBUILD和REORGANIZE操作。如果碎片报告在非页层,需要REBUILD来减少碎片。
  • 填充因子设置:索引填充因子值的错误设置可能造成内部碎片。如果内部碎片是因为填充因子设置造成的,我们需要使用填充因子的新值对索引进行REBUILD
  • 记录大小:有些时候,记录大小也会称为内部碎片的原因。例如我们假设一条记录大小是3000 bytes,一页只能保存2条记录。第3条记录不能插入页,因为页里剩下的空间小于3000 bytes。在这种情况下,每一页都会有2060 bytes的空余。为了解决因这个原因造成的碎片,我们需要重新设计表或者对表进行垂直分区。

外部碎片检测

外部检测也是用LIMITED模式的sys.dm_db_index_physical_stats,但我们使用avg_fragmentation_in_percent 的结果来检测外部碎片。使用LIMITED模式会给我们叶子层的碎片。如果要获得非页层的碎片,可以使用DETAILED或SAMPLE模式。碎片是页的连续分配。例如如果一个索引有150页,页分配从1到50,55到60,65到120,还有140到180。每个这样序列被称为碎片,这里就是有4个碎片。

 1 EXEC sp_configure 'show advanced options', 1
 2 GO
 3 RECONFIGURE WITH OVERRIDE
 4 GO
 5 DECLARE @DefaultFillFactor INT 
 6 DECLARE @Fillfactor TABLE
 7     (
 8       Name VARCHAR(100) ,
 9       Minimum INT ,
10       Maximum INT ,
11       config_value INT ,
12       run_value INT
13     )
14 INSERT  INTO @Fillfactor
15         EXEC sp_configure 'fill factor (%)'     
16 SELECT  @DefaultFillFactor = CASE WHEN run_value = 0 THEN 100
17                                   ELSE run_value
18                              END
19 FROM    @Fillfactor 
20 
21 SELECT  DB_NAME() AS DBname ,
22         QUOTENAME(s.name) AS CchemaName ,
23         QUOTENAME(o.name) AS TableName ,
24         i.name AS IndexName ,
25         stats.Index_type_desc AS IndexType ,
26         stats.page_count AS [PageCount] ,
27         stats.partition_number AS PartitionNumber ,
28         CASE WHEN i.fill_factor > 0 THEN i.fill_factor
29              ELSE @DefaultFillFactor
30         END AS [Fill Factor] ,
31         stats.avg_fragmentation_in_percent ,
32         stats.fragment_count ,
33         CASE WHEN stats.index_level = 0 THEN 'Leaf Level'
34              ELSE 'Nonleaf Level'
35         END AS IndexLevel
36 FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
37         AS stats ,
38         sys.objects AS o ,
39         sys.schemas AS s ,
40         sys.indexes AS i
41 WHERE   o.OBJECT_ID = stats.OBJECT_ID
42         AND s.schema_id = o.schema_id
43         AND i.OBJECT_ID = stats.OBJECT_ID
44         AND i.index_id = stats.index_id
45         AND stats.avg_fragmentation_in_percent >= 20
46         AND stats.page_count >= 1000
47 ORDER BY stats.avg_fragmentation_in_percent DESC ,
48         stats.page_count DESC

在这个查询里,我使用的WHERE条件只列出碎片大于20%且最少1000页的索引。avg_fragmentation_in_percent 值高的话,可能有下列原因:

  • SQL Server存储引擎对表或索引从混合区开始分配页,直到页数达到8页。一旦页数达到8页,SQL Server引擎开始把整个统一区分配给索引。因此这里对于小表会有很高的碎片,重建索引会增加碎片。例如,我们假设一个索引有7页,这些页是从2个混合区分配的,当我们重建索引的时候,很可能把页分配从2个混合区变成最大7个混合区,这就导致了碎片增加。
  • 即使也从混合区分配,还是有碎片产生的可能。当索引大小增长时,在非页层也需要更多的页。如果分配给叶子层的最后页是250,在叶子层索引结构里增加更多的记录,可能会在第1层索引需要更多的页,然后SQL Server存储引擎分配251页在第1层索引,这就在叶子层产生了碎片。
  • 造成分页的其他常见原因就是DML操作。Rebuild/Reorganize 索引对于上述不能很好的减少碎片,但可以减少由分页或删除操作造成的碎片。
  • 我们按下列要求进行索引的维护:
    • 20-40%的碎片,用Reorganize来重新组织索引。
    • 大于40%的碎片,需要用Rebuild来重建索引。
    • 低于1000页的索引,在索引维护逻辑上是被忽略的(不处理)
    • 大于50k的页,碎片在10-20%之间,也要用Reorganize来重新组织索引。

 


(本文来自互联网,不代表搜站(http://www.ylzx8.cn/)的观点和立场)
本站所有内容来自互联网,若本站收录的信息无意侵犯了贵司版权,请给我们来信(ylzx8cn@163.com),我们会及时处理和回复,谢谢