SqlServer索引碎片

SqlServer索引碎片

1.产生碎片的操作

通过sys.dm_index_physical_stats来查看，索引上的页不在具有连续性时就会产生碎片，碎片是索引上页拆分的物理结果。

（1）.插入操作：

INSERT操作在聚集索引和非聚集索引上都可以引起碎片

使用业务键或者GUID等类型 做聚集索引，很容易产生碎片

代码如下：

IF OBJECT_ID('dbo.Table_GUID') IS NOT NULLDROP TABLE dbo.Table_GUID;CREATE TABLE Table_GUID(RowID UNIQUEIDENTIFIER CONSTRAINT DF_GUIDValue DEFAULT NEWID(),--使用GUID作为默认值Name sysname,Value VARCHAR(2000));--插入数据,注意此时还没有创建聚集索引INSERT INTO dbo.Table_GUID( Name, Value ) SELECT name,REPLICATE('X',2000) FROM sys.columns SELECT * FROM dbo.Table_GUID--在列上创建聚集索引CREATE CLUSTERED INDEX CLUS_UsingUniqueidentifer 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.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')

非聚集索引：

CREATE NONCLUSTERED INDEX IX_Name ON dbo.Table_GUID(Name) INCLUDE(Value)

执行以上两次操作。

由此可见：当INSERT操作发生时，产生碎片再所难免，唯一要做的是尽可能降低碎片的产生速率。

（2）：更新操作

--跟新操作 IF OBJECT_ID('dbo.Update_Fr') IS NOT NULLDROP TABLE dbo.Update_Fr;CREATE TABLE Update_Fr(RowID INT IDENTITY(1,1),--使用GUID作为默认值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

键值的改变导致碎片的产生：

--创建一个非聚集索引 CREATE NONCLUSTERED INDEX IX_Name ON dbo.Update_Fr(Name) INCLUDE (Value); --通过REVERST函数把名称反转监控跟新前后的碎片情况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')

--通过REVERST函数把名称反转监控跟新前后的碎片情况

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')

（3）.收缩操作：

IF DB_ID(N'Fragmentation') IS NOT NULL DROP DATABASE Fragmentation CREATE DATABASE Fragmentation USE Fragmentation IF OBJECT_ID('dbo.FirstTable') IS NOT NULLDROP TABLE dbo.FirstTable;CREATE TABLE dbo.FirstTable(RowID INT IDENTITY(1,1),Name sysname,Value VARCHAR(2000),CONSTRAINT PK_FirstTable PRIMARY KEY CLUSTERED(RowID));INSERT INTO dbo.FirstTable ( Name, Value )SELECT name,REPLICATE('X',2000) FROM sys.columns IF OBJECT_ID('dbo.SecondTable') IS NOT NULLDROP TABLE dbo.SecondTable;CREATE TABLE dbo.SecondTable(RowID INT IDENTITY(1,1),Name sysname,Value VARCHAR(2000),CONSTRAINT PK_SecondTable PRIMARY KEY CLUSTERED(RowID));INSERT INTO dbo.SecondTable ( Name, Value )SELECT name,REPLICATE('X',2000) FROM sys.columnsINSERT INTO dbo.FirstTable ( Name, Value )SELECT name,REPLICATE('X',2000) FROM sys.columnsINSERT INTO dbo.SecondTable ( Name, Value )SELECT name,REPLICATE('X',2000) FROM sys.columnsINSERT INTO dbo.FirstTable ( Name, Value )SELECT name,REPLICATE('X',2000) FROM sys.columns 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.FirstTable'),NULL,NULL,'DETAILED') IF OBJECT_ID('dbo.SecondTable') IS NOT NULLDROP TABLE dbo.SecondTable; 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.FirstTable'),NULL,NULL,'DETAILED')

删除前后的索引碎片一样。

由于SqlServer不会自动回收，调用DBCC SHRINKDATABASE来收缩数据库，再次查看碎片情况。

DBCC SHRINKDATABASE(Fragmentation)

再次查看碎片：

所以自动收缩数据不建议使用。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。