none
reindexing is taking lot of time.

    Question

  • Hi All,
    i am supporting a production sql server 2000.the database size is approx 75GB.
    I am executing a sp for reindexing the tables.But it is taking more than 6 hrs.
    when i am runing manually it is taking around 2hrs.
    Kindly give me any suggestion.
    i am posting that sp below for clear understanding of the problem.

    CREATE PROCEDURE sp_defragment_indexes @maxfrag DECIMAL

     

    AS

     

    /* T.Pullen

     

    This stored procedure checks index fragmentation in a database and defragments

    indexes whose scan densities fall below a specified threshold, @magfrag, which

    is passed to the SP.

     

    Must be run in the database to be defragmented.

     

    */

     

     

    -- Declare variables

     

    SET NOCOUNT ON

    DECLARE @tablename VARCHAR (128)

    DECLARE @execstr VARCHAR (255)

    DECLARE @objectid INT

    DECLARE @objectowner VARCHAR(255)

    DECLARE @indexid INT

    DECLARE @frag DECIMAL

    DECLARE @indexname CHAR(255)

    ---DECLARE @dbname sysname

    DECLARE @tableid INT

    DECLARE @tableidchar VARCHAR(255)

     

    --check this is being run in a user database

    /*SELECT @dbname = db_name()

    IF @dbname IN ('master', 'msdb', 'model', 'tempdb')

    BEGIN

    PRINT 'This procedure should not be run in system databases.'

    RETURN

    END*/

     

    --begin Stage 1: checking fragmentation

    -- Declare cursor

    DECLARE tables CURSOR FOR

    SELECT top 20 convert(varchar,id)

    FROM sysindexes

    WHERE indid = 1

    or indid = 0

    ORDER BY rows DESC

     

    -- Create the temporary table to hold fragmentation information

    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 running dbcc showcontig on each one

    FETCH NEXT

    FROM tables

    INTO @tableidchar

     

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- Do the showcontig of all indexes of the table

    INSERT INTO #fraglist

    EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')

    FETCH NEXT

    FROM tables

    INTO @tableidchar

    END

     

    -- Close and deallocate the cursor

    CLOSE tables

    DEALLOCATE tables

     

    -- Report the ouput of showcontig for results checking

    SELECT * FROM #fraglist

     

    -- Begin Stage 2: (defrag) declare cursor for list of indexes to be defragged

    DECLARE indexes CURSOR FOR

    SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity

    FROM #fraglist f

    JOIN sysobjects so ON f.ObjectId=so.id

    WHERE ScanDensity <= @maxfrag

    AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

     

    -- Write to output start time for information purposes

    SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())

     

    -- Open the cursor

    OPEN indexes

     

    -- Loop through the indexes

    FETCH NEXT

    FROM indexes

    INTO @tablename, @objectowner, @objectid, @indexname, @frag

     

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET QUOTED_IDENTIFIER ON

     

    SELECT @execstr = 'DBCC DBREINDEX('''  + RTRIM(@objectowner) + '.' + RTRIM(@tablename) + ''','''',90)'

    SELECT 'Now executing: '

    SELECT(@execstr)

    EXEC (@execstr)

     

    SET QUOTED_IDENTIFIER OFF

     

    FETCH NEXT

    FROM indexes

    INTO @tablename, @objectowner, @objectid, @indexname, @frag

    END

     

    -- Close and deallocate the cursor

    CLOSE indexes

    DEALLOCATE indexes

     

    -- Report on finish time for information purposes

    SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())

     

    -- Delete the temporary table

    DROP TABLE #fraglist

    GO

    Thanks, 

     

    Tuesday, August 25, 2009 6:16 PM

All replies

  • Just a thought:

    You could replace Cursors with a combination of Temp Tables and Delete SQL statements for iteration. Cursors might be slowing down the process...

    Tuesday, August 25, 2009 6:22 PM
  • Alternatively, You could run SQL Profiler and see which part of the code is the bottleneck.
    Tuesday, August 25, 2009 6:23 PM
  • Hi ...
    Sorry for delay reply as i was busy in solving this issue in my production environment.
    I  have use one  filter to fast the speed.
    I used scandensity<99
    due to this my execution time improves considerably....
    i observed that when we are doing reindexing whose scandensity is above 99 it is taking lot of time compare to whose scandensity falls below 99.
    Thanks to u all who shown interest in this thread.
    Kindly write me in case of any further issue related to this thread.
    Tuesday, September 01, 2009 8:51 AM