索引碎片概述
什么是索引碎片，为什么我应该关注它： 当索引包含的页中，索引中的逻辑排序（基于索引中的键值）与索引页中的物理排序不匹配时，就存在碎片。 无论何时对基础数据执行插入、更新或删除操作，数据库引擎 都会自动修改索引。 例如，在表中添加行可能会导致拆分行存储索引中的现有页，以腾出空间来插入新键值。 随着时间的推移，这些修改可能会导致索引中的信息分散在数据库中（含有碎片）。 当索引包含的页中的逻辑排序（基于键值）与数据文件中的物理排序不匹配时，就存在碎片。 大量碎片式索引可能会降低查询性能，因为需要额外 I/O 来查找索引指向的数据。 较多的 I/O 会导致应用程序响应缓慢，特别是在涉及扫描操作时。
检测碎片量
通过使用 sys.dm_db_index_physical_stats，可以检测特定索引中的碎片、表或索引视图上的所有索引、某个数据库中的所有索引或所有数据库中的所有索引。 对于已分区索引， sys.dm_db_index_physical_stats 还提供每个分区的碎片信息。 sys.dm_db_index_physical_stats 返回的结果集包含以下列： 检测行存储索引中的碎片 列 说明 avg_fragmentation_in_percent 逻辑碎片（索引中的无序页）的百分比。 fragment_count 索引中的碎片（物理上连续的叶页）数量。 avg_fragment_size_in_pages 索引中一个碎片的平均页数。 知道碎片程度后，可以使用下表确定删除碎片的最佳方法：INDEX REORGANIZE 或 INDEX。 检测行存储索引中的碎片 avg_fragmentation_in_percent 值 修复语句
5% 且 < = 30% 1 ALTER INDEX REORGANIZE 30% 1 ALTER INDEX REBUILD WITH (ONLINE = ON) 2
DECLARE @db_id SMALLINT
;
DECLARE @object_id INT
;
SET @db_id
= DB_ID
(N
'court_juror');
SET @object_id
= OBJECT_ID
(N
'court_juror.dbo.z_sms');
IF @db_id IS NULL
BEGIN
;
PRINT N
'Invalid database';
END
;
ELSE IF @object_id IS NULL
BEGIN
;
PRINT N
'Invalid object';
END
;
ELSE
BEGIN
;
SELECT avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages FROM sys.dm_db_index_physical_stats
(@db_id, @object_id, NULL, NULL ,
'LIMITED');
END
;
使用脚本中的 sys.dm_db_index_physical_stats 重新生成或重新组织索引
-- Ensure a USE
<databasename
> statement has been executed first.
SET NOCOUNT ON
;
DECLARE @objectid int
;
DECLARE @indexid int
;
DECLARE @partitioncount bigint
;
DECLARE @schemaname nvarchar
(130
);
DECLARE @objectname nvarchar
(130
);
DECLARE @indexname nvarchar
(130
);
DECLARE @partitionnum bigint
;
DECLARE @partitions bigint
;
DECLARE @frag float
;
DECLARE @command nvarchar
(4000
);
-- Conditionally
select tables and indexes from the sys.dm_db_index_physical_stats
function
-- and convert 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
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
-- Open the cursor.
OPEN partitions
;
-- Loop through the partitions.
WHILE
(1
=1
)
BEGIN
;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag
;
IF @@FETCH_STATUS
< 0 BREAK
;
SELECT @objectname
= QUOTENAME
(o.name
), @schemaname
= QUOTENAME
(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
= QUOTENAME
(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
SET @command
= N
'ALTER INDEX ' + @indexname + N
' ON ' + @schemaname + N
'.' + @objectname + N
' REORGANIZE';
IF @frag
>= 30.0
SET @command
= N
'ALTER INDEX ' + @indexname + N
' ON ' + @schemaname + N
'.' + @objectname + N
' REBUILD';
IF @partitioncount
> 1
SET @command
= @command + N
' PARTITION=' + CAST
(@partitionnum AS nvarchar
(10
));
EXEC
(@command
);
PRINT N
'Executed: ' + @command
;
END
;
-- Close and deallocate the cursor.
CLOSE partitions
;
DEALLOCATE partitions
;
-- Drop the temporary table.
DROP TABLE
GO
SQL server官方文档1 SQL server官方文档2
