SQL Server Performance

DBCC DBREINDEX takes very long

Discussion in 'Performance Tuning for DBAs' started by AGONZALEZ, May 6, 2003.

  1. AGONZALEZ New Member

    SQL 7.0 SP3 I have table showing Reserved 28779360kb, Data 22489176, Index_size 6687656. I used to be able to reorg the an entire DB 110gb in one Maintenance Plan run doing all indexes in the db with original fill factor. Runtimes on this varying but usually finished within 48 hours. Since the Maintenance Plan did not finish after 50hours on the last run I changed to DBCC DBREINDEX on only the table in question and still did not finish after 50 hours.

    I am wondering if anyone knows what factors maybe affecting this.

    Any help is appreciated.

    Albert
  2. bradmcgehee New Member

    Disk I/O will be your biggest issue, and you may have reached the physical limit of your server. I am sure eventually it will finish, but it will take a long time, as you have seen. I think it is about time to check with your boss about moving up to new hardware.

    Another issue might be physical file fragmentation. You may want to defrag your files using a tool like Diskkeeper. If you do, the database must be turned off for the file to be defragged, which may or may not be practical for you.

    How often do you do this reorg? Just curious. Also, how fragmented does your tables become?

    To help reduce how often you have to do it, you might consider upping your current fillfactor, which will increase the amount of time between when you need to do a reorg.



    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  3. AGONZALEZ New Member

    I don't think fragmentation at the OS is an issue. There are 3 database files at the OS 2 of which are 58gb and one which is 50gb. Windows 2000 defrag does not indicate files are fragmented.

    I reorg quarterly. I am providing the SHOW CONTIG for this table.

    DBCC SHOWCONTIG scanning 'ZZRSLA' table...
    Table: 'ZZRSLA' (1041516331); index ID: 1, database ID: 5
    TABLE level scan performed.
    - Pages Scanned................................: 2778550
    - Extents Scanned..............................: 348544
    - Extent Switches..............................: 506011
    - Avg. Pages per Extent........................: 8.0
    - Scan Density [Best Count:Actual Count].......: 68.64% [347319:506012]
    - Logical Scan Fragmentation ..................: 34.99%
    - Extent Scan Fragmentation ...................: 57.61%
    - Avg. Bytes Free per Page.....................: 898.0
    - Avg. Page Density (full).....................: 88.91%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.


  4. Luis Martin Moderator

    Would you give information about your Hardware?.
    Please include RAID organization.

    Luis Martin
  5. bradmcgehee New Member

    I have to come back to my original thought, which is that you have exceeded what your hardware can do, and need to consider a faster I/O system, assuming you want to be able to perform reorgs in a timely manner.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  6. AGONZALEZ New Member

    Thanks for the response I gave incorrect info prior. The DB 152gb used space the table in question based on sp_spaceused is 28gb. It does not make sense that the entire db except for that large table gets reorged in 1 weekend but that one table cannot be reorged in the same time span.

    Albert
  7. bradmcgehee New Member

    So if I understand correctly, the table in question is only 28GB, and it takes over 50 hours just to reindex it? Yes, if this is the case, this does not sound correct? Do you ever run DBCC UPDATEUSAGE or run DBCC CHECKDB at all? If not, you might consider running both of these to see what effect, if any, they have on the length of time it takes to do your reindexes. Keep in mind that these commands also will take some time to run. Read up on them in the Books Online before you run them, assuming you are not familiar with them.

    If this doesn't fix your problem, then you may want to contact Microsoft Support for assistance.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  8. Chappy New Member

    Also do you have a decent amount of free disk space? If disk space is very tight, could this slow a defrag down?
  9. AGONZALEZ New Member

    To answer both replies. A DBCC CHECKDB is run every weekend (not run during the reorg of course) from the maintenance plan and "Include Indexes" is checked. This runs 17 hours. If DBCC CHECKDB is runs does not generate errors I should not have to run UPDATEUSAGE correct?

    Regarding unused space in the data files I have 23gb available with another 6 gb which the datafiles can extend themselves to.
  10. Chappy New Member

    I expect disk space is not the underlying problem then <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  11. bradmcgehee New Member

    Updateusage performs a different function thatn CHECKDB. It will update the system tables with the correct information on the actual sizes of your tables. Running it may not fix the problem, but I have seen cases where running it has fixed unusual problems, similar to yours. It can't hurt to run it, although it will take some time to run.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  12. AGONZALEZ New Member

    Very interesting before the UPDATEUSAGE:

    name rows reserved data index_size unused
    -------------------- ----------- ------------------ ------------------ ------------------ ------------------
    ZZRSLA 41867687 29207976 KB 22714280 KB 6891144 KB -397448 KB

    after the UPDATEUSAGE:

    name rows reserved data index_size unused
    -------------------- ----------- ------------------ ------------------ ------------------ ------------------
    ZZRSLA 41903362 44902576 KB 22738928 KB 22150624 KB 13024 KB

    Do you know how much space is necessary in the DB to DBREINDEX this table?

    Albert
  13. Luis Martin Moderator

    Are your disk in RAID 5?
  14. AGONZALEZ New Member

    Yes RAID 5.
  15. Luis Martin Moderator

    Ok.

    Supongo que, por tu apellido, puedes leer castellano. Si no es asi, lo escribo en ingles.

    En mi opinion el RAID 5 es, en gran medida, el causante del tiempo que toma reindexar esa o cualquiera otra tabla de gran tamano.

    Si pudieras cambiar o agregar discos con RAID 10 y pasar la tablas grandes a esos discos, puedo asegurar que el tiempo se reducira al menos a la tercera parte y, por otro lado, mejorara la performance general de todo el sistemas.

    Saludos,


    Luis Martin
  16. gaurav_bindlish New Member

    Luis, please transalate into english. I would like to see the solution suggested.

    Gaurav
  17. Luis Martin Moderator

    Apologies Gaurav.
    My english is very bad.

    In my opinion RAID 5 is the razon of the time in Agonzalez case.
    I suggest, if he can, to create RAID 10 and translate the table to this RAID. I think that will improve at list 3 times the amount in hours.

    Even more, the overall performance will improve to.

    Thank an sorry again.

    Luis Martin
  18. AGONZALEZ New Member

    Luis,

    Muchas gracias por responder a este problema. Lo siento si no escribo el espanol muy bien, vine de Cuba a la edad de 5. No es possible pasar la tabla a RAID 10. Aunque puedes tener rason con la possibilidad que sea RAID 5. Pienso que siempre no se explica com acaba 110gb (el resto del database) en 48hours per una tabla de 44gb no acaba en el mismo tiempo.

    Albert

    Gaurav,

    My response in english reiterates the concern that how can the 110gb database be reorged in 48hours but the 44gb table not finish in the same time span.

    Albert
  19. bradmcgehee New Member

    If you are rebuilding a table with a clustered index, it takes about 120% of the size of the largest table to perform the rebuild.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  20. AGONZALEZ New Member

    The table does have a clustered index. The fact that the table is 45gb (reserved) would mean I need 54gb available which I don't. I seems that is my problem. I will be able to archive data from this table in a few months. I think after the archive I will attempt the reorg. again. I do have the ability to add another database file which can grow to 99gb but I think I will wait till after the archive instead.

    Thanks to all contributors!

    Albert
  21. bradmcgehee New Member

    One little known performance issue is that if an NTFS disk gets more than 80% full, disk I/O performance will suffer. Ideally, at least 20% empty space should be available on all disk arrays.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com

Share This Page