作者: littleboy 2024-05-15 21:48:58

SQLserver重建索引

当PDM数据库使用时间较长，使用过程中数据表增删涂改量大的时候，索引往往会出现失效问题，

参考：SQLServer索引碎片查询及处理 - misA丶 - 博客园

查询索引

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 显示内容，其中avg_fragmentation_in_percent返回索引的平均碎片百分比
SELECT OBJECT_NAME(ips.object_id) AS TableName,
       ips.index_id,
       name AS IndexName,
       avg_fragmentation_in_percent,
       DB_NAME(ips.database_id) AS DatabaseName
-- 
FROM sys.dm_db_index_physical_stats(DB_ID(DB_NAME()), NULL, NULL, NULL, NULL) AS ips
    INNER JOIN sys.indexes AS si
        ON si.object_id = ips.object_id
           AND si.index_id = ips.index_id
-- 筛选：只显示有碎片（大于0%）的索引 并且 堆表没有索引结构，不用处理其“碎片”，排除掉。
WHERE ips.avg_fragmentation_in_percent > 0 AND si.index_id <> 0;

重建索引

avg_fragmentation_in_percent是索引碎片率，低于5%可以不考虑。5%-30%建议reorganize，超过30%建议rebuild

2：reorganize

1
alter index [index_name] on dbo.[表名] reorganize;

3：rebuild

1
alter index [index_name] on dbo.[表名] rebuild;

4：rebuild all 重建表上所有索引

1
alter index all on dbo.[表名] rebuild;

SSMS里手动重建索引

image-20260430220137489

1
2
3
4
5
6
7
8
SELECT OBJECT_SCHEMA_NAME(B.OBJECT_ID) 架构,OBJECT_NAME(B.OBJECT_ID) 表名,B.NAME 索引名,ROUND(A.AVG_FRAGMENTATION_IN_PERCENT,2) 碎片率,
    CASE WHEN A.AVG_FRAGMENTATION_IN_PERCENT>30 THEN N'重新生成索引' ELSE N'重新组织索引' END 处理方式,
    'ALTER INDEX '+QUOTENAME(B.NAME)+' ON '+QUOTENAME(OBJECT_SCHEMA_NAME(B.OBJECT_ID))+'.'+QUOTENAME(OBJECT_NAME(B.OBJECT_ID))+' '
        +CASE WHEN A.AVG_FRAGMENTATION_IN_PERCENT>30 THEN 'REBUILD' ELSE 'REORGANIZE' END 生成SQL语句
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A INNER JOIN sys.indexes B ON A.OBJECT_ID=B.OBJECT_ID AND A.INDEX_ID=B.INDEX_ID
WHERE A.AVG_FRAGMENTATION_IN_PERCENT>5 AND B.INDEX_ID>0
    --AND OBJECT_NAME(B.OBJECT_ID) IN ('INVMB')    --指定表
ORDER BY CASE WHEN A.AVG_FRAGMENTATION_IN_PERCENT>30 THEN N'重新生成索引' ELSE N'重新组织索引' END,OBJECT_NAME(B.OBJECT_ID),B.INDEX_ID

索引维护计划

手工重建又极为麻烦，下面利用数据库创建维护计划自动重建/组织数据库表索引方法：

查看表碎片情况，可使用语句：DBCC SHOWCONFIG。可以查看表的扫描密度大小，逻辑碎片及区碎片数量判断表是否需要重建。

1、 打开SQL SERVER数据库（SSMS），选择展开【管理】，找到【维护计划】，右键，选择【维护计划向导】

注意：若【管理】无法展开，或【维护计划向导】创建失败，检查服务中【SQL Server代理】是否处于运行状态，如若未运行，右键【启动】服务。

2、 创建【维护计划向导】后，对弹出的【SQL Server维护计划向导】选择“下一页”

3、 在【选择计划属性】界面中，可重命名维护计划名称。我们也可以对维护计划执行时间周期进行设置，选择【更改】。

在【新建作业计划】界面中，我们可以对计划执行的类型，频率以及持续时间进行设置，设置完毕后，点击“确定”。
Eg. 设置重建索引计划为每周执行一次，执行时间为每周日上午12:00:00分开始执行。计划将于2021/11/12开始执行，持续不间断执行。

【选择计划属性】设置完成后，选择“下一步”。

4、 【选择维护任务】中，勾选“重新生成索引”选项（也可以勾选“重新组织索引”选项对表中索引进行整理与压缩，以提升索引扫描性能），勾选完成后，点击“下一步”。

【选择维护任务顺序】中对任务执行顺序进行设置，如下图仅存在一个任务，无需设置任务执行顺序，点击“下一步”。

5、 【定义“重新生成索引”任务】中，选择需要执行的数据库，也可以对执行索引选项（扫描类型、碎片、页计数等）进行自定义设置执行
Eg. 选择所有数据库执行，对数据库中碎片>30%，页计数>1000表进行索引重建

选择执行维护计划数据库中，可以选择所有数据库，也可以对指定数据库进行勾选执行

6、 【选择报告选项】中，可以设置将报告写入文本文件或以电子邮件形式发送维护计划操作报告。设置完成，点击“下一步”。

7、 得到【完成向导】，点击“完成”。

【维护计划向导进度】，创建成功。

8、 创建维护计划完成后，刷新SQLServer对象资源管理器，如下图，新建的“RebuildIndex”维护计划已显示，选择“RebuildIndex”，右键，【执行】

执行维护计划成功。

标签

tags:SQLsever