May 26, 2006 at 5:04 am
Hello,
My weekly database optimisation consists of jobs created by the SQL Maintenance Plan Wizard. I have selected the "Reorganise data and index pages" option. Do I need to do anything else for performace maintenance i.e. DBCC INDEXDEFRAG / DBREINDEX or Update Statistics. Or can someone tell me if the maintenance planner incorporates everything I need to be doing.
Thanks in advance once again for all the help I have received from this forum.
Sam
May 29, 2006 at 8:00 am
This was removed by the editor as SPAM
May 30, 2006 at 4:13 am
The maintenance plan does the dbreindex and everything else , Auto-update statustics should be set on the database level settings , your can exeute sp_updatestats on the database , this will update your statistics.
DBCC INDEXDEFRAG - this is a online defrag which takes a long time and can be execute live but does not work so well with clustered indexes on non-clustered indexes
It is better to use DBREINDEX to which drops and recreates the index and get rid of fragmentation
to check for fragmentation execute dbcc showcontig (table) -- warning this will pull down production on a large table.
check for Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
and fragmentation
regards
May 30, 2006 at 9:32 am
Thanks for you reply Wayne,
If I have "Auto-Update Statistics" checked for my options, do I still need to run "sp_updatestats"? Would you know if I need to set my obects to recompile after an update stats command?
Sam
May 30, 2006 at 11:36 am
dbcc showstatistics will give you the needed stats information but I do not believe you need to manually run statistics
Mike
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply