Friday, July 20, 2012

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.

















No comments:

Post a Comment

Dear User,

Thank you very much for your kind response