Will changing INDEX_DEFRAG from script to stored procedure make things worse?

Question

Will changing INDEX_DEFRAG from script to stored procedure make things worse?

sql_er

SSCarpal Tunnel

Points: 4135
More actions
August 16, 2007 at 4:06 pm

#75172

Guys,
I've been running INDEX_DEFRAG as an SQL Script (as shown below) all the time before. Recently, I decided to turn it into a stored procedure, so that it looks more compact in a Job. However, I then remembered that one of the good things about INDEX_DEFRAG is that it processes table indexes one by one, and if it fails at some point, everything else up to that point WILL NOT be rolled back.
I was just wondering if the same property would hold if I turn this script into a stored procedure. Can anyone tell me if it would or would not and why?
Thanks a lot

/*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- 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 = 10.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
    ORDER BY IndexID

-- 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)

     WAITFOR DELAY '000:00:01'

     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

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

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

steve smith-401573 SSChampion Points: 10889 More actions · Answer 1

I believe that once an index is defragged, there is nothing to roll back. more critical, though, is that any subsequent index after the failed index will not be defragged. How will you defrag the remaining index(es)

sql_er SSCarpal Tunnel Points: 4135 More actions · Answer 2

Steve,

But wasn't it the case with the INDEX_DEFRAG script anyway (i.e. if it fails, what is not defragged is not defragged). In that case we just rerun the job manually.

Do you have better ideas?

Thank you!

Darren Weiner SSC Rookie Points: 44 More actions · Answer 3

First off, the nature of the indexdefrag is an index at a time...so it won't rollback anything but the one call you are making.

Although I'm guessing that, given the script you provided, the job will keep running through your temp table and the defrag on additional indexes will continue, Steve's point is basically getting to one of handling some errors/logging, which the current script doesn't seem to...try the new Try..Catch feature, or just capture @@Error, and log to a table...

sql_er SSCarpal Tunnel Points: 4135 More actions · Answer 4

From my experiment it looks like having a stored procedure will work just as the script.

Thanks a lot!