INDEX DEFRAGMENTATION SCRIPT for SQL server 2000

INDEX DEFRAGMENTATION SCRIPT SQL 2000

This blog has redirected. Please get my latest script here.

http://www.sqlserverblogforum.com/2011/03/index-defragmentation-script-for-sql-server-2000/

        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.

Use the DBCC SHOWCONTIG statement. It will display the fragmentation information.

Here I used 3 conditions:

1. Fragmentation >=30 AND PAGES>1000 then rebuild

2. Fragmentation between 15 to 29 AND PAGES>1000 then reorganize & update statistics

3. If the above conditions are false then update the statistics

Before you going to run the procedure create the tables provided for history propose.

Note :This Index Defragmentation script only works for SQL server 2000. You can also get defragmentation  script for SQL server 2005/2008 here.

USE MSDB;

go

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())

)

go

--Archive the data's in master DB

USE MASTER;

go

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())

)

go

USE msdb

go

CREATE  PROC [DBO].[indexdefragmentatiON] @p_dbname SYSNAME

/*

Summary:        Remove the Index Fragmentation to improve the query performance

Contact:           Muthukkumaran Kaliyamoorhty SQL DBA

Description:      This Sproc will take the fragmentation details and do three kinds of work.

                       1. Check the fragmentation greater than 30% and pages greater than 1000 then rebuild

                       2. Check the fragmentation between 15% to 29% and pages greater than 1000 then reorganize

                       3. Update the statistics the first two conditions is false

ChangeLog:

Date                             Coder                                                                           Description

2011-03-11                    Muthukkumaran Kaliyamoorhty                                      created

*************************All the SQL keywords should be written in upper case*************************

*/

AS

BEGIN

SET NOCOUNT ON

DECLARE

@db_name SYSNAME,

@tab_name SYSNAME,

@ind_name VARCHAR(500),

@schema_name SYSNAME,

@frag FLOAT,

@pages INT,

@min_id INT,

@max_id INT

SET @db_name=@p_dbname

--------------------------------------------------------------------------------------------------------------------------------------

--inserting the Fragmentation details

--------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE #tempfrag

(

   id INT identity,

   ObjectName char(255),

   ObjectId INT,

   IndexName varchar(1000),

   IndexId INT,

   Lvl INT,

   CountPages INT,

   CountRows INT,

   MinRecSize INT,

   MaxRecSize INT,

   AvgRecSize INT,

   ForRecCount INT,

   Extents INT,

   ExtentSwitches INT,

   AvgFreeBytes INT,

   AvgPageDensity INT,

   ScanDensity DECIMAL,

   BestCount INT,

   ActualCount INT,

   LogicalFrag DECIMAL,

   ExtentFrag DECIMAL);

INSERT INTO #tempfrag

EXEC ('use ['+@db_name+'];DBCC SHOWCONTIG WITH FAST, TABLERESULTS, ALL_INDEXES');

CREATE TABLE #tempschema

(

obj SYSNAME,

ind SYSNAME,

TABLE_schema SYSNAME,

frag FLOAT,

page INT

)

INSERT INTO #tempschema

EXEC('

SELECT

d.objectname,

d.indexname ,

i.TABLE_schema,

d.logicalfrag ,

d.countpages

FROM #tempfrag d JOIN ['+@db_name+'].INFORMATION_SCHEMA.TABLES i ON (d.OBJECTNAME=i.TABLE_NAME)

')

SELECT @min_id=MIN(ID)FROM #tempfrag

SELECT @max_id=MAX(ID)FROM #tempfrag

TRUNCATE TABLE msdb.DBO.dba_defrag_maintenance_history

WHILE (@min_id<=@max_id)

BEGIN

SELECT

@tab_name=d.objectname,

@ind_name=d.indexname ,

@schema_name=t.TABLE_schema,

@frag=d.logicalfrag ,

@pages=d.countpages

FROM #tempfrag d JOIN #tempschema t ON(d.objectname=t.obj)

WHERE id=@min_id

--------------------------------------------------------------------------------------------------------------------------------------

--Check the fragmentation greater than 30% and pages greater than 1000 then rebuild

--------------------------------------------------------------------------------------------------------------------------------------

IF (@ind_name IS NOT NULL )

BEGIN

IF (@frag>=30 AND @pages>1000)

BEGIN

EXEC ('USE ['+@db_name+'];SET QUOTED_IDENTIFIER OFF;DBCC DBREINDEX("['+@db_name+'].[DBO].['+@tab_name +']",['+@ind_name+'])')

INSERT INTO msdb.DBO.dba_defrag_maintenance_history

VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'REBUILD',GETDATE())

END

--------------------------------------------------------------------------------------------------------------------------------------

--Check the fragmentation between 15% to 29% and pages greater than 1000 then reorganize

--------------------------------------------------------------------------------------------------------------------------------------

ELSE IF((@frag BETWEEN 15 AND 29) AND @pages>1000 )

BEGIN

EXEC ('USE ['+@db_name+'];SET QUOTED_IDENTIFIER OFF;DBCC INDEXDEFRAG( ['+@db_name+'],['+@tab_name +'], ['+@ind_name+'] )')

EXEC ('USE ['+@db_name+'];SET QUOTED_IDENTIFIER OFF;UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+']' )

INSERT INTO msdb.DBO.dba_defrag_maintenance_history

VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'REORGANIZE & UPDATESTATS',GETDATE())

END

--------------------------------------------------------------------------------------------------------------------------------------

--Update the statistics if the first two conditions is false

--------------------------------------------------------------------------------------------------------------------------------------

ELSE

BEGIN

EXEC ('USE ['+@db_name+'];SET QUOTED_IDENTIFIER OFF;UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+']'  )

INSERT INTO msdb.DBO.dba_defrag_maintenance_history

VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'UPDATESTATS',GETDATE())

END

END

SET @min_id=@min_id+1

END

--------------------------------------------------------------------------------------------------------------------------------------

--Archive the fragmentation details for future reference

--------------------------------------------------------------------------------------------------------------------------------------

INSERT INTO master.DBO.dba_defrag_maintenance_history

SELECT * FROM msdb.DBO.dba_defrag_maintenance_history

END

INDEX DEFRAGMENTATION SCRIPT

INDEX DEFRAGMENTATION SCRIPT for 2008 / 2005

         I dedicate this article to my senior DBA “Roshan Joe Joseph” who has helped me to write a script.

I have posted the updated INDEX DEFRAGMENTATION script here, which is already published in sqlservercentral (SSC).

http://www.sqlservercentral.com/scripts/Automated+index+defragmentation+script/68802/

.

Script changes:

·         I used while loop instead of cursor.
·         I used rebuild index if the page level locking is disabled.

Msg 2552, Level 16, State 2, Line 1
the index "index name" (partition 1) on table "table name” cannot be reorganized because page level locking is disabled

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 used 4 conditions:

1. Fragmentation >=30 AND PAGES>1000 then rebuild
2. Fragmentation between 15 to 29 AND PAGES>1000 then reorganize & update statistics
3. If the page level locking is disabled then rebuild
4. If the above conditions are false then update the statistics

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

I'd recommended to read the Index architecture written by Gail Shaw's http://www.sqlservercentral.com/articles/Indexing/68439/

-- For SQL-2005/2008

USE MSDB;

go

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())

)

go

--Archive the data's in master DB

USE MASTER;

go

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())

)

go

USE msdb

go

CREATE PROC [dbo].[indexdefragmentation]@p_dbname SYSNAME

/*

Summary:     Remove the Index Fragmentation to improve the query performance

Contact:        Muthukkumaran Kaliyamoorhty SQL DBA

Description:   This Sproc will take the fragmentation details and do four kinds of work.

                    1. Check the fragmentation greater than 30% and pages greater than 1000 then rebuild

                    2. Check the fragmentation between 15% to 29% and pages greater than 1000 then reorganize

                    3. Check the fragmentation between 15% to 29% and pages greater than 1000 and page level lock disabled then rebuild

                   4. Update the statistics if the three conditions is false

ChangeLog:

Date              Coder                                                             Description

2011-11-23     Muthukkumaran Kaliyamoorhty                        created

*************************All the SQL keywords should be written in upper case*************************

*/

AS

BEGIN

SET NOCOUNT ON

DECLARE

@db_name SYSNAME,

@tab_name SYSNAME,

@ind_name VARCHAR(500),

@schema_name SYSNAME,

@frag FLOAT,

@pages INT,

@min_id INT,

@max_id INT

SET @db_name=@p_dbname

--------------------------------------------------------------------------------------------------------------------------------------

--inserting the Fragmentation details

--------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE #tempfrag

(

id INT IDENTITY,

table_name SYSNAME,

index_name VARCHAR(500),

frag FLOAT,

pages INT,

schema_name SYSNAME

)

EXEC ('USE ['+@db_name+'];

INSERT INTO #tempfrag (table_name,index_name,frag,pages,schema_name)

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'

)

SELECT @min_id=MIN(ID)FROM #tempfrag

SELECT @max_id=MAX(ID)FROM #tempfrag

TRUNCATE TABLE msdb.dbo.dba_defrag_maintenance_history

WHILE (@min_id<=@max_id)

BEGIN

SELECT

@tab_name=table_name,

@schema_name=schema_name,

@ind_name=index_name ,

@frag=frag ,

@pages=pages

FROM #tempfrag WHERE id = @min_id

--------------------------------------------------------------------------------------------------------------------------------------

--Check the fragmentation greater than 30% and pages greater than 1000 then rebuild

--------------------------------------------------------------------------------------------------------------------------------------

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

VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'REBUILD',GETDATE())

END

--------------------------------------------------------------------------------------------------------------------------------------

--Check the fragmentation between 15% to 29% and pages greater than 1000 then reorganize

--------------------------------------------------------------------------------------------------------------------------------------

ELSE IF((@frag BETWEEN 15 AND 29) AND @pages>1000 )

BEGIN

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

--------------------------------------------------------------------------------------------------------------------------------------

--Check the fragmentation between 15% to 29% and pages greater than 1000 and page level 

--lock disabled then rebuild

--------------------------------------------------------------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------------------------------------------------------------

--Update the statistics  for all indexes if the first three conditions is false

--------------------------------------------------------------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------------------------------------------------------------

--Update the statistics  for all tables if the first three conditions is false

--------------------------------------------------------------------------------------------------------------------------------------

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

SET @min_id=@min_id+1

END

DROP TABLE #tempfrag

INSERT INTO master.dbo.dba_defrag_maintenance_history

SELECT * FROM msdb.dbo.dba_defrag_maintenance_history

END