July 22, 2014 at 3:22 am
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
Kwisatz78 (7/22/2014)
Hi thereI 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
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
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
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
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