SQLServer

索引碎片维护

在SQL Server中，存储数据最小的单位是页，每一页能存放8060个字节。当表上存在聚集索引时，页的组织方式是通过B树方式。在聚集索引B树上，只有叶子节点存放实际数据，每个叶子节点为一页，而SQLServer每个页内存储数据的最小的单位是row，当叶子节点新插入或更新导致叶子节点无法容纳时，就会进行页分裂，在分裂过程中就产生了碎片。

外部碎片

外部碎片就是由于页分裂而产生的碎片，新分裂的页很多情况下与之前的页在磁盘上并不连续，而聚集索引要求行逻辑上连续，因此跨页查询会增加IO消耗，导致性能下降

内部碎片

内部碎片就是页内的碎片，假如页原本能容纳4条记录，但对页内一条数据进行更新后，原本的页已经无法容纳了，只能进行页分裂把数据放在新页上，这样两个页内部都存在空间浪费，数据不够紧凑。

查看大于30%碎片率的索引

1
2
3
4
5
6
7
8
9

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind  
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 30 
ORDER BY indexstats.avg_fragmentation_in_percent DESC

碎片维护 根据上面的描述，不管是内部碎片还是外部碎片都会造成更多的IO操作，导致性能下降。因此，我们应该针对这种情况对索引进行维护，通常有两种方式：REORGANIZE和REBUILD，REORGANIZE不会重建索引，只是整理，当遇到加锁的页时跳过，效果较差，仅适用于碎片率较低的索引；REBUILD会重建索引，重建会造成阻塞，可以添加ONLINE选项降低锁，但重建时间会变长

这里我们可以通过微软官方团队提供的索引维护脚本来进行批量定时处理，项目地址：AdaptiveIndexDefrag](https://github.com/Microsoft/tigertoolbox/tree/master/AdaptiveIndexDefrag)

下载完成后，只需要执行usp_AdaptiveIndexDefrag.sql脚本创建相关对象，其中包含如下对象：

  • tbl_AdaptiveIndexDefrag_Working：用于跟踪要对哪些对象执行操作，以及影响这些对象的处理方式的关键信息
  • tbl_AdaptiveIndexDefrag_Stats_Working：上表的统计对应项
  • tbl_AdaptiveIndexDefrag_log：索引操作日志记录表
  • tbl_AdaptiveIndexDefrag_Stats_log：统计操作日志记录表，其中记录所有统计信息操作
  • tbl_AdaptiveIndexDefrag_Exceptions：一个例外表，您可以在其中设置处理某些对象的天数的限制
  • tbl_AdaptiveIndexDefrag_IxDisableStatus：其中记录已禁用的索引，以便碎片整理周期中的中断可以解释这些索引已被碎片整理周期本身而不是用户禁用
  • usp_AdaptiveIndexDefrag_PurgeLogs：清理超过90天的日志表数据，以避免无限增长，可以设置参数@daystokeep自定义天数
  • usp_AdaptiveIndexDefrag_Exclusions：这将有助于设置允许在哪些天（如果有）上对给定表上的特定索引，甚至所有索引进行分段
  • usp_AdaptiveIndexDefrag_CurrentExecStats：可用于跟踪当前执行中到目前为止已停用的索引
  • usp_AdaptiveIndexDefrag：处理索引碎片和统计信息更新主要的存储过程
  • vw_ErrLst30Days：检查过去 30 天内的所有已知执行错误
  • vw_ErrLst24Hrs：检查过去 24 小时内的所有已知执行错误
  • vw_AvgTimeLst30Days：检查过去 30 天内每个索引的平均执行时间
  • vw_AvgFragLst30Days：检查过去 30 天内每个索引的平均碎片数
  • vw_AvgLargestLst30Days：检查过去 30 天内每个索引的平均大小
  • vw_AvgMostUsedLst30Days：检查过去 30 天内每个索引的平均使用情况
  • vw_LastRun_Log：上次执行的方式

dbo.usp_AdaptiveIndexDefrag默认是对碎片率大于5%的索引进行REORGANIZE，超过30%的索引进行REBUILD，执行时长默认为8小时，更新相关统计信息，以下是几种常用的执行方式：

针对TEST数据库进行维护

1

EXEC dbo.usp_AdaptiveIndexDefrag @dbScope ="TEST"

针对TEST数据库的TAB1表进行维护

1

EXEC dbo.usp_AdaptiveIndexDefrag @dbScope ="TEST",@tblName ="TAB1"

不执行维护操作，仅输出相关命令

1

EXEC dbo.usp_AdaptiveIndexDefrag @Exec_Print = 0，@printCmds = 1

添加详细扫描模式，以便在统计信息更新中允许更精细的阈值，并强制更新统计信息在索引相关统计信息上运行，而不是所有表统计信息

1

EXEC dbo.usp_AdaptiveIndexDefrag @Exec_Print = 0，@printCmds = 1，@scanMode = "DETAILED"，@updateStatsWhere = 1

REBUILD的标准降低到20%

1

EXEC dbo.usp_AdaptiveIndexDefrag @minFragmentation = 3，@rebuildThreshold = 20

在线重建索引

1

EXEC dbo.usp_AdaptiveIndexDefrag @onlineRebuild = 1

限制执行时长(分钟)

1

EXEC dbo.usp_AdaptiveIndexDefrag @timeLimit = 360
INDEX
Licensed under CC BY-NC-SA 4.0
comments powered by Disqus