我的编程空间,编程开发者的网络收藏夹
学习永远不晚

SQL Server索引碎片维护进阶

短信预约 信息系统项目管理师 报名、考试、查分时间动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

SQL Server索引碎片维护进阶

SQL Server索引碎片维护进阶

一、产生原因及影响

索引是数据库引擎中针对表(有时候也针对视图)建立的特别数据结构,用来帮助查找和整理数据,它的重要性体现在能够使数据库引擎快速返回查询结果。当对索引所在的基础数据表进行增删改时,若存储的数据进行了不适当的跨页(SQL Server中存储的最小单位是页,页是不可再分的),就会导致索引碎片的产生。随着索引碎片的不断增多,查询响应时间就会变慢,性能也因此而下降。要解决这个问题,可以通过重新生成或重新组织索引来解决。

二、碎片分类

1、外部碎片

当索引页不在逻辑顺序上时就会产生外部碎片。索引创建时,索引键按照逻辑顺序放在一组索引页上。当新数据插入索引时,新的键可能放在存在的键之间。为了让新的键按照正确的顺序插入,可能会创建新的索引页来存储需要移动的那些存在的键。这些新的索引页通常物理上不会和那些被移动的键原来所在的页相邻。创建新页的过程会引起索引页偏离逻辑顺序。

2、内部碎片

当索引页没有用到最大量时就产生了内部碎片。虽然在一个有频繁数据插入的应用程序里这也许有帮助,然而设置一个fill factor(填充因子)会在索引页上留下空间,服务器内部碎片会导致索引尺寸增加,从而在返回需要的数据时要执行额外的读操作。这些额外的读操作会降低查询的性能。

三、维护方法

删除索引并重建。

使用DROP_EXISTING语句重建索引。

使用ALTER INDEX REBUILD重新生成索引。(推荐)

使用ALTER INDEX REORGANIZE重新组织索引。(推荐)

四、注意事项

碎片率 采用方法
>30% ALTER INDEX REBUILD WITH(ONLINE = ON)
>5% 且 <=30% ALTER INDEX REORGANIZE

重新生成索引可以联机执行,也可以脱机执行。

重新组织索引始终联机执行。这些值提供了一个大致指导原则,用于确定应在ALTER INDEX REORGANIZE和ALTER INDEX REBUILD之间进行切换的点。不过,实际值可能会随情况而变化,必须要通过试验来确定最适合您环境的阈值。

非常低的碎片级别(小于5%)不应通过这些命令来解决,因为删除如此少量的碎片所获得的收益始终远低于重新生成或重新组织索引的开销。

切记:所有索引碎片维护一定要在凌晨(非业务高峰期间)进行!!!

五、优化指导原则

5.1、如何知道是否发生了索引碎片?

在SQL Server数据库中,可以通过DBCC SHOWCONTIG WITH ALL_INDEXESDBCC SHOWCONTIG(表ID或者表名) WITH ALL_INDEXES来检查索引碎片情况。

--方法一
--目标数据库
USE DB_NAME
--创建变量指定要查看的表
DECLARE @TABLE_ID INT
SET @TABLE_ID=OBJECT_ID("TABLE_NAME")
--执行
DBCC SHOWCONTIG(@TABLE_ID) WITH ALL_INDEXES

--方法二
USE DB_NAME
DBCC SHOWCONTIG("TABLE_NAME") WITH ALL_INDEXES

5.2、索引碎片判断标准

通过对逻辑扫描碎片(过高)、平均页密度(满)(过低)的结果分析,判定是否需要进行索引处理,如下所示:

逻辑扫描碎片 ..................:97.83% 该百分比应该在0%到10%之间,高了则说明有外部碎片。

平均页密度(满) ..................:62.42% 该百分比应该尽可能靠近100%,低了则说明有外部碎片。

六、优化实践

6.1、手动方式

第一步:查询数据库所有表的索引信息。

SELECT OBJECT_NAME(B.OBJECT_ID) 表名,B.NAME 索引名称,A.INDEX_TYPE_DESC 索引类型,
    ROUND(A.AVG_FRAGMENTATION_IN_PERCENT,2) 碎片率
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 1=1
    AND A.AVG_FRAGMENTATION_IN_PERCENT>30
    --AND A.AVG_FRAGMENTATION_IN_PERCENT>5 AND A.AVG_FRAGMENTATION_IN_PERCENT<=30
ORDER BY OBJECT_NAME(B.OBJECT_ID),A.AVG_FRAGMENTATION_IN_PERCENT DESC

注:通过碎片率,依四、注意事项处理方式,也可以逐个对表的索引进行对应的重新生成或重新组织处理。

第二步:生成数据库所有表的索引处理的SQL语句。

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

注:将【生成SQL语句】拷贝出来执行即可。

6.2、自动方式

第一步:在服务中启动SQL Server 代理。

第二步:点击"管理"->右键"维护计划"->"新建维护计划"。

第三步:起个名字,点击"确定"。

第四步:点击左侧"工具箱",将"重新生成索引"及"重新组织索引"拖至右边区域。

第五步:分别对着"重新生成索引"及"重新组织索引"点击右键->"编辑"->在"数据库"项勾选要处理的数据库->点击"确定"。

第六步:点击"新建作业计划"按钮->设置频率及执行时间->点击"确定"。

第七步:点击"保存选定项"即可。

七、更新统计信息

作用:UPDATE STATISTICS更新统计信息来提高查询效率。建议放在索引碎片计划任务执行完成之后进行。

查看:查看某个表的统计信息,可以在SSMS下面查看。

执行:

--方法一:UPDATE STATISTICS 表名
UPDATE STATISTICS INVMB

--方法二:执行存储过程SP_UPDATESTATS(更新所有表)
EXEC SP_UPDATESTATS

 

后记:建议不要过于频繁地执行重新生成索引、重新组织索引以及更新统计信息。

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

SQL Server索引碎片维护进阶

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

SQL Server索引碎片维护进阶

一、产生原因及影响索引是数据库引擎中针对表(有时候也针对视图)建立的特别数据结构,用来帮助查找和整理数据,它的重要性体现在能够使数据库引擎快速返回查询结果。当对索引所在的基础数据表进行增删改时,若存储的数据进行了不适当的跨页(SQL Server中存储的最小单
SQL Server索引碎片维护进阶
2020-05-08

SQL Server高级进阶之索引碎片维护

一、产生原因及影响索引是数据库引擎中针对表(有时候也针对视图)建立的特别数据结构,用来帮助查找和整理数据,它的重要性体现在能够使数据库引擎快速返回查询结果。当对索引所在的基础数据表进行增删改时,若存储的数据进行了不适当的跨页(SQL Server中存储的最小单
SQL Server高级进阶之索引碎片维护
2020-06-22

SQL Server高级进阶之索引碎片

一、产生原因及影响索引是数据库引擎中针对表(有时候也针对视图)建立的特别数据结构,用来帮助查找和整理数据,它的重要性体现在能够使数据库引擎快速返回查询结果。当对索引所在的基础数据表进行增删改时,若存储的数据进行了不适当的跨页(SQL Server中存储的最小单
SQL Server高级进阶之索引碎片
2020-09-09

SQL Server索引碎片的原因和修复

目录前言1. 基本知识2. 检索碎片2.1 dm_db_index_physical_stats2.2 DBCC SHOWCONTIG3. 修复和优化前言索引碎片太高本身会阻碍查询的效率,这个问题要重视1. 基本知识索引中的数据页不再
SQL Server索引碎片的原因和修复
2024-09-14

SQL Server高级进阶之索引优化

1.1、查找缺失索引SELECT A.USER_SEEKS 查找次数,A.USER_SCANS 扫描次数, ROUND(A.AVG_TOTAL_USER_COST,2) 减少的用户查询的平均成本,A.AVG_USER_IMPACT 可能获得的平均百分比收
SQL Server高级进阶之索引优化
2016-07-26

SQL Server高级进阶之索引优化查询

1.1、查找缺失索引SELECT A.USER_SEEKS 查找次数,A.USER_SCANS 扫描次数, ROUND(A.AVG_TOTAL_USER_COST,2) 减少的用户查询的平均成本,A.AVG_USER_IMPACT 可能获得的平均百分比收
SQL Server高级进阶之索引优化查询
2014-08-11

描述SQL Server中索引碎片的影响以及如何处理它

索引碎片是指索引中数据的物理存储并非按照逻辑顺序排列,导致数据库性能下降的情况。索引碎片会增加查询的成本,降低数据库的性能。处理索引碎片的方法包括重新组织索引和重建索引。重新组织索引是通过重新排列索引中的数据来减少碎片,而不会改变索引的定
描述SQL Server中索引碎片的影响以及如何处理它
2024-06-04

编程热搜

目录