Maintenance task in SQL RRS feed

  • Question

  • I need to resize the 1 TB database with maintenance activity to improve the performance.Is it possible to do maintenance activity like rebuild index,checkdb for 1 TB database?

    Wednesday, April 27, 2011 7:10 AM


All replies

  • For index rebuilds on VLDB, use the -E switch, to allocate 4 pages rather than 1. Also use MAXDOP(1) This avoids fragmentation, but also helps with range scans.

    CHECKDB will be quite slow. Paul Randal did a good post about how to consistancy check VLDBs. See this post http://www.sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-Consistency-Checking-Options-for-a-VLDB.aspx

    Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/
    Wednesday, April 27, 2011 8:18 AM
  • Thanks peter,your suggestion is much helpful for me in my working environment

    Thursday, April 28, 2011 2:20 AM

    It would be better to DEFRAG the indexes rather than REBUILD the Index. Understand the differences

    REBUILD an index removes index interleaving, updates statistics, is heavily logged(if recovery mode is simple/bulk-logged logging is minimal), is an offline operation, consumes heavy memory, and is transaction bound. REBUILD drops the index and recreates it.

    While REORGANIZE (OR DEFRAG)is the exact opposite of REBUILD.

    Base the Index defragmentation on Logical Fragmentation rather than scan density or extent fragmentation. You can have defrag done indexes ahving logical fragmentation greater than 30%

    AFTER REORGANIZE update statistics is necessary.

    Break the number of tables in DB into buckets (say 7 each representing a weekday) and defrag

    say if you have 70 tables. Defrag ten tables per day.

    Further if you have SQL Server 2005/2008, defrag the latest partition only.

    All this will reduce the logging activity.


    Paul Randal's article, as suggested by Peter will work.

    Thursday, April 28, 2011 9:30 AM