Resolve fragmentation problems for SQL Server tables or indexes

Long-term modification or deletion of a table can produce a large amount of fragmentation that affects database performance. The solution is to rebuild the table or index, eliminate fragmentation, and achieve optimization.

Directly on the code:

* Query Fragmentation, avg_fragmentation_in_percent is the percentage of the index, more than 30 is not normal, need to rebuild * *

DECLARE @db_id int;

DECLARE @object_id int;

SET @db_id = db_id (N ' adventureworks2008r2 ');

SET @object_id = object_id (N ' adventureworks2008r2. Person.Address ');

IF @db_id is NULL

BEGIN;

PRINT N ' Invalid database ';

End;

ELSE IF @object_id is NULL

BEGIN;

PRINT N ' Invalid object ';

End;

ELSE

BEGIN;

SELECT * from Sys.dm_db_index_operational_stats (@db_id, @object_id, NULL, NULL);

End;

Go

/* Rebuild Table * *

ALTER TABLE < table name > rebuild

/* Rebuild Index * *

Alter INDEX < index name > on < table name > rebuild