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

     

    Tuesday, August 25, 2009 6:17 PM

All replies

  • There's an extra param you can add to dbreindex, called "sorted_data_reorg

    Ex:  dbcc dbreindex(TABLE_NAME, INDEX_NAME, FILL_FACTOR, sorted_data_reorg)

    dbcc dbreindex(myTableName, '', 90, sorted_data_reorg)
    Tuesday, August 25, 2009 6:19 PM
  • Hi thanks for your reply ....
    can you give some detail about this parameter..sorted_data_reorg
    Wednesday, August 26, 2009 7:16 PM
  • See, I will suggest you something here...
    I think Your indexes are getting fragmented very frequently causing this issue....

    Solutions :
    1) Try using DBCC INDEXDEFRAG insted of DBREINDEX
    2) Also find the fill factor for indexes and set perfect fill factor for tables.
    3) Analyze page splitting from Performance counters.

    Let me know if you hhave any further questions.

    Please Vote & "Mark As Answer" if this post is helpful to you.

    Cheers
    Bikash Dash
    MCDBA/MCITP

    Thursday, August 27, 2009 5:50 AM
  • I think the question is why this major difference when u run manual vs sp. Are you sure that the manaul script contains the same script what this sp dynamically generate? WHat you can do is, comment the EXEC (@execstr) line and you put a PRINT @execstr there. Run the SP. It will print all the command it generate to Message window. Take that printed script and match with your manual script.


    Madhu
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Thursday, August 27, 2009 8:26 AM
    Moderator
  • Hey Madhu,
    I think manual script will be same as SP...

    Only thing needed here to consider is Check page spliting and Fragmentation levels...


    Cheers
    Bikash dash
    Thursday, August 27, 2009 1:46 PM
  • Hey Madhu,
    I think manual script will be same as SP...

    Only thing needed here to consider is Check page spliting and Fragmentation levels...


    Cheers
    Bikash dash

    May be... But the question is, if the script is similar in both (sp and manual) then how will it have this  6 hrs vs 2 hrs to complete the same task? WHen the script is addressing same table / same index which will have same fragmentation in both case , how will it have this kind difference? not sure.


    Madhu
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Thursday, August 27, 2009 4:51 PM
    Moderator
  • Hi,

    I believe the difference could be caused by the time the script and sp were executed. I mean, imagine that he scheduled the SP to execute durring off-hours at the same time a backup was executing. In this situation, surely the SP will take longer to execute. I think we need more information to understand what happened but unfortunately, looks like ravirajaryan left the thread.

    Take Care!

    MarcosGalvani
    Thursday, August 27, 2009 5:02 PM
  • Hi,

    I believe the difference could be caused by the time the script and sp were executed. I mean, imagine that he scheduled the SP to execute durring off-hours at the same time a backup was executing. In this situation, surely the SP will take longer to execute. I think we need more information to understand what happened but infortunately, looks like ravirajaryan left the thread.

    Take Care!

    MarcosGalvani

    Agree  Marcos... very valid possible reason

    Madhu
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Thursday, August 27, 2009 5:06 PM
    Moderator
  • Hi ...
    Sorry for delay reply as i was busy in solving this issue in my production environment.
    both the script is same....
    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 1, 2009 8:48 AM
  • Hi ...
    Sorry for delay reply as i was busy in solving this issue in my production environment.
    both the script is same....
    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 1, 2009 8:48 AM
  • Hi ...
    Sorry for delay reply as i was busy in solving this issue in my production environment.
    both the script is same....
    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 1, 2009 8:48 AM
  • Hi Ravirajaryan,

    I think when the stored proc is being executed it is taking more time in generating possible execution plans and/or the optimizer is taking too long time in choosing the best plan out of them. I suggest it can be worked out by using plan guide and forcing the stored proc to use the plan guide. Run the query manually and try to save the execution plan in xml format. create a plan guide using the saved execution plan, now in the stored proc force it to use this plan guide for execution. The execution plan in the plan guide be updated regularly taking the fresh plan from the manually executed query, depending on the no. of changes(updates) made to the system tables sysindexes and sysobjects within a specific time span for example once or twice in a week.

    For further details about using plan guides check this link: http://msdn.microsoft.com/en-us/library/ms190788(SQL.90).aspx 

    Though this is kinda work around to the prblm, i would still be interested to know the reason behind the query taking 2hrs when executed directly and 6hrs when executed as a stored proc. Try taking a profiler trace for both and analyse if you could find some difference.

    Regards,
    Raja Rama Krishna Saradhi.
    Wednesday, October 14, 2009 11:01 PM