SQL SERVER REORGANIZE OR REBUILD INDEXES SCRIPT v2.0

Rebuild or reindex ?? good question !

Analysis indexes defragmentation RATIO and decide to REORGANIZE OR REBUILD

WHY ?

Rebuilding is massive time resource consuming, reindexing more fast than rebuild

Scope\purpose of this script di help to decide if rebuild ore reindex….

SOLUTION

RS Repacked Basic Script available time ago and free to use on MSDN: “SQL Script for SQL Index Maintenance (Degraf or Rebuild)”

HOW SYSTEM WORK ?

Example : Analysis indexes defragmentation RATIO < 30% *

“30” is an arbitrary decision point at which to SWITCH between REORGANIZING OR REBUILDING

AFTER DECISION: DO THAT !

IF @frag < 30.0

SET @command = N’ALTER INDEX ‘ + @indexname + N’ ON ‘ + @schemaname + N’.’ + @objectname + N’ REORGANIZE’; */

IF @frag >= 30.0

SET @command = N’ALTER INDEX ‘ + @indexname + N’ ON ‘ + @schemaname + N’.’ + @objectname + N’ REBUILD’; */

************************************************************************

/* RS REORGANIZE OR REBUILD SCRIPT 2.0

SP_DefragORRebuild2.0 */

USE [DATABASE]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[SP_DefragORRebuild2.0]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname nvarchar(130);

DECLARE @objectname nvarchar(130);

DECLARE @indexname nvarchar(130);

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @command nvarchar(4000);

DECLARE @dbid smallint;

— ** READ FROM TABLES WITH Index degrafmentation stattistics

“sys.dm_db_index_physical_stats”

— ** convert object and index IDs to names.

SET @dbid = DB_ID();

SELECT

[object_id] AS objectid,

index_id AS indexid,

partition_number AS partitionnum,

avg_fragmentation_in_percent AS frag, page_count

/* Temporary table to calculate defragmentation RATIO > 10% , CASE > 10%*/

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N’LIMITED’)

WHERE avg_fragmentation_in_percent > 10.0    — Allow limited fragmentation

AND index_id > 0        — Ignore heaps

AND page_count > 25;    — Ignore small tables (not necessary to defragment\rebuild)

— Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR SELECT objectid,indexid, partitionnum,frag FROM #work_to_do; //READ FROM #work_to_do

— Open the cursor.

OPEN partitions;

— Loop through the partitions.

WHILE (1=1)

BEGIN

FETCH NEXT

FROM partitions

INTO @objectid, @indexid, @partitionnum, @frag;

IF @@FETCH_STATUS < 0 BREAK;

SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)

FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @objectid;

SELECT @indexname = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)

FROM sys.partitions

WHERE object_id = @objectid AND index_id = @indexid;

/* Analysis indexes defragmentation RATIO < 30% o >=30% */

— 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

IF @frag < 30.0

SET @command = N’ALTER INDEX ‘ + @indexname + N’ ON ‘ + @schemaname + N’.’ + @objectname + N’ REORGANIZE’;

IF @frag >= 30.0

SET @command = N’ALTER INDEX ‘ + @indexname + N’ ON ‘ + @schemaname + N’.’ + @objectname + N’ REBUILD’;

IF @partitioncount > 1

SET @command = @command + N’ PARTITION=’ + CAST(@partitionnum AS nvarchar(10));

EXEC (@command);

PRINT N’Executed: ‘ + @command;

END

— Close and deallocate the cursor.

CLOSE partitions;

DEALLOCATE partitions;

— Drop the temporary table.

DROP TABLE #work_to_do;

END;