Post reply

Best Maintenance Plan

  • November 18, 2012 at 11:44 pm

    #285114

    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.

  • November 19, 2012 at 2:14 am

    #1560215

    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

  • November 19, 2012 at 4:35 am

    #1560276

    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.

  • November 19, 2012 at 4:38 am

    #1560280

    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

  • November 19, 2012 at 5:52 am

    #1560307

    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?

  • November 19, 2012 at 6:38 am

    #1560335

    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