索引碎片整理(转)
2015-03-18 11:25 ko绝缘体 阅读(180) 评论(0) 编辑 收藏 举报索引碎片整理
一碎片种类
1 内部碎片,又称为平均页密度。是指索引正在占有超过它实际所需的空间大小。
它具有两面型:低百分比会对读取数据的查询产生负面影响,会涉及更多读取操作,因为如果页被填充满的话,
只需读取更少的页;另一方面,如果如果在创建索引时设置一个较低的填充因子,就可以避免当插入更多记录而不
必进行页拆分。
对应sys.dm_db_index_physical_stats的列avg_page_space_used_in_percent。
2 外部碎片,又称平均碎片百分比,或逻辑碎片。是指在分页的逻辑顺序与物理顺序不匹配或者索引拥有的扩展不
连续时产生。包括以下两种:
逻辑碎片:这是索引的叶级页中出错页所占的百分比。出错页是指在IAM 中所指示的下一页不同于由叶级页中的下一页指针所指向的页。
区碎片(有的书翻译成:扩展碎片):这是堆的叶级页中出错区所占的百分比。出错区是指:包含堆的当前页的区不是物理上的包含前一页的区后的下一个区。
这种碎片对索引的有序扫描操作具有非常显著的影响。它会对那些不依赖于索引链接的列表的操作(例如:查找操作,lookup操作,无序扫描)不产生影响。
对应sys.dm_db_index_physical_stats的列avg_fragmentation_in_percent 。因此为了获得最佳性能,avg_fragmentation_in_percent 的值应尽可能接近零。
但是,从0 到10%范围内的值都可以接受。所有减少碎片的方法(例如重新生成、重新组织或重新创建)都可用于降低这些值。
二碎片的检测
使用系统函数sys.dm_db_index_physical_stats 可以检测特定索引、表或索引视图的所有索引、一个数据库中的所有索引或所有数据库中的所有索引中的碎片。
扫描模式如下:
LIMITED 模式运行最快,扫描的页数最少。对于堆,它将扫描所有页,但对于索引,则只扫描叶级上面的父级别页。
在该模式下,以下列将显示为NULL:avg_page_space_used_in_percent,record_count,min_record_size_in_bytes,max_record_size_in_bytes,avg_record_size_in_bytes
SAMPLED 模式将返回基于索引或堆中所有页的1%样本的统计信息。如果索引或堆少于10,000 页,则使用DETAILED 模式代替SAMPLED。
DETAILED 模式将扫描所有页并返回所有统计信息。
需要重点关注的几列:
()index_id 索引的索引ID。= 堆。
()avg_fragmentation_in_percent 索引的逻辑碎片或堆的区碎片。
注:因此为了获得最佳性能,avg_fragmentation_in_percent 的值应尽可能接近零。
()avg_page_space_used_in_percent 内部碎片
()
fragment_count 索引中的碎片(物理上连续的叶页)数量
avg_fragment_size_in_pages 索引中一个碎片的平均页数。
注:
该值大于个分页,扫描性能会相当不错,上限为个分页,如果超过个分页,性能提升不明显。
碎片由分配单元中同一文件内的物理连续的叶级页组成。一个索引至少有一个碎片。索引可以包含的最大碎片数等于索引的页级别页数。碎片越大,意味着读取相同页数所需的磁盘I/O 越少。因此,avg_fragment_size_in_pages 值越大,范围扫描的性能越好。avg_fragment_size_in_pages 和avg_fragmentation_in_percent 值成反比。因此,重新生成或重新组织索引会减少碎片数量,但同时增大碎片大小。
()forwarded_record_count 堆中的前推记录数。
注:如果数据表拥有的许多前推记录数,全表扫描就会非常有效率。
()
ghost_record_count 分配单元中将被虚影清除任务删除的虚影记录数。
version_ghost_record_count 由分配单元中未完成的快照隔离事务保留的虚影记录数。
注:虚影记录数是物理上仍然存在于分页上,但逻辑上已经被删除的记录。
如果存在大量的虚影记录,数据表就具有大量的内部碎片的缺陷且没有任何益处。这些虚影记录
在所有相关事务被提交或者回滚以前都不会被清除。
--查询脚本
--为防止db_id和object_id无效时,会返回NULL,而不会报错,这样就会查询所有数据库和数据库中的全部对象,
--而不是我们要查询的数据库和数据库对象
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks');
SET @object_id = OBJECT_ID(N'AdventureWorks.Person.Address');
IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN
PRINT N'Invalid object';
END;
SELECT * FROM sys.dm_db_index_physical_stats
(@db_id, @object_id, NULL, NULL, NULL);
三碎片移除
()重新组织
范围:avg_fragmentation_in_percent 的值在到之间
ALTER INDEX idx_cl_od ON dbo.Orders REORGANIZE;
()重新生成
范围:avg_fragmentation_in_percent 值大于或avg_fragment_size_in_pages值小于
ALTER INDEX idx_cl_od ON dbo.Orders REBUILD WITH (ONLINE = ON);
四索引重建
ALTER INDEX PK_TransactionHistory_TransactionID
ON Production.TransactionHistory REBUILD;
-- 推荐DROP_EXISTING属性
CREATE UNIQUE CLUSTERED INDEX PK_TransactionHistory_TransactionID
ON Production.TransactionHistory
(TransactionDate, TransactionID)
WITH DROP_EXISTING;
--将自动重新组织或重新生成数据库中平均碎片超过10%的所有分区
-- ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command varchar(8000);
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
-- conditionally select from the function, converting object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
SELECT @objectname = o.name, @schemaname = s.name
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = name
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding
IF @frag < 30.0
BEGIN;
SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REORGANIZE';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
END;
IF @frag >= 30.0
BEGIN;
SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + @objectname + ' REBUILD';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
END;
PRINT 'Executed ' + @command;
FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- drop the temporary table
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
GO
五存在疑问
()填充因子的大小,该如何决定呢?
()“碎片越大,意味着读取相同页数所需的磁盘I/O 越少”,这句话不太理解。
()虚影记录,该如何处理?
解答:
()
它依赖于应用程序对SQLServer表的读和写的比率。首要的原则,按照下面的指导:
低更改的表(读写比率为:):%的填充因子
高更改的表(写超过读):-70%的填充因子
读写各一半的:-90%的填充因子
(转深潭)