Solve the Problem of SQL Server table or index fragmentation

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 shards. avg_fragmentation_in_percent indicates the percentage of indexes. If the value is greater than 30, It is abnormal. You need to re-create * DECLARE @ db_idint; DE

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:/* to query fragments. avg_fragmentation_in_percent indicates the percentage of indexes. If the value is greater than 30, It is abnormal. re-create */DECLARE @ db_id int; DE.

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 fragmentation. avg_fragmentation_in_percent indicates the percentage of indexes. If the value is greater than 30, It is abnormal. Reconstruction is required */

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 <表名> Rebuild

/* Re-Index */

Alter index <索引名> ON <表名> Rebuild