MICROSOFT SQL SERVER 2005/2008/2012:Rebuild or Reorganize Indexes based on fragmentation Percentage

This topic describes how to reorganize or rebuild a fragmented index in SQL Server by using SQL Server Management Studio or Transact-SQL. The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.

You can remedy index fragmentation by reorganizing or rebuilding an index. For partitioned indexes built on a partition scheme, you can use either of these methods on a complete index or a single partition of an index. Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction. Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing also compacts the index pages. Compaction is based on the existing fill factor value.

For further reference, http://technet.microsoft.com/en-us/library/ms189858.aspx

1.Create Stored Procedure for rebuild or reorganize indexes based on their Average fragmented value.

This procedure perform operation on all databases in sql server.

/******************************************************************************/   

/* Copyright: 2012 Database Experts  

*    

* Name : [INDEX_REBUILD_REORGANIZE]$   

* Version : $Revision: 1    

* Last Updated : $Modtime:

* Created By : $Author: Bhavesh Thakkar

* Modified By : $Author:    

* Script : This procedure is used to rebuild/reorganize table indexes

* Input:

* Version Info:   

* No.                 Author                            Date                                                           Comments   

* --- ------ ---- --------   

    1                Bhavesh Thakkar                        2012-07-20  

*/

/*****************************************************************************/   

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[INDEX_REBUILD_REORGANIZE]

AS

BEGIN

DECLARE @NAME VARCHAR(50) -- database name

DECLARE @DATABASENAME VARCHAR(50) -- database name

DECLARE @TABLENAME VARCHAR(1000) -- table name

DECLARE @INDEXNAME VARCHAR(1000) -- index name

DECLARE @Sql varchar(5000)-- Script for SQL Queries

DECLARE @Sql1 varchar(5000)-- Script for SQL Queries

DECLARE @dbid int-- Database DB Id

DECLARE @objid int-- Database Object Id

--create #temp,#temp1 tables for operation

CREATE TABLE #TEMP (DATABASENAME VARCHAR(50),TABLENAME VARCHAR(1000),ObjId int,INDEXNAME VARCHAR(1000),INDEXID INT)

CREATE TABLE #TEMP1 (DATABASENAME VARCHAR(50),TABLENAME VARCHAR(1000),INDEX_ID INT,FRAG_VAL DECIMAL(8,3))

DECLARE db_cursor CURSOR FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name  not IN ('master','model','msdb','tempdb')

OPEN db_cursor 

FETCH NEXT FROM db_cursor INTO @name 

WHILE @@FETCH_STATUS = 0 

BEGIN 

       SET @Sql='USE '+@NAME+'  INSERT INTO #TEMP  select '''+@NAME+''' AS DATABASENAME,

                                o.name as TableName,o.object_id objid,i.name as IndexName,I.INDEX_ID 

                                from sys.indexes i

                                join sys.objects o on i.object_id = o.object_id

                                where o.[type] = ''U'' order by o.[name], i.[name]'

                                           

                                           

        EXEC(@Sql)

       

               

           

FETCH NEXT FROM db_cursor INTO @name             

           

END                                       

CLOSE db_cursor 

DEALLOCATE db_cursor

DECLARE db_cursor1 CURSOR FOR SELECT DISTINCT DATABASENAME,TABLENAME,OBJID FROM #TEMP ORDER BY DATABASENAME,TABLENAME

OPEN db_cursor1 

FETCH NEXT FROM db_cursor1 INTO @DATABASENAME,@TABLENAME,@objid 

WHILE @@FETCH_STATUS = 0 

BEGIN 

SET @dbid=DB_ID(@DATABASENAME)

 

SET @Sql='USE '+@DATABASENAME+' INSERT INTO #TEMP1 SELECT '''+@DATABASENAME+''' DATABASENAME,'''+@TABLENAME+''' TABLENAME,a.index_id INDEX_ID,avg_fragmentation_in_percent FRAG_VAL

FROM sys.dm_db_index_physical_stats ('+cast(@dbid as varchar(100))+', '+cast(@objid as varchar(100))+', NULL, NULL, NULL) AS a

    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;'

EXEC(@Sql)

FETCH NEXT FROM db_cursor1 INTO @DATABASENAME,@TABLENAME,@objid 

END

CLOSE db_cursor1 

DEALLOCATE db_cursor1

--operation of rebuild or reorganize based on avg_fragmentation_in_percent

--avg_fragmentation_in_percent >30 % then rebuild

--avg_fragmentation_in_percent <30 % then reorganize

SELECT

       'ALTER INDEX '+A.INDEXNAME+' ON '+A.DATABASENAME+'.'+'dbo.'+A.TABLENAME+CASE WHEN B.FRAG_VAL >30 THEN ' REBUILD' ELSE ' REORGANIZE' END [sql],A.INDEXNAME INDEXNAME INTO #MAIN

      

FROM     #TEMP A INNER JOIN #TEMP1 B

        ON A.DATABASENAME=B.DATABASENAME

       

        AND A.TABLENAME=B.TABLENAME

       

        AND A.INDEXID=B.INDEX_ID

DECLARE db_cursor2 CURSOR FOR

SELECT [SQL],INDEXNAME FROM #MAIN WHERE [sql] IS NOT NULL

OPEN db_cursor2 

FETCH NEXT FROM db_cursor2 INTO @SQL,@INDEXNAME 

WHILE @@FETCH_STATUS = 0 

BEGIN

        PRINT 'PROCESS ON '+@INDEXNAME

       

        EXEC(@SQL)

       

        PRINT 'PROCESS COMPLETED '+@INDEXNAME

       

        FETCH NEXT FROM db_cursor2 INTO @SQL,@INDEXNAME 

END

CLOSE db_cursor2 

DEALLOCATE db_cursor2

END

2.Execute this procedure on weekly and monthly basis.

About Me