Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jbates99
Constraint Violating Yak Guru
396 Posts |
 Posted - 2011-01-23 : 20:58:29
|
| Hello gurus,This question is for 2005.I have a 65 GB database on which I run a Db Maint Plan every Sunday.The plan does the following tasks and runs for a total of 5 hours.Rebuild Indexes for Tables and Views - 2 1/2 hours ** and changes free space to 10 %Update Statistics - full scan on all Tables and Views - 2 hoursDBCC UPDATEUSAGE - 30 minutesA couple of questions:1. Am I causing any performance issues by changing the free space to 10 %? (The database is growing fairly rapidly - table row counts have doubled over the past 14 months)2. Because Auto Update Statistics is ON, am I wasting time by running the Update Statistics task? I run it just to be certain that I have current stats on all tables/views.Thank you.John |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-01-24 : 16:06:32
|
| It has been stated that you no longer need to worry about running DBCC UPDATEUSAGE in 2005. However, they found that there were still issues and I haven't heard if those have been fixed yet. If you are on SP4 it might be fixed and you can avoid running it.I would recommend that you not rebuild all indexes, instead - download any one of the smart re-indexer utilities available and use that to only rebuild/reorganize indexes on those indexes that need to be touched. I believe Tara has one - and you can search for Ola Hallengren's utilites or SQL Fool's (Michelle Unsford) versions.By performing a full update statistics after rebuilding your indexes you are really just wasting system resources and time. An index rebuild updates the index statistics for the index being rebuilt with a full scan already, so doing it again is just a waste of time.If you use a smart reindexer, then you could use 'sp_updatestats' with the resample option to only update statistics on indexes and columns that need to be updated. The resample option will perform the scan based upon the last sampling rate used. In other words, if the last time the statistics were updated it was with a full scan - it will perform a full scan.Jeff |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jbates99
Constraint Violating Yak Guru
396 Posts |
Posted - 2011-01-24 : 23:13:17
|
| Thanks Jeff and Tara. this instance is 2005 SP3.sp_configure shows that the Fill Factor is 5 %. But the Rebuild Index task changes free space per page to 10 % - Tara, you *strongly* suggested that I remove the chnage free space piece. No it is not part of a shrink.Any advice on the Fill Factor?Thanks, John |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-01-25 : 13:25:36
|
| We only use 100% fill factor. We did extensive performance testing to see if we could reduce how fast fragmentation was occurring by reducing the fill factor. Not only were the gains not significant enough on the inserts, but the performance degradation on reads was extreme. Read performance is very important to our systems, so we could not use anything other than 100% fill factor. BOL seems to indicate that lowering it should be done in rare cases too, and I'd have to say I agree (GUIDs are one place perhaps to reduce it).Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
|
|
|
|
Need Opinions on This Maintenance Plan