SQL Server Performance

Maintenance plan for a client - why not reindexing?

Discussion in 'Performance Tuning for DBAs' started by eramgarden, Nov 14, 2007.

  1. eramgarden New Member

    we have a client who used the maintenace plan like this link:
    http://www.mssqltips.com/tip.asp?tip=1013
    They have "reorganize data and index pages" checked. Second radiobutton under it checked with value of 10. Also , "remove unused space" checked.
    I checked one of the main tables with dbcc showcontig and dentsity was 71%. Some other tables had density of 75% or less.
    They run this once a week. They also update statistics. Database is4 GB.
    If that maintenace plan reindexes, howcome density is still at 71%?
    Also, do they need recompile stored procs after they reindex?
  2. martins New Member

    When running DBCC Showcontig, you shouldn't really worry about the density. The important figures to look at is the Logical Scan Fragmentation and the Extent Scan Fragmentation. The density figures only tell you how full the pages are, but isn't an indication of fragmentation.
    The fragmentation figures should be low, and anything over 10% could impact performance negatively.
    Stored procs do not have to be recompiled after a reindex.
    Hope this helps.
  3. Luis Martin Moderator

    That plan is one of those to run every day.
    On weekends my recommendation is to run reindex and full update statistics.
  4. satya Moderator

Share This Page