SQL Server Performance

Anything else I should add to my Maint Plan?

Discussion in 'Performance Tuning for DBAs' started by Fubak, Jan 15, 2004.

  1. Fubak New Member

    I have 2 SQL 2000 Servers running Windows 2003 Server. Both are used heavily on a daily basis and have anywhere from 30-50 databases on them at any given time. Databases are added, deleted, shrink, grow, etc. all the time.

    I have 3 jobs running nightly...

    Integrity check (Using a maintenance plan)
    Run sp_updatestats on all user databases (manually created job)
    Shrink all user databases (manually created job)
    Is there anything else I should be doing? Should I be running steps 2 and 3 on all DBs rather than just the user DBs?

    Also, I would like to run defrag on a regular basis. Is there anyway to schedule a defrag in Windows 2003? If not, is there a free utility out there that will do it for me?
  2. Luis Martin Moderator

    Yes, sp_updatestats is ok, but statistics are updates 10% witch is default. Time to time I suggest full scan (100%) at least in critical tables.
    Other topic is defragmentation. I think you mean index defragmentation, in this case use DBREINDEX or INDEXDEFRAG also in non working time.

    In this Forum you can find some scripts to run defrag.



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  3. Fubak New Member

    Any reason why I shouldn't do a full scan every night?
  4. Luis Martin Moderator

    Full scan every night is the best choice.
    The problem is, full scan take long time, that's why I suggest once a week and default every day.




    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  5. Twan New Member

    If you are concerned about disk fragmentation then you could either take all databases off line periodically and defrag the disk in w2k3, or a better alternative might be to use a tool like diskkeeper which will defrag the disk on a continuous basis.<br /><br />NOTE that dbreindex followed by a shrink will undo all the benefit of the reindex... unless you set the shrink to not move any pages, in which case your database won't shrink. this is by design, so we're told <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Cheers<br />Twan
  6. Fubak New Member

    All the benefits of running an 'Update Statistics with Full Scan' will be gone after doing a Shrink DB?

    Do I need to run the Shrink DB first, then the Update Stats?
  7. Twan New Member

    Hi ya,

    no not update statistics, but if you do a dbcc dbreindex, then SQL will create all the indexes in the database by extending the database size and marking the pages of the old indexes as unused. If you then shrink the database then SQL will move pages from the end of the file back into the gaps free up by the reindex, which will end up fragmenting them again.

    Update statistics and shrink db won't interfere with each other

    Cheers
    Twan
  8. Fubak New Member

    Ok, thanks for clarifying that!
  9. satya Moderator

    But its better to keep those 2 processes seperately, its suggested to run any other until the SHRINK operation is finished.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. Fubak New Member

    quote:Originally posted by satya

    But its better to keep those 2 processes seperately, its suggested to run any other until the SHRINK operation is finished.

    What do you mean? Run the shrink first, then do the integrity check and update statistics?
  11. Luis Martin Moderator

    I gess Satya mean: does not important the order but, when you shrink do nothing else.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  12. satya Moderator

    Exactly, while doing any DBCC operation is better to keep away other process that may cause a table scan or resource usage.
    You can go with the current set of plan by running maint.plan first and then shrink job to take care of sizes.

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  13. Twan New Member

    You should not do a shrink after a dbcc dbreindex. The shrink will undo all of the benefit of the dbcc dbreindex

    Cheers
    Twan
  14. satya Moderator

    Twan

    At our side never had any trouble in following similar approach (reindex and then shrink) on 2 of our services.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  15. Luis Martin Moderator

    Twan

    That's new for me, would you explain why?



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  16. Twan New Member

    The way a dbreindex works is it will increase the size of the database to create the new indexes in the newly added contiguous free space. Then as it finishes building each index it marks the previous pages that the index was taken up as now being free. This makes the last part of database file nice and contiguous but the first part of the database file is now fragmented with information from the indexes not defragged and tables with no clustered indexes.

    If you now do a shrink asking SQL to move pages from the end of the file, then SQL will start with the last pages in the file and move them into the gaps in the early part of the file. This puts the indexes pretty much back where they were...

    The only way that it would be ok is if you have a clustered index on every table and you do a dbcc dbreindex on every table before you do the shrink, but even then you are liekly to reintroduce some fragmentation (or at least reversal of pages, since the shrink will just start at the end of the database and move pages into the earliest available slot in the file.

    Cheers
    Twan

Share This Page