Indexes,  Scripts

INDEX DEFRAGMENTATION SCRIPT for SQL server 2000

 

INDEX DEFRAGMENTATION SCRIPT SQL 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 https://ola.hallengren.com/ – for SQL 2000 above.

/*
USE msdb;
go
CREATE TABLE [DBO].[dba_defrag_maintenance_current]
(
[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 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
*/

-- exec msdb.dbo.[indexdefragmentatiON] 'sanatest'
USE msdb
go

alter  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
2017-04-07                    Muthukkumaran Kaliyamoorhty                                      updated			Removed indexID Zero

*************************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),
@IndexId INT,
@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,
IndexId INT,
TABLE_schema SYSNAME,
frag FLOAT,
page INT
)

INSERT INTO #tempschema
EXEC('
SELECT
d.objectname,
d.indexname ,
d.IndexId,
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_current

WHILE (@min_id<=@max_id)
BEGIN

SELECT
@tab_name=d.objectname,
@ind_name=d.indexname ,
@IndexId =d.IndexId,
@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 AND @IndexId>0)
BEGIN

  IF (@frag>=30 AND @pages>1000 AND @IndexId>0)
  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 AND @IndexId>0)
  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+']('+@ind_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 for the index which are less than 15% & 1000 pages
--------------------------------------------------------------------------------------------------------------------------------------

  ELSE IF((@frag <15) AND @pages<1000 AND @IndexId>0 )
  BEGIN
  EXEC ('USE ['+@db_name+'];SET QUOTED_IDENTIFIER OFF;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,'Ind 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,'TBL UPDATESTATS',GETDATE())
  END 

-- IF end
END

SET @min_id=@min_id+1
-- Loop end
END

--------------------------------------------------------------------------------------------------------------------------------------
--Archive the fragmentation details for future reference
--------------------------------------------------------------------------------------------------------------------------------------

INSERT INTO msdb.DBO.dba_defrag_maintenance_history
SELECT * FROM msdb.DBO.dba_defrag_maintenance_current

-- Main end
END

 

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

21 Comments

  • Muthukkumaran Kaliyamoorthy

    hmmm.My dear

    I have to tell thanks to u ram because, i find issue while writing the script and learned that too.

  • Anonymous

    I searched in many sql server websites and google but the most of the sites have 2005 & 2008 defragmentation script.

    Finally i found your link in sql server central Thanks for your SQL server 2000 index defragmentation script.Its helped me.

  • Muthukkumaran

    Make another one loop or write another one procedure and run the procedure inside this.

    Post me your mail id I'll help you

    • GT

      Thank you for your good work.
      Can you please send me the modified script I can use on all the databases on an instance?

      Thanks in advance!
      GT

  • LONGOR

    Thankyou for sharing INDEX DEFRAGMENTATION SCRIPT for SQL server 2000 Sql server Blog Forum with us keep update bro love your article about INDEX DEFRAGMENTATION SCRIPT for SQL server 2000 Sql server Blog Forum .

  • rii

    Hi tried using this script and i’m getting the below error from one of my database.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    Server: Msg 1038, Level 15, State 3, Line 1
    Cannot use empty object or column names. Use a single space if necessary.
    Server: Msg 1038, Level 15, State 1, Line 1
    Cannot use empty object or column names. Use a single space if necessary.

    If i ran a dbcc reindex manually on that database it does not return any errors.
    Hope you can help me with this.

    Thanks

  • Terry

    I cannot work out how to use this script…
    I pick up that the first part creates tables, and the second part seemingly creates a procedure… but then what?
    1. Am I then supposed to run the procedure?
    If so, what is the syntax for this?
    2. Am I supposed to change anything to point to a specific database?
    If so where do I make this change
    If not does this automatically run against all databases?
    3. The notes suggest to remove the Index Fragmentation to improve…
    Where exactly do you do this?