May 26, 2009 at 6:29 pm
Hi,
We have SQL Server 2005 EE x64 with SP3 on windows 2003 EE x64.
I have created the Maintenance paln for IntegrityCheck and Index Rebuild and the related jobs running weekly. I did not created Maintenance plan for UPDATESTATISTICS and I never run the updatestats manually too since 6 months. Its production server.
Could you please tell me Do I need to create a maintenance plan for Updatestatistics. If yes, how frequently I need to run the job.i.e daily/weekly?
please advice me
May 26, 2009 at 10:32 pm
Just to add , it will be with full scan i.e. 100% sampling ..:-)
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
May 27, 2009 at 1:08 am
Yes, you should add the update statistics job.
When you rebuild the indices the statistics on those indices will be updated, but not on the non-indexed fields. The statistics are also updated depending on the data changes within the table (if auto-update is on) but it's a fairly high percentage of writes that are required to trigger that.
How frequently you need to run it depends on the data profile, both what's in the table/index/field and what is being written to the table. If what's in the table/index/field is pretty random and so are the new writes then it doesn't seem to be as important, but if the writes are heavily skewed (eg. bulk loads of data with the same date but no time) then the statistics can become skewed as well.
The main effect of bad statistics is inefficient query plans and the first people to notice that are your end users, so I consider it essential to my continued well-being to run the update statistics regularly. In most cases weekly is enough, although I've had one system with badly skewed data where I had to run it for a couple of specific tables during the week as well.
As far as whether to do the full-scan or not, generally that wouldn't be necessary: the more random the data the smaller the proportion needed to be sampled to produce a representative population. In most cases the default sample size will suffice: again, it's only when I've had badly skewed data where I've needed to specify an increased sample size.
May 27, 2009 at 7:08 am
AShehzad (5/26/2009)
Statistics will be updated automatically after index rebuild. There is no requirement to separately configure this task.
Are you sure? 😉
But since you shouldn't automatically rebuild all indexes all the time, but instead should only rebuild indexes that have achieved a certain level of fragmentation, you'll still need to update the statistics on a regular basis because, despite automatic maintenance, they can become stale. Just make sure that you are careful when you schedule the statistics maintenance. Don't run update statistics after an index rebuild because as was noted above (several times), the rebuild does a full scan.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 27, 2009 at 9:11 am
Grant Fritchey (5/27/2009)
AShehzad (5/26/2009)
Statistics will be updated automatically after index rebuild. There is no requirement to separately configure this task.Are you sure? 😉
Yes, statistics are updated after rebuilding the index - on the statistics that support that index. If the index is reorganized, statistics are not updated. 😀
If you don't need to update your statistics with full scan - then you should use the procedure sp_updatestats which will only select statistics that need to be updated. However, this procedure uses the default sampling rate and may not be right for all tables.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 27, 2009 at 11:05 am
Jeffrey Williams (5/27/2009)
Yes, statistics are updated after rebuilding the index - on the statistics that support that index. If the index is reorganized, statistics are not updated. 😀If you don't need to update your statistics with full scan - then you should use the procedure sp_updatestats which will only select statistics that need to be updated. However, this procedure uses the default sampling rate and may not be right for all tables.
Sorry. That wasn't a real question. I know that they statistics are updated as part of a full rebuild. I was pointing out the fact that there were six or seven posts that all said the same thing. My humor never quite seems to fly.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 27, 2009 at 11:23 am
Is there an emoticon for sarcasm? looks like we really could use one. 😉
May 27, 2009 at 2:48 pm
Statistics will be updated automatically after index rebuild
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply