CNDBA社区
微软在Github上提供了一个脚本，用于自动管理索引和统计信息。地址
其实就是创建一个存储过程对一个或多个数据库的一个或多个索引和统计信息执行智能碎片整理。简而言之，这个过程自动选择是根据索引的碎片级别，还是根据其他参数(如是否允许页锁或lob的存在)重新构建或重新组织索引，同时使用线性阈值更新统计信息。所有这些都在您选择的指定时间范围内，默认为8小时。还可以根据大小、碎片级别或索引使用(基于范围扫描计数)设置碎片整理优先级，这是默认设置。它还处理分区索引、columnstore索引、内存表中的索引、统计信息更新(表范围内的或仅与索引相关的)、使用原始填充因子或索引填充进行重新构建以及在线操作，等等。
环境要求
支持SQL Server 2005 SP2及之后的版本。
注：不保证可以在SQL Server 2005版本中正常运行。
安装
直接在SSMS中运行脚本即可。如：
由于脚本太长，需要看脚本内容的朋友自己去网上查看：https://github.com/Microsoft/tigertoolbox/blob/master/AdaptiveIndexDefrag/usp_AdaptiveIndexDefrag.sql
输出信息:
Droping existing objects
Preserving historic data
tbl_AdaptiveIndexDefrag_log table created
tbl_AdaptiveIndexDefrag_Analysis_log table created
tbl_AdaptiveIndexDefrag_Exceptions table created
tbl_AdaptiveIndexDefrag_Working table created
tbl_AdaptiveIndexDefrag_Stats_Working table created
tbl_AdaptiveIndexDefrag_Stats_log table created
tbl_AdaptiveIndexDefrag_IxDisableStatus table created
Copying old data...
Done copying old data...
Removed old tables...
Procedure usp_AdaptiveIndexDefrag created
Reporting views created
Procedure usp_AdaptiveIndexDefrag_PurgeLogs created (Default purge is 90 days old)
Procedure usp_AdaptiveIndexDefrag_CurrentExecStats created (Use this to monitor defrag loop progress)
Procedure usp_AdaptiveIndexDefrag_Exceptions created (If the defrag should not be daily, use this to set on which days to disallow it. It can be on entire DBs, tables and/or indexes)
All done!
上面表示已经安装成功了。
使用
存储过程支持参数还是很多的，我这里只介绍其中几个常用的。其他参数，请查看官方的文档：
https://github.com/Microsoft/tigertoolbox/blob/master/AdaptiveIndexDefrag/OPTIONS.md
- @Exec_Print：默认是1(执行存储过程生成的SQL命令)，0表示不执行(仅显示)。
- @printCmds ：默认是0(不打印SQL命令)，1(打印SQL命令)。如果只想查看哪些命令要执行，这个选项很有用。
- @outputResults：默认0(不输出碎片信息)，1表示命令执行完后会显示碎片信息。
- @timeLimit ：用于限制用于整理索引碎片的时间。默认：8小时。当然如果一个很大的索引，还是可能会超过8小时的。
- @dbScope：指定要整理碎片的数据库。如果不指定，则整理所有非系统数据库，包括msdb，model。
- @tblName：指定要整理碎片表的索引。如果不指定，则整理数据库中所有表。格式：schema.table_name。
- @minFragmentation：默认5%，低于5%将不会被整理碎片。
- @rebuildThreshold：默认30%，高于30%将会被重建。而不是重新组织。
- @scanMode指定使用哪种扫描模式来确定碎片级别。LIMITED模式是默认模式。扫描最小数量的页面。对于索引，只扫描b树的父级页面（即叶级以上的页面）。对于堆，只检查相关的PFS和IAM页面。没有扫描堆的数据页。其他选项包括SAMPLED（根据索引或堆中所有页面的1％的样本返回统计信息）或DETAILED（扫描所有页面并返回所有统计信息）。可能导致性能问题）。如果索引或堆的页数少于10,000，则自动使用DETAILED模式而不是SAMPLED模式。
- @onlineRebuild：默认0(离线重建)，1表示在线重建。
下面结合几个例子来说明一下：
全部采用默认值，整理碎片：
EXEC dbo.usp_AdaptiveIndexDefrag
默认设置是对碎片大于5％的索引进行碎片整理; 重建碎片大于30％的索引; 整理所有索引; 命令将自动执行; 以DESC顺序对RANGE_SCAN_COUNT值进行碎片整理; 规定时限，为480分钟（8小时）; 所有数据库都将进行碎片整理; 所有表都将进行碎片整理; 将重新扫描索引; 扫描将以限定模式执行; LOB将被压缩; 将defrags限制为超过8页的索引; 索引将进行碎片整理OFFLINE; 索引将在DATABASE中排序; 索引将具有其原始填充因子; 如果大于8页，则仅考虑最右侧填充的分区; 统计数据将在重组指数上更新; 碎片整理将使用处理器的系统默认值; 不打印t-sql命令; 不输出碎片级别; 在索引操作之间等待5秒;注意：慎用。执行需要时间过长，而且默认情况下是离线重建索引。
指定数据库
EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'AdventureWorks2014'- 指定数据库，表
EXEC dbo.usp_AdaptiveIndexDefrag @dbScope = 'AdventureWorks2014', @tblName = 'Production.BillOfMaterials' - 只显示命令，不会运行命令去整理碎片
结果：EXEC dbo.usp_AdaptiveIndexDefrag @Exec_Print = 0, @printCmds = 1Printing SQL statements... ALTER INDEX [PK_Warehouse_StockItems] ON [WideWorldImporters].[Warehouse].[StockItems] REORGANIZE WITH (LOB_COMPACTION = ON); -- No need to update statistic [PK_Warehouse_StockItems] on table or view [StockItems] of DB [WideWorldImporters]... ALTER INDEX [PK_Purchasing_PurchaseOrders] ON [WideWorldImporters].[Purchasing].[PurchaseOrders] REORGANIZE WITH (LOB_COMPACTION = ON); -- No need to update statistic [PK_Purchasing_PurchaseOrders] on table or view [PurchaseOrders] of DB [WideWorldImporters]... ALTER INDEX [IX_Application_People_Perf_20160301_05] ON [WideWorldImporters].[Application].[People] REORGANIZE WITH (LOB_COMPACTION = ON); -- No need to update statistic [IX_Application_People_Perf_20160301_05] on table or view [People] of DB [WideWorldImporters]... ALTER INDEX [FK_Purchasing_PurchaseOrderLines_PurchaseOrderID] ON [WideWorldImporters].[Purchasing].[PurchaseOrderLines] REORGANIZE WITH (LOB_COMPACTION = ON); -- No need to update statistic [FK_Purchasing_PurchaseOrderLines_PurchaseOrderID] on table or view [PurchaseOrderLines] of DB [WideWorldImporters]... ALTER INDEX [FK_Purchasing_PurchaseOrderLines_StockItemID] ON [WideWorldImporters].[Purchasing].[PurchaseOrderLines] REORGANIZE WITH (LOB_COMPACTION = ON); -- No need to update statistic [FK_Purchasing_PurchaseOrderLines_StockItemID] on table or view [PurchaseOrderLines] of DB [WideWorldImporters]... ALTER INDEX [FK_Purchasing_PurchaseOrderLines_PackageTypeID] ON [WideWorldImporters].[Purchasing].[PurchaseOrderLines] REORGANIZE WITH (LOB_COMPACTION = ON); -- No need to update statistic [FK_Purchasing_PurchaseOrderLines_PackageTypeID] on table or view [PurchaseOrderLines] of DB [WideWorldImporters]... UPDATE STATISTICS [msdb].[dbo].[tbl_AdaptiveIndexDefrag_Stats_log] ([PK_AdaptiveIndexDefrag_Stats_log]); ALTER INDEX [PK_AdaptiveIndexDefrag_Stats_log] ON [msdb].[dbo].[tbl_AdaptiveIndexDefrag_Stats_log] REBUILD WITH (DATA_COMPRESSION = NONE, FILLFACTOR = 100, SORT_IN_TEMPDB = OFF);
版权声明：本文为博主原创文章，未经博主允许不得转载。
AdaptiveIndexDefrag
