Post reply

Index Maintenance and Update Stats

  • July 22, 2014 at 3:22 am

    #397092

    Hi there

    I am using Ola Hallengrens scripts for index and stats maintenance but I am wondering what most people to in terms of the maintenance schedules. At present we do an index rebuild\reorg weekly, but do people also do update stats nightly?

    I suppose there is an element of "it depends" here in that the data may be fairly static so the update stats may not be required, or if heavily updated then perhaps rebuilding indexes may be required more frequently, but any thoughts shared are welcome.

    Thanks

  • July 22, 2014 at 5:52 am

    #1731386

    Kwisatz78 (7/22/2014)

    Hi there

    I am using Ola Hallengrens scripts for index and stats maintenance but I am wondering what most people to in terms of the maintenance schedules. At present we do an index rebuild\reorg weekly, but do people also do update stats nightly?

    I suppose there is an element of "it depends" here in that the data may be fairly static so the update stats may not be required, or if heavily updated then perhaps rebuilding indexes may be required more frequently, but any thoughts shared are welcome.

    Thanks

    There isn't "an element of 'it depends'" here. It COMPLETELY depends in my opinion. I have some tables I do update stats on for clients as frequently as every hour. Others NEVER get them (because they are [mostly] static). I do believe that many tables in most systems can benefit from more frequent stats updates that once a week, unless or perhaps even if, you are using trace flag 2371.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • July 22, 2014 at 7:29 am

    #1731419

    Thanks, I wan't aware of that trace flag. Is there a good list of useful trace flags anywhere?

  • July 22, 2014 at 8:37 am

    #1731450

    Kwisatz78 (7/22/2014)

    Thanks, I wan't aware of that trace flag. Is there a good list of useful trace flags anywhere?

    I am sure there are lists. Whether they are good or not is up for debate. Whether you should implement any given trace flag is what matters, and that is a very NOT easy question to answer in most cases. 😎

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • July 23, 2014 at 10:15 am

    #1731830

    I hope this doesn't get viewed as a thread hijack..but TheSQLGuru.

    If you are updating stats more than once week, at what point do you update stats with FULLSCAN? or do you not bother with that?

  • July 25, 2014 at 3:21 am

    #1732294

    SQLSlammer-133701 (7/23/2014)

    I hope this doesn't get viewed as a thread hijack..but TheSQLGuru.

    If you are updating stats more than once week, at what point do you update stats with FULLSCAN? or do you not bother with that?

    I preferentially do FULLSCAN whenever the system allows it.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

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

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