Reindexing all tables ... am I missing something (likely ...)

Question

Reindexing all tables ... am I missing something (likely ...)

Jim Holcomb

Right there with Babe

Points: 722
More actions
September 3, 2005 at 2:26 pm

#165841

I've been trying to figure out a strategy to reindex my databases and handle the large transaction logs that get created. I have some 50 databases and the transaction logs get as big as the database itself after reindexing (somewhat of a disk space issue). What I ended up doing is:
10:00pm - transaction log backups (last of my hourly backups)
10:15pm - set databases to simple recovery, reindex all tables in databases, set back to full recovery. (I stage this over several nights, 5-7 databases an evening).
Midnight - full backups
It's that "reindex all tables in databases" that is/was the problem. I found several scripts here and in other places that did that for me but I was trying to keep things simple. I ended up using xp_sqlmaint to do the reindexing and I'm wondering if I'm missing something - it seems too easy.
This is what I'm doing:
exec master..xp_sqlmaint '-S myservername -U "sa" -P "mypassword" -D mydatabase -RebldIdx 10'
How is this different / better / worse than the scripts?
Jim

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47794 More actions · Answer 1

The main thing I hate about sqlmaint is it's all or nothing approach.

Generally, what I would be doing is checking for fragmentation first using DBCC SHOWCONTIG. Then just reindex, or indexdefrag, just those tables that need it. Bit of a waste to reindex a table that doesn't need it. You won't get any real performance benefit out of it.

I also, wouldn't fiddle with the recovery model. Better off scheduling a DBCC SHRINKFILE after reindexing.

--------------------
Colt 45 - the original point and click interface

Jim Holcomb Right there with Babe Points: 722 More actions · Answer 2

Phill -

Thanks for the info, I'll work on writing something with a little less brute force.

Jim

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Use this:

DECLARE @TName sysname, @IndId int, @Query varchar(4000)

DECLARE index_cursor CURSOR

FOR SELECT sysobjects.name, sysindexes.indid FROM sysobjects

Inner join sysindexes on sysobjects.id = sysindexes.id

where OBJECTPROPERTY(sysobjects.id, N'IsUserTable') = 1

and sysindexes.indid between 1 and 250

order by sysobjects.name, sysindexes.indid

OPEN index_cursor

FETCH NEXT FROM index_cursor

Into @TName, @IndId

WHILE @@FETCH_STATUS = 0

BEGIN

SET @Query = 'DBCC INDEXDEFRAG (0, '+ @TName + ', '+ convert(varchar(20), @IndId) + ')'

PRINT (@Query)

EXECUTE (@Query)

FETCH NEXT FROM index_cursor

Into @TName, @IndId

END

Close index_cursor

DEALLOCATE index_cursor

_____________
Code for TallyGenerator

philcart SSC-Forever Points: 47794 More actions · Answer 4

But isn't this the same approach to what the maintenance plan would do? Only difference is that it runs DBCC INDEXDEFRAG instead of DBCC DBREINDEX.

Better option is to run DBCC SHOWCONTIG using the WITH TABLERESULTS option. This allows you to capture the output in a table for processing

 CREATE PROCEDURE usp_IndexDefrag 
    @DBase sysname
 , @MaxFrag decimal(9,2) = 30.00

AS

BEGIN
    SET NOCOUNT ON
    SET DATEFORMAT dmy
    
    -- Declare variables
    DECLARE @SQLStmt varchar (500)
    DECLARE @TblNm sysname
    DECLARE @ObjID int
    DECLARE @IdxID int
    DECLARE @intDbID int
    DECLARE @TblID int
    DECLARE @ErrNum int
    
    SET @ErrNum = 0
    
    -- Create temp table for output from DBCC SHOWCONTIG
    CREATE TABLE #Fraglist (
        ObjectName sysname NOT NULL
        , ObjectId int NOT NULL
        , IndexName sysname NOT NULL
        , IndexId int NOT NULL
        , Lvl int NOT NULL
        , CountPages int NOT NULL
        , CountRows int NOT NULL
        , MinRecSize int NOT NULL
        , MaxRecSize int NOT NULL
        , AvgRecSize int NOT NULL
        , ForRecCount int NOT NULL
        , Extents int NOT NULL
        , ExtentSwitches int NOT NULL
        , AvgFreeBytes int NOT NULL
        , AvgPageDensity int NOT NULL
        , ScanDensity decimal(9,4) NOT NULL
        , BestCount int NOT NULL
        , ActualCount int NOT NULL
        , LogicalFrag decimal(9,4) NOT NULL
        , ExtentFrag decimal(9,4) NOT NULL
    )
    
    -- list of tables for DBCC INDEXDEFRAG
    CREATE TABLE #TblList (
        TblID int IDENTITY(1,1)
        , TableName sysname
        , ObjectId int NOT NULL
        , IndexId int NOT NULL
    )
    
    -- build showcontig statement
    SET @SQLStmt = 'USE ' + @DBase + CHAR(13) + CHAR(10)
    SET @SQLStmt = @SQLStmt + 'DBCC SHOWCONTIG WITH TABLERESULTS'
    SET @SQLStmt = @SQLStmt + ', ALL_INDEXES, NO_INFOMSGS'
    
    -- execute statement storing resuilt in temp table
    INSERT intO #fraglist
    EXEC (@SQLStmt)
    
    -- Build SQL Statment to select all objects that have a Logical
    -- fragmentation level greater than maximum allowed 
    SET @SQLStmt = 'USE ' + @DBase + CHAR(13) + CHAR(10)
    SET @SQLStmt = @SQLStmt + 'SELECT Frag.ObjectName, Frag.ObjectId, Frag.IndexId '
    SET @SQLStmt = @SQLStmt + 'FROM #Fraglist Frag '
    SET @SQLStmt = @SQLStmt + 'INNER JOIN [' + @DBase + '].dbo.sysobjects sObj '
    SET @SQLStmt = @SQLStmt + 'ON Frag.ObjectID=sObj.[id] '
    SET @SQLStmt = @SQLStmt + 'WHERE Frag.LogicalFrag>=' + CAST(@MaxFrag as varchar(10))
    SET @SQLStmt = @SQLStmt + ' AND INDEXPROPERTY(Frag.ObjectId, Frag.IndexName, '
    SET @SQLStmt = @SQLStmt + '''IndexDepth'')>0 AND sObj.xtype = ''U'''
    
    -- clear temp table
    TRUNCATE TABLE #TblList
    
    -- get list of tables that require a defrag
    INSERT INTO #TblList ( TableName, ObjectId, IndexId )
    EXEC (@SQLStmt)
    
    SELECT @TblID = COUNT(ObjectID)
    FROM #TblList
    
    -- Get minimum table ID to prime WHILE loop
    SELECT @TblID = MIN(TblID) FROM #TblList
    
    -- loop through the list of tables
    WHILE @TblID IS NOT NULL
    BEGIN
        -- get IDs for SQL Statement
        SELECT @ObjID = ObjectID
         , @IdxID = indexid
        FROM #TblList
        WHERE TblID = @TblID
        
        -- build SQL Statment incorporating selected IDs  
        SET @SQLStmt = 'USE ' + @DBase + CHAR(13) + CHAR(10)
        SET @SQLStmt = @SQLStmt + 'DBCC INDEXDEFRAG (0, '
        SET @SQLStmt = @SQLStmt + RTRIM(@ObjID) + ', '
        SET @SQLStmt = @SQLStmt + RTRIM(@IdxID) + ') WITH NO_INFOMSGS'
        
        EXEC(@SQLStmt)
        
        -- Get next table ID for WHILE loop
        SELECT @TblID = MIN(TblID) FROM #TblList WHERE TblID > @TblID
        
    END
    
    -- Delete the temporary tables
    DROP TABLE #TblList
    DROP TABLE #fraglist
    
END

--------------------
Colt 45 - the original point and click interface