SQL Server Index Fragmentation

1. Operations that produce fragmentation

By Sys.dm_index_physical_stats, pages on the index are fragmented when they are not contiguous, and fragmentation is the physical result of page splits on the index.

(1). Insert operation:

Insert operations can cause fragmentation on both clustered and nonclustered indexes

Use a business key or GUID to make a clustered index, which can easily produce fragmentation

The code is as follows:

IF object_id (' dbo. Table_guid ') is not nulldrop TABLE dbo. Table_guid; CREATE TABLE table_guid (RowID uniqueidentifier CONSTRAINT df_guidvalue default NEWID (),--use GUID as default value name Sysname,value VARCHAR (2000));--insert data, note that no clustered index has been created at this time insert INTO dbo. Table_guid (  Name, Value) Select Name,replicate (' X ', "a") from Sys.columns SELECT * FROM dbo. table_guid--creating a clustered index on a column Create CLUSTERED index clus_usinguniqueidentifer on dbo. Table_guid (RowID);--View average fragment 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 ')

The average fragment is 0, and the index is not built until it is inserted.

Insert data:

Insert new data into the dbo. Table_guid         (  name, Value) SELECT Name, REPLICATE (' X ', $) from sys.objects

To view index fragmentation:

--View Average fragment 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 ')

Nonclustered indexes:

CREATE nonclustered INDEX ix_name on dbo. Table_guid (Name) INCLUDE (Value)

Perform the above two operations.

This shows: When the insert operation occurs, the fragmentation is unavoidable, the only thing to do is to minimize the production rate of fragmentation.

(2): Update action

--With the new Operation  IF object_id (' dbo. Update_fr ') is not nulldrop TABLE dbo. UPDATE_FR; The CREATE TABLE update_fr (RowID INT IDENTITY (,--) uses the GUID as the default value, name Sysname,value VARCHAR, and INSERT into dbo. Update_fr        (  Name, Value) SELECT name,replicate (' X ', $) from sys.columns CREATE CLUSTERED INDEX clus_ Usinguniqueidentifier on dbo. UPDATE_FR (RowID); --Check the space 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 the data to make the length longer update dbo. Update_fr SET value=replicate (' X ', $) WHERE RowID% 5=1

Changes in key values result in fragmentation:

--Creating a nonclustered index create nonclustered index ix_name on dbo. UPDATE_FR (Name) INCLUDE (Value); --Through the Reverst function to monitor the name reversal and the new debris situation 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 ')

--by Reverst function to monitor the name inversion and the new debris situation

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). Shrink Operation:

 If db_id (N ' fragmentation ') is not a NULL DROP DATABASE fragmentation CREATE database fragmentation use fragmentation IF O BJECT_ID (' dbo. Firsttable ') is not nulldrop TABLE dbo. firsttable; CREATE TABLE dbo. Firsttable (RowID INT IDENTITY (), Name sysname,value VARCHAR (), CONSTRAINT pk_firsttable PRIMARY KEY CLUSTERED ( RowID)); INSERT into dbo. Firsttable (Name, Value) SELECT name,replicate (' X ', *) from sys.columns IF object_id (' dbo. Secondtable ') is not nulldrop TABLE dbo. secondtable; CREATE TABLE dbo. Secondtable (RowID INT IDENTITY (), Name sysname,value VARCHAR (), CONSTRAINT pk_secondtable PRIMARY KEY CLUSTERED ( RowID)); INSERT into dbo. Secondtable (Name, Value) SELECT name,replicate (' X ', "a") from Sys.columnsinsert to dbo. Firsttable (Name, Value) SELECT name,replicate (' X ', "a") from Sys.columnsinsert to dbo. Secondtable (Name, Value) SELECT name,replicate (' X ', "a") from Sys.columnsinsert to dbo. Firsttable (Name, Value) SELECT name,repliCATE (' X ', ') 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 ')

The same as the index fragment before and after deletion.

Because SQL Server does not automatically recycle, call DBCC SHRINKDATABASE to shrink the database and look at the fragmentation situation again.

DBCC Shrinkdatabase (fragmentation)

To view fragmentation again:

So auto shrink data is not recommended.

SQL Server Index Fragmentation