SqlServer索引碎片

1.产生碎片的操作

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

（1）.插入操作：

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

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

代码如下：

 IF OBJECT_ID('dbo.Table_GUID') IS NOT NULL

DROP 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 NULL

DROP 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 NULL

DROP 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 NULL

DROP 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.columns

INSERT INTO dbo.FirstTable

        (  Name, Value )

SELECT name,REPLICATE('X',2000) FROM sys.columns

INSERT INTO dbo.SecondTable

        (  Name, Value )

SELECT name,REPLICATE('X',2000) FROM sys.columns

INSERT 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 NULL

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

再次查看碎片：

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

巴特西

SqlServer索引碎片

最新文章

热门文章