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