Create a Maintenance Plan is SQL 2005 standard edition

Hello,

I am trying to create a Maintenance Plan in SQL 2005; I need to do the following:

Rebuild index
Update statistics
Transactional log every 2 hours
Differential every 4 hours
Full every day

Which one should I do first Rebuild index or update statistics and how frequently should I performs those steps?

Thank you in advance.


hmraAsked:
Who is Participating?
 
Ady FootSharePoint ConsultantCommented:
Taken from an MVP post elsewhere answering the same question:

You should update the statistics to keep the performance well. The frequency 'depends' if many rows are regularly added or modified you need to do frequently. Else you can do that weekly at off production hours. Sql Server keeps tracks the last time the statistics were updated. You can refer this link for more information, http://weblogs.sqlteam.com/joew/archive/2007/08/31/60316.aspx 

Rebuilding indexes needs to performed if there is large fragmentation you can make use of sys.dm_db_index_physical_stats to identify the index fragmentation. You can perform it on a weekly basis as it will utilize most of the system resources and needs to be scheduled at off production hours. Refer this link where similar topic was discussed. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2744477&SiteID=1
0
 
Aaron ShiloChief Database ArchitectCommented:
hi

if you run rebuild index then you dont need to update statistics since the rebuild operation allready takes care of that.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.