Index DeFragmentation


/*

Index Defragmentation is one of the most important DBA tasks. This will significantly improve query performance. When you perform any DML operation (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. If you use predefined maintenance plan it will take much server resource and time. Here is a custom stored procedure.

If you want to determine the level of fragmentation, you can use the SYS.DM_DB_INDEX_PHYSICAL_STATS statement. The SYS.DM_DB_INDEX_PHYSICAL_STATS DMV displays fragmentation information for the data and indexes of the specified object.

Here I use 3 conditions :

1. Fragmentation >30 AND PAGES>1000 then rebuild
2. Fragmentation between 15 to 30 AND PAGES>1000 then reorganize&updatestatistics
3. If the above two conditions are false then update the statistics

Before you run the procedure create the tables provided for history propose

Click the following URL Index Architecture By Gail Shaw–>http://www.sqlservercentral.com/articles/Indexing/68439/

*/

————————-For SQL-2005/2008—————————-

USE MSDB;

CREATE TABLE [DBO].[DBA_DEFRAG_MAINTENANCE_HISTORY]

(

[DB_NAME] [SYSNAME] NOT NULL,

[TABLE_NAME] [SYSNAME] NOT NULL,

[INDEX_NAME] [SYSNAME] NOT NULL,

[FRAG] [FLOAT] NULL,

[PAGE] [INT] NULL,

[ACTION_TAKEN] [VARCHAR](35) NULL,

[DATE] [DATETIME] NULL DEFAULT (GETDATE())

) ON [PRIMARY]

–Archive the data’s in master DB

USE MASTER;

CREATE TABLE [DBO].[DBA_DEFRAG_MAINTENANCE_HISTORY]

(

[DB_NAME] [SYSNAME] NOT NULL,

[TABLE_NAME] [SYSNAME] NOT NULL,

[INDEX_NAME] [SYSNAME] NOT NULL,

[FRAG] [FLOAT] NULL,

[PAGE] [INT] NULL,

[ACTION_TAKEN] [VARCHAR](35) NULL,

[DATE] [DATETIME] NULL DEFAULT (GETDATE())

) ON [PRIMARY]

— Defragmentation Procedure:

— Note: Below script I use try/catch statement if PAGE LEVEL LOCK is disabled then the index has rebuild.

–EXEC [MSDB].[DBO].[USP_DBA_INDEX_DEFRAGMENTATION] ‘dba_db1’

USE [MSDB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROC [DBO].[USP_DBA_INDEX_DEFRAGMENTATION]

@P_DBNAME SYSNAME

AS

BEGIN

SET NOCOUNT ON

DECLARE

@DB_NAME SYSNAME,

@TAB_NAME SYSNAME,

@IND_NAME VARCHAR(5000),

@SCHEMA_NAME SYSNAME,

@FRAG FLOAT,

@PAGES INT

SET @DB_NAME=@P_DBNAME

TRUNCATE TABLE MSDB.DBO.DBA_DEFRAG_MAINTENANCE_HISTORY

CREATE TABLE #TEMPFRAG

(

TABLE_NAME SYSNAME,

INDEX_NAME VARCHAR(5000),

FRAG FLOAT,

PAGES INT,

SCHEM_NAME SYSNAME

)

EXEC (‘USE [‘+@DB_NAME+’];

INSERT INTO #TEMPFRAG

SELECT OBJECT_NAME(F.OBJECT_ID) OBJ,I.NAME IND,

F.AVG_FRAGMENTATION_IN_PERCENT,

F.PAGE_COUNT,TABLE_SCHEMA

FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(),NULL,NULL,NULL,NULL) F

JOIN SYS.INDEXES I

ON(F.OBJECT_ID=I.OBJECT_ID)AND I.INDEX_ID=F.INDEX_ID

JOIN INFORMATION_SCHEMA.TABLES S

ON (S.TABLE_NAME=OBJECT_NAME(F.OBJECT_ID))

–WHERE INDEX_ID<> 0

AND F.DATABASE_ID=DB_ID()

AND OBJECTPROPERTY(I.OBJECT_ID,”ISSYSTEMTABLE”)=0′

)

DECLARE CUR_FRAG CURSOR FOR

SELECT * FROM #TEMPFRAG

OPEN CUR_FRAG

FETCH NEXT FROM CUR_FRAG INTO

@TAB_NAME ,@IND_NAME , @FRAG , @PAGES ,@SCHEMA_NAME

WHILE @@FETCH_STATUS=0

BEGIN

IF (@IND_NAME IS NOT NULL)

BEGIN

IF (@FRAG>30 AND @PAGES>1000)

BEGIN

EXEC (‘USE [‘+@DB_NAME+’];ALTER INDEX [‘+@IND_NAME+’] ON [‘+@SCHEMA_NAME+’].[‘+@TAB_NAME +’] REBUILD ‘)

INSERT INTO MSDB.DBO.DBA_DEFRAG_MAINTENANCE_HISTORY

VALUES (@DB_NAME,@TAB_NAME,@IND_NAME,@FRAG,@PAGES,’REBUILD’,GETDATE())

END

ELSE IF((@FRAG BETWEEN 15 AND 30 ) AND @PAGES>1000 )

BEGIN

–IF PAGE LEVEL LOCKING IS DISABLED (PLLD) THEN REBUILD

BEGIN TRY

EXEC (‘USE [‘+@DB_NAME+’];ALTER INDEX [‘+@IND_NAME+’] ON [‘+@SCHEMA_NAME+’].[‘+@TAB_NAME +’] REORGANIZE ‘)

EXEC (‘USE [‘+@DB_NAME+’];UPDATE STATISTICS [‘+@SCHEMA_NAME+’].[‘+@TAB_NAME+’] ([‘+@IND_NAME+’]) ‘ )

INSERT INTO MSDB.DBO.DBA_DEFRAG_MAINTENANCE_HISTORY

VALUES (@DB_NAME,@TAB_NAME,@IND_NAME,@FRAG,@PAGES,’REORGANIZE & UPDATESTATS’,GETDATE())

END TRY

BEGIN CATCH

IF ERROR_NUMBER()=2552

EXEC (‘USE [‘+@DB_NAME+’];ALTER INDEX [‘+@IND_NAME+’] ON [‘+@SCHEMA_NAME+’].[‘+@TAB_NAME +’] REBUILD ‘)

INSERT INTO MSDB.DBO.DBA_DEFRAG_MAINTENANCE_HISTORY

VALUES (@DB_NAME,@TAB_NAME,@IND_NAME,@FRAG,@PAGES,’PLLD_REBUILD’,GETDATE())

END CATCH

END

ELSE 

BEGIN 

EXEC (‘USE [‘+@DB_NAME+’];UPDATE STATISTICS [‘+@SCHEMA_NAME+’].[‘+@TAB_NAME+’] ([‘+@IND_NAME+’]) ‘  ) 

INSERT INTO MSDB.DBO.DBA_DEFRAG_MAINTENANCE_HISTORY

VALUES (@DB_NAME,@TAB_NAME,@IND_NAME,@FRAG,@PAGES,’UPDATESTATS’,GETDATE()) 

END  

END 

ELSE

BEGIN

EXEC (‘USE [‘+@DB_NAME+’];UPDATE STATISTICS [‘+@SCHEMA_NAME+’].[‘+@TAB_NAME+’]’) 

INSERT INTO MSDB.DBO.DBA_DEFRAG_MAINTENANCE_HISTORY 

VALUES (@DB_NAME,@TAB_NAME,’HEAP’,@FRAG,@PAGES,’UPDATESTATS’,GETDATE()) 

END

FETCH NEXT FROM CUR_FRAG INTO

@TAB_NAME ,@IND_NAME , @FRAG , @PAGES ,@SCHEMA_NAME

END

DROP TABLE #TEMPFRAG

CLOSE CUR_FRAG

DEALLOCATE CUR_FRAG

INSERT INTO MASTER.DBO.DBA_DEFRAG_MAINTENANCE_HISTORY

SELECT * FROM MSDB.DBO.DBA_DEFRAG_MAINTENANCE_HISTORY

END

— SELECT * FROM MASTER.DBO.DBA_DEFRAG_MAINTENANCE_HISTORY

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: