🚀 go-pugleaf

[posted and mailed]

louis nguyen (louisducnguyen@hotmail.com) writes:
> B) Reindexing.  On any given day maybe 5% (max) of new data is added
> to my DB.  But DBCC reindex has to work on all the indexes.  So DBCC
> reindex takes 3 hours a night.  Yuck.
> 
> Maybe there is a better way.  But I try to keep each database as small
> as possible.

Here is a script that I got from SQL Server MVP Andrew Kelly, which examines
which tables that are in actually in need of fragmentation. This could
reduce the reindex time a bit.



--  Gather Fragmentation Info

SET NOCOUNT ON
USE PRESENTS

DECLARE @TableName VARCHAR(100)

--  Create a table to hold the results of DBCC SHOWCONTIG
IF OBJECT_ID('Tempdb.dbo.#Contig') IS NOT NULL
    DROP TABLE #Contig

CREATE TABLE #Contig ([ObjectName] VARCHAR(100), [ObjectId] INT, [IndexName]
VARCHAR(200),
        [IndexId] INT, [Level] INT, [Pages] INT , [Rows] INT ,
[MinimumRecordSize] INT,
        [MaximumRecordSize] INT , [AverageRecordSize] INT,
[ForwardedRecords] INT  ,
        [Extents] INT, [ExtentSwitches] INT, [AverageFreeBytes] NUMERIC(6,2)
,
        [AveragePageDensity] NUMERIC(6,2), [ScanDensity] NUMERIC(6,2) ,
[BestCount] INT ,
        [ActualCount] INT , [LogicalFragmentation] NUMERIC(6,2) ,
[ExtentFragmentation] NUMERIC(6,2) )


DECLARE curTables CURSOR STATIC LOCAL
FOR
SELECT Table_Name
    FROM Information_Schema.Tables
        WHERE Table_Type = 'BASE TABLE'

OPEN curTables
FETCH NEXT FROM curTables INTO @TableName
SET @TableName = RTRIM(@TableName)

WHILE @@FETCH_STATUS = 0
BEGIN

    INSERT INTO #Contig EXEC('DBCC SHOWCONTIG(' + @TableName + ') WITH
TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')

    FETCH NEXT FROM curTables INTO @TableName
END

CLOSE curTables
DEALLOCATE curTables


--  Get a list of all the tables where the Logical Fragmentation of at least
--    one index is over 30% and is not a statistic, a heap or Text / Image.
DECLARE curFrag CURSOR STATIC LOCAL
FOR
   SELECT [ObjectName]
       FROM #Contig
           WHERE LogicalFragmentation >= 30 AND IndexID BETWEEN 1 AND 254
              AND INDEXPROPERTY (ObjectId, IndexName, 'IsStatistics') = 0
            GROUP BY [ObjectName]


OPEN curFrag
FETCH NEXT FROM curTables INTO @TableName
SET @TableName = RTRIM(@TableName)

WHILE @@FETCH_STATUS = 0
BEGIN

    DBCC DBREINDEX(@TableName)

    FETCH NEXT FROM curFrag INTO @TableName
END

CLOSE curFrag
DEALLOCATE curFrag



select * from #Contig



-- 
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp