Long-term modification or deletion of a table will produce a large number of fragments, affecting the database performance. The solution is to reconstruct the table or index, eliminate fragments, and achieve optimization.
Directly run the Code:
/* Query fragments. avg_fragmentation_in_percent indicates the percentage of indexes. If the value is greater than 30, it is not normal and needs to be rebuilt */
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 'invalidobject ';
END;
ELSE
BEGIN;
SELECT * FROM sys. dm_db_index_operational_stats (@ db_id, @ object_id, NULL, NULL );
END;
GO
/* Recreate the table */
Alter table <table Name> rebuild
/* Re-Index */
Alter index <index Name> ON <Table Name> rebuild