Index defrag scripts 2

[jeffmoore64]

Anyone have a scrip that will defrag all the indexes in a db?
tia

 

[wbodger]

Here is a sproc that I use:

CREATE PROCEDURE [dbo].[DefragDB] AS

-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr   VARCHAR (255)
DECLARE @objectid  INT
DECLARE @indexid   INT
DECLARE @frag      DECIMAL
DECLARE @maxfrag   DECIMAL

-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0

-- Declare cursor
DECLARE tables CURSOR FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'

-- Create the table
CREATE TABLE #fraglist (
   ObjectName CHAR (255),
   ObjectId INT,
   IndexName CHAR (255),
   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)

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
   FROM tables
   INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist 
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
   FETCH NEXT
      FROM tables
      INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- Open the cursor
OPEN indexes

-- loop through the indexes
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%'
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')'
   EXEC (@execstr)

   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table
DROP TABLE #fraglist
GO

 

[ptheriault]

I have this.
If you database is huge you should run it in small parts.

/*Perform a 'USE <database name>' to select the database
in which to run the script.*/
-- Declare variables.
SET NOCOUNT off
DECLARE @tablename varchar (128)
DECLARE @execstr       varchar (255)
DECLARE @objectid      int
DECLARE @indexid       int
DECLARE @frag            decimal
DECLARE @maxfrag     decimal
DECLARE @execstr2	varchar (255)

-- Decide on the maximum fragmentation to allow.
SELECT @maxfrag = 10.0 -- per BOL recommendation 10%
-- Declare cursor.


DECLARE tables CURSOR FOR
   SELECT TABLE_NAME 
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE' and TABLE_SCHEMA = 'dbo'
-- Create the table.




CREATE TABLE reindexlist (
   ObjectName char (255),
   ObjectId int,
   IndexName char (255),
   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)


-- Open the cursor.
OPEN tables
-- Loop through all the tables in the database.
FETCH NEXT
   FROM tables
   INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Run the DBCC SHOWCONTIG command to view
-- fragmentation information about all the table's indexes.
   
   INSERT INTO reindexlist
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
        with TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, FAST')
   FETCH NEXT
      FROM tables
      INTO @tablename
	PRINT 'Now running DBCC Showcontig against ' + RTRIM(@tablename) + '  Time: ' + convert(varchar(20), getdate())
END
-- Close and deallocate the cursor.
CLOSE tables
DEALLOCATE tables


-- Declare cursor for list of indexes to be defragmented.
DECLARE indexes CURSOR FOR
   SELECT '' + ObjectName + '', ObjectId, IndexId, LogicalFrag
   FROM reindexlist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor.
OPEN indexes
-- Loop through the indexes.
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag
WHILE @@FETCH_STATUS = 0
BEGIN

select @execstr = 'DBCC dbreindex (''' + RTRIM(@tablename) + ''','''',100' + ')'

 PRINT 'Executing ' + @execstr + ' - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%.  Started: ' + convert(varchar(20), getdate())
 
print @execstr   

--EXEC (@execstr)
--update reindexlist set LogicalFrag = 1 where ObjectId = @objectid and IndexId = @indexid


   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag
END
-- Close and deallocate the cursor.
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table.




--drop table reindexlist

/****************************************************************************
select * from reindexlist where LogicalFrag = 1

update reindexlist set LogicalFrag = 15 where CountPages > 25000
update reindexlist set LogicalFrag = 100 where ObjectName = 'PS_PO_ITM_STG_UD'  where CountPages < 25000



select * from reindexlist where LogicalFrag > 10 order by IndexId
select * from reindexlist where CountPages > 25000 order by CountPages desc
select count(*) from reindexlist where CountPages > 25000 
select count(*) from reindexlist where LogicalFrag <> 1
select * from reindexlist where IndexId = 1 order by LogicalFrag desc
select ObjectName, BestCount, ActualCount, LogicalFrag from reindexlist ORDER BY ObjectName

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

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[wbodger]

So, Paul, would there be any benefit to making this into a sproc and scheduling it to run perhaps weekly at some inane hour?

Willie

 

[ptheriault]

you would be better off creating a maint plan to re-index the database. What version of SQL is this for. The script I provided is for 2000.

I only use it for one time only re-indexing. I have weekly maint plans to re-index, check db and update stats.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[wbodger]

I'm running SQL Server 2000 on a high transaction database. This script looks like a combination of defrag and reindex, is this not a good way to defrag weekly?

Willie

 

[ptheriault]

Willie,
It is not a bad way. But a maint plan would be better if only from a logging perspective.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[wbodger]

So, with a maintenance plan you can handle the reindexing, but what about the fragmentation?

 

[ptheriault]

Re-indexing corrects fragmentation. infact defrag goes away in future versions of SQL Server.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[jeffmoore64]

Very nice Paul. So how often would you say I need to run this? I plan on sticking it into sproc and scheduling it to run on the weekends.

 

[jeffmoore64]

Future versions as in 2005?

 

[ptheriault]

It is still supported in 2005 but in BOL they state it will not be supported in future version. 2007 would be my guess.

I re-index weekly. That maybe overkill on some less transactional databases but I have the maint window so I take advantage of it.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[jeffmoore64]

I'm running that scrip on my test db now just to get some idea how long the initial run is going to take. I'm hoping I can make the initial run against all my db's on the weekends...