Re: DBCC DBREINDEX\INDEXDEFRAG

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 06/08/04 

Date: Tue, 8 Jun 2004 09:52:02 +0200

Dan,

You were executing the wrong variable (@execstr), a variable you never assigned a value to. You should execute
the @CmdType variable instead. You can use below code, but you need to comment out my print and remove the
comment for your EXEC:

DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @IndexName VARCHAR (255)
DECLARE @maxfrag DECIMAL
DECLARE @CmdType VARCHAR (255)

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

-- 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) NULL,
   ObjectId INT NULL,
   IndexName CHAR (255) NULL,
   IndexId INT NULL,
   Lvl INT NULL,
   CountPages INT NULL,
   CountRows INT NULL,
   MinRecSize INT NULL,
   MaxRecSize INT NULL,
   AvgRecSize INT NULL,
   ForRecCount INT NULL,
   Extents INT NULL,
   ExtentSwitches INT NULL,
   AvgFreeBytes INT NULL,
   AvgPageDensity INT NULL,
   ScanDensity DECIMAL NULL,
   BestCount INT NULL,
   ActualCount INT NULL,
   LogicalFrag DECIMAL NULL,
   ExtentFrag DECIMAL NULL)

-- 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 #fraglist. ObjectName
   , #fraglist. ObjectId
   , #fraglist. IndexId
   , #fraglist. LogicalFrag
   , #fraglist. IndexName
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
   ORDER BY LogicalFrag desc, ObjectName asc

-- Open the cursor
OPEN indexes

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

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing Check on (0, ' + RTRIM(@tablename) + ', ' + RTRIM(@IndexName) + ') - fragmentation
currently ' + RTRIM(CONVERT(varchar(15),@frag)) + '%'
   if @frag > 75
     select @CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM(@tablename) + ',' + rtrim(@IndexName) + ', 0)'
   else
   if @frag between 5 AND 75
     SELECT @cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ', ' + RTRIM(@indexid) + ')'

-- EXEC (@cmdtype)
print @cmdtype
   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag, @IndexName
END

-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

-- Delete the temporary table
DROP TABLE #fraglist
GO 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <anonymous@discussions.microsoft.com> wrote in message news:1927901c44d03$1b2cad90$a601280a@phx.gbl...
>
> I modified the DBCC INDEXDEFRAG script from BOL with the
> DBCC DBREINDEX.  If the LogicalFragmentation is greater
> than 75 then DBCC REINDEX the index. If the
> LogicalFragmentation is between 5 and 75 then used the
> DBCC INDEXDEFRAG.
>
> I applied the script listed below to a database that has
> 1500 indexes that is heavy fragmented.  The script on
> found approximately 75 indexes to reindex or defrag.
>
> Please help me with the missing indexes that need to be
> reindexed or defragmented.
>
> Thanks,
>
> Dan
>
> DECLARE @tablename VARCHAR (128)
> DECLARE @execstr   VARCHAR (255)
> DECLARE @objectid  INT
> DECLARE @indexid   INT
> DECLARE @frag      DECIMAL
> DECLARE @IndexName VARCHAR (255)
> DECLARE @maxfrag   DECIMAL
> DECLARE @CmdType   VARCHAR (255)
>
> -- Decide on the maximum fragmentation to allow
> SELECT @maxfrag = 5
>
> -- 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) NULL,
>    ObjectId INT NULL,
>    IndexName CHAR (255) NULL,
>    IndexId INT NULL,
>    Lvl INT NULL,
>    CountPages INT NULL,
>    CountRows INT NULL,
>    MinRecSize INT NULL,
>    MaxRecSize INT NULL,
>    AvgRecSize INT NULL,
>    ForRecCount INT NULL,
>    Extents INT NULL,
>    ExtentSwitches INT NULL,
>    AvgFreeBytes INT NULL,
>    AvgPageDensity INT NULL,
>    ScanDensity DECIMAL NULL,
>    BestCount INT NULL,
>    ActualCount INT NULL,
>    LogicalFrag DECIMAL NULL,
>    ExtentFrag DECIMAL NULL)
>
> -- 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 #fraglist. ObjectName
>    , #fraglist. ObjectId
>    , #fraglist. IndexId
>    , #fraglist. LogicalFrag
>    , #fraglist. IndexName
>    FROM  #fraglist
>    WHERE LogicalFrag >= @maxfrag
>       AND INDEXPROPERTY (ObjectId,
> IndexName, 'IndexDepth') > 0
>    ORDER BY LogicalFrag desc, ObjectName asc
>
> -- Open the cursor
> OPEN indexes
>
> -- loop through the indexes
> FETCH NEXT
>    FROM indexes
>    INTO @tablename, @objectid, @indexid, @frag, @IndexName
>
> WHILE @@FETCH_STATUS = 0
> BEGIN
>    PRINT 'Executing Check on (0, ' + RTRIM(@tablename)
> + ', ' + RTRIM(@IndexName) + ') - fragmentation
> currently ' + RTRIM(CONVERT(varchar(15),@frag)) + '%'
>    if @frag > 75
>      select @CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
> (@tablename) + ',' + rtrim(@IndexName) + ', 0)'
>    else
>    if @frag between 5 AND 75
>      SELECT @cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
> (@objectid) + ', ' + RTRIM(@indexid) + ')'
>
>    EXEC (@execstr)
>
>    FETCH NEXT
>       FROM indexes
>       INTO @tablename, @objectid, @indexid, @frag,
> @IndexName
> END
>
> -- Close and deallocate the cursor
> CLOSE indexes
> DEALLOCATE indexes
>
> -- Delete the temporary table
> DROP TABLE #fraglist
> GO
>
>

Relevant Pages