SQL Server Performance

DB Reorg - how to determine when to run

Discussion in 'Performance Tuning for DBAs' started by dilan, Jul 23, 2007.

  1. dilan New Member

    Hi: We have several databases that have maintenance plans set to "Reorganize data and index pages (free space 10%)". This is schedules to run once a week. We are currently running a problem with our backups on the same day due to insufficient disk space which I believe is caused by either the locks placed by the maintenance plan or some other related reason.

    What are the best practices around running reorg jobs

    -entire database vs. single tables
    -regularly scheduled vs. when the conditions require it

    Dila n

  2. Luis Martin Moderator

    Most of the times reorganization cause database grows.
    If you don't have space, I prefer to run the job for the most important tables.

    Luis Martin

    All in Love is Fair
    Stevie Wonder

    All postings are provided “AS IS” with no warranties for accuracy.

  3. MohammedU New Member

    Instead of running against all tables you can run DBCC SHOWCONTIG to identify the fragmented table and then reindex only fragmented once...
    You can use the sample script from BOL to get the dbcc showcontig script to against all tables...

    Microsoft SQL Server MVP

    All postings are provided “AS IS” with no warranties for accuracy.
  4. Argyle New Member

  5. satya Moderator

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx by all means go through it to know the best practices.

    IMHO I would suggest to include heavily used tables rather than running the process on the whole database(s). Also you need to identify what is the rootcause of blocking here and simply backup process shouldn't block other usual process.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator

    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  6. dilan New Member

    Thank you for your suggestions. I found them very helpfull.

Share This Page