SQL Server Performance

CREATE INDEX WITH DROP EXISTING taking more time than DBCC DBREINDEX

Discussion in 'General DBA Questions' started by rajesh_kasturi, Nov 2, 2009.

  1. rajesh_kasturi New Member

    All
    I have a db with 450 GB in size, In this db I have 13 tables where application will perform more DML operations.
    SQL Server Version : SQL Server 2000 with SP4
    OS : Windows 2000 Advanced Server
    SAN Storage.

    Monthly twice I have rebuild index task on only these 13 tables, these individual table size vary from 25 MB to 150 GB.
    There was a job which Rebuild the tables by using the below script
    -----------------------------------------------------
    dbcc dbreindex(Table1,'',0,sorted_data_reorg)
    go
    sp_recompile tblremark
    .
    .
    .
    .
    dbcc dbreindex(Table1,'',0,sorted_data_reorg)
    go
    sp_recompile tblremark
    -----------------------------------------------------
    The above script was taking 10 to 12 hours
    After the reindex job ran we have the below issues.
    My database data files (.mdf and .ndf) files used to grow huge and our disks filled up and had to shrink the data files.

    This space issue come on alternative monday.
    When we use DBCC DBREINDEX, behind the screens SQL Server will create a new index and drops the old one that's why we need
    more space and data files will grow.
    To avoid data files growth while reindexing and shrinking I writen the script as follows
    Run DBCC SHOWCONTIG on 13 tables and get the results to temp table then
    If logical fragmentation is > 40 go for CREATE INDEX WITH DROP EXISTING.
    If logical fragmentation is between 20 to 40 go for DBCC INDEXDEFRAG
    After implementing new script as above, I am facing the below issues
    Data files did't grow that much but job has taken inacceptable time that is 35 hours to complete.
    DBCC INDEXDEFRAG has taken more time for huge table ( I know INDEXDEFRAG has will take more time for huge tables)
    Here I have the below questions
    1) Old script (that is DBCC DBREINDEX for all 13 tables) was able tocompelte in 12 hours and when I implement the new script with CREATEINDEX WITH DROP EXISTING has taken huge time, Why it is taking thatmuch time
    Note:I know DBCC INDEXDEFRAG will take more time compare toDBREINDEX but script in this case I found only one index to defrag thathas taken 10 hrs what about the rest 25 hours what might be the reasontt has taken that much time?
    2) Will CREATE INDEX WITH DROP EXISTING will take more time than DBCC DBREINDEX?
    3) Upto what level of logical fragmentation we can ignore, What isthe best values to go for DBCC DBREINDEX and DBCC INDEXDEFRAG.
    Example: After running DBCC SHOWCONTIG if the logical Fragmentation is<30 can we leave it like that or if the logical fragmentation is>40 we should go for drop and recreat like that.
    Pleaase share your ideas to reduce the job execution time and avoid data file growth and space issues.
    Thanks in advance.
  2. moh_hassan20 New Member

    rebuild index is IO performance issue , and let us add other alternatives to your suggestions:
    4) tune SAN for best IO performance
    review: http://sql-server-performance.com/Community/forums/p/31071/162169.aspx#162169
    5) upgrade O.S to windows 2003/2008
    6) upgrade sql server engine: 2005/2008

  3. rajesh_kasturi New Member

    Hi Hassan,
    Thanks for the reply I can do the point no 4 but I can't do 5 and 6 due to client budget issue.
    can you answer for the below question.
    3) Upto what level of logical fragmentation we can ignore, What isthe best values to go for DBCC DBREINDEX and DBCC INDEXDEFRAG.
    Example:After running DBCC SHOWCONTIG if the logical Fragmentation is<30 canwe leave it like that or if the logical fragmentation is>40 weshould go for drop and recreat like that.

  4. moh_hassan20 New Member

    [quote user="rajesh_kasturi"]Upto what level of logical fragmentation we can ignore[/quote]
    less 20 is reasonable value to ignore
    [quote user="rajesh_kasturi"]What isthe best values to go for DBCC DBREINDEX and DBCC INDEXDEFRAG.[/quote]
    only for index size > 1000 page :
    if logical fragementation <20 run INDEXDEFRAG (or ignore it) other wise run DBCC DBREINDEX
    • DBCC DBREINDEX offers more optimizations than individual DROP INDEX and CREATE INDEX statements, so don't CREATE INDEX WITH DROP EXISTING
    • select best fillfactor (50-80) for indexes based on insert/update size , don't use 0 value
    • Avoid shrinking database, which opposite the action of defragementation
    • Don't care for fragmentation of indexes with less than 1,000 pages




Share This Page