Is this Maintenance plan ok?

  • Hello

    I have created some tasks for our SQL server, I wanted to check that I have them setup correctly. They run weekly over the weekend. I use SQL for Sharepoint and SAGE (Companies accounting software)

    Backups are not a problem

    Image is attached (I created it in SQL Management Studio as my SQL writing abilities are limited to SELECT!)

    Thank you

  • Do the update stats before the rebuild/reorg. Reason is that an index rebuild will update stats will full scan, so if you do a sampled update after that you end up with less accurate stats then if the update stats hadn't been there.

    With stats update before index rebuild, all stats get updated sampled and then those associated with indexes get an update with full scan. Best of both worlds.

    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 for that

    Callum

  • Your best bet is to use an existing index rebuild script that checks the fragmentation levels of the indexes and only rebuilds ones that are fragmented more than a specific threshold, then does a re-org on the others and updates the stats for the ones that are only re-orged. There are several of them available, I use the one written by Andrew Kelley and published in SQL Server magazine.

    You can also use sp_updatestats because in SQL 2005, they modified it to only update statistics that need to be updated which should ignore any that were just modified because of an index rebuild.

Viewing 4 posts - 1 through 3 (of 3 total)

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