SQL server index fragmentation – reorganize or rebuild example

SQL server index fragmentation – reorganize or rebuild

Below are some SQL scripts to reorganize or rebuild an index. 

DECLARE @dbname nvarchar(50);

DECLARE @tblname nvarchar(100);

DECLARE @dbid int;

DECLARE @tblid int;

SET @dbname = 'AdventureWorks2008R2';

SET @tblname = @dbname + N'.dbo.Customer'

SELECT @dbid = DB_ID(@dbname);

SELECT @dbid  AS [Database ID];

SELECT @tblid = OBJECT_ID(@tblname);

SELECT @tblid AS [Object ID];

GO

Database ID

-----------

44

(1 row(s) affected)

Object ID

-----------

1605580758

(1 row(s) affected)

USE master;

GO

DECLARE @dbname nvarchar(50);

DECLARE @tblname nvarchar(100);

SET @dbname = 'AdventureWorks2008R2';

SET @tblname = @dbname + N'.dbo.Customer'

SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(@dbname), OBJECT_ID(@tblname), NULL, NULL , 'DETAILED')

WHERE avg_fragmentation_in_percent > 30;

GO

--- defragmentation of the index , reorganize ----------

USE AdventureWorks2008R2

GO

ALTER INDEX PK_Customer ON dbo.Customer

      REORGANIZE WITH ( LOB_COMPACTION = OFF );

GO

--- defragmentation of the index, rebuild ------------------------------------

USE AdventureWorks2008R2

GO

ALTER INDEX PK_Customer ON dbo.Customer

      REBUILD;

GO