当前位置：首页 > 数据库 > MSSQL教程 > 索引及统计信息2 > 正文

索引及统计信息2

来源：这里教程网 时间：2026-03-02 10:44:05 作者：

五  索引维护

5.1 索引碎片

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

聚集索引引起碎片

    if object_id('dbo.Table_GUID') IS NOT NULL
         drop table dbo.Table_GUID;
     create table dbo.table_guid(
     rowid uniqueidentifier constraint df_guidvalue default newid(),
     name sysname,
     value varchar(2000)
     );
--插入数据，注意此时还没有聚集索引
    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')

这时从结果可以看到，平均碎片为 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')









编辑推荐：

相关推荐

最新软件资讯

索引及统计信息2
索引及统计信息2

热文推荐

天极热推