Best Maintenance Plan

  • Hi,

    I am currently tasked with an incident to optimize the current MP that we are running on one of our PROD server. The size of the DB is 125GB and are running the MP on a daily basis. Following are the tasks with the MP.

    Steps Sequence-

    Check DB Integrity -> Shrink Database -> Rebild Index

    I am asked by the management to optimize the MP to achive better perfromance. Could you please let me know if the sequence that I am using has no issues? Also please advice if there are any steps that i am missing.

    Version : SQL Serve 2005

    OS: Windows 2003.

    Thanks,

    Nagarjun.

  • The sequence you're using definitely does have issues. Do Not shrink your DB on a regular basis. You're just forcing it to grow again, making the index rebuilds do huge amounts of works and spending a lot of time and resources for nothing.

    That said, performance is not usually improved by maintenance plans, it's improved by tuning the queries running on the server.

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    p.s. You do have a backup strategy, right?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Master for your reply.

    I would alter my MP to the following Sequence. Please let me know if this is Ok to go with.

    Check DB Integrity -> Rebild Index

    Thanks,

    Nagarjun.

  • That'll do. Again, it's not going to suddenly and magically make the server perform perfectly.

    You do have some backups running?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Not on a daily basis but during weekends. We dont have any Txn log bkps or Diffnt bkps running during business hours, so performance kill with backups is ruled out?

  • Backups are not about performance.

    Backups are about recoverability. If you have no diff or tran log backups running in business hours, then I assume that losing all data for a business day is acceptable to your users

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply