五索引维护

5.1.1 产生碎片的操作
碎片的问题主要是通过 sys.dm_db_index_physical_stats来查看的。
(1) 插入操作
insert 操作在聚集索引和非聚集索引上都可能引起碎片

聚集索引引起碎片

--插入数据，注意此时还没有聚集索引

--在列上创建聚集索引

--查看平均碎片

select index_type_desc,
index_depth,
index_level,
page_count,
record_count,
CAST(avg_fragmentation_in_percent as decimal(6,2)) as avg_fragmentation_in_percent,
fragment_count,
avg_fragment_size_in_pages,
CAST(avg_page_space_used_in_percent as decimal(6,2)) as avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.table_guid'),NULL,NULL,'DETAILED')

这时从结果可以看到，平均碎片为 0，因为在插入后才建索引。

然后执行插入操作。并查看索引碎片。

insert into dbo.table_guid(name,value) select name,REPLICATE('x',2000)
from sys.columns
create clustered index clus_usinguniqueidentifier on dbo.table_guid(rowid);
select index_type_desc,
index_depth,
index_level,
page_count,
record_count,
CAST(avg_fragmentation_in_percent as decimal(6,2)) as avg_fragmentation_in_percent,
fragment_count,
avg_fragment_size_in_pages,
CAST(avg_page_space_used_in_percent as decimal(6,2)) as avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.table_guid'),NULL,NULL,'DETAILED')

碎片情况如图：

从上图可以看出，碎片在迅速增长。

非聚集索引引起碎片

create nonclustered index IX_Name on dbo.table_guid(Name) include(value);
select index_type_desc,
index_depth,
index_level,
page_count,
record_count,
CAST(avg_fragmentation_in_percent as decimal(6,2)) as avg_fragmentation_in_percent,
fragment_count,
avg_fragment_size_in_pages,
CAST(avg_page_space_used_in_percent as decimal(6,2)) as avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.table_guid'),NULL,NULL,'DETAILED')

查看碎片

插入数据后，再次查看：

点击(此处)折叠或打开

insert into dbo.table_guid(name,value) select name,REPLICATE('x',2000)
from sys.OBJECTS
select index_type_desc,
index_depth,
index_level,
page_count,
record_count,
CAST(avg_fragmentation_in_percent as decimal(6,2)) as avg_fragmentation_in_percent,
fragment_count,
avg_fragment_size_in_pages,
CAST(avg_page_space_used_in_percent as decimal(6,2)) as avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.table_guid'),NULL,NULL,'DETAILED')

(2) 更新操作
update操作也会引起碎片

点击(此处)折叠或打开

if OBJECT_ID('dbo.Update_Fr') IS NOT NULL
drop table dbo.Update_Fr;
create table dbo.Update_Fr(
rowid int identity(1,1),
name sysname,
value varchar(2000)
);
insert into dbo.Update_Fr(
name,value)
select name ,REPLICATE('x',1000)
from sys.columns
create clustered index clus_usingUniqueidentifier on dbo.update_fr(rowid);
select index_type_desc,
index_depth,
index_level,
page_count,
record_count,
CAST(avg_fragmentation_in_percent as decimal(6,2)) as avg_fragmentation_in_percent,
fragment_count,
avg_fragment_size_in_pages,
CAST(avg_page_space_used_in_percent as decimal(6,2)) as avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.update_fr'),NULL,NULL,'DETAILED')

接下来更新数据，让长度变长。

点击(此处)折叠或打开

update dbo.update_fr
set value=replicate('x',2000)
where rowid%5=1
select index_type_desc,
index_depth,
index_level,
page_count,
record_count,
CAST(avg_fragmentation_in_percent as decimal(6,2)) as avg_fragmentation_in_percent,
fragment_count,
avg_fragment_size_in_pages,
CAST(avg_page_space_used_in_percent as decimal(6,2)) as avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.update_fr'),NULL,NULL,'DETAILED')

可以看出，碎片增长非常快。

创建一个非聚集索引

点击(此处)折叠或打开

create nonclustered index ix_name on dbo.update_fr(name) include (value)
select index_type_desc,
index_depth,
index_level,
page_count,
record_count,
CAST(avg_fragmentation_in_percent as decimal(6,2)) as avg_fragmentation_in_percent,
fragment_count,
avg_fragment_size_in_pages,
CAST(avg_page_space_used_in_percent as decimal(6,2)) as avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.update_fr'),NULL,NULL,'DETAILED')

通过 reverse函数把名称反转，监控更新前后的碎片情况

点击(此处)折叠或打开

update dbo.update_fr
set name=reverse(name)
where rowid%9=1
select index_type_desc,
index_depth,
index_level,
page_count,
record_count,
CAST(avg_fragmentation_in_percent as decimal(6,2)) as avg_fragmentation_in_percent,
fragment_count,
avg_fragment_size_in_pages,
CAST(avg_page_space_used_in_percent as decimal(6,2)) as avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.update_fr'),NULL,NULL,'DETAILED')

ITPub博客