Please could you tell me the correct order of maintenance plan operations in SQL Server?
- Check integrity
- Update statistics
- Rebuild indexes
Is correct this order?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
There is no point in updating your statistics if you are rebuilding your indexes. The statistics for an index are updated if the index is rebuilt.
Another problem is that the rebuild index job in the Microsoft maintenance plans are not intelligent so they just rebuild all indexes whether they need it or not. Brent Ozar talks about this here http://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/
The best solution for index maintenance is to schedule a custom script that checks for things like fragmentation levels and then performs the appropriate action. The best script out there to do this can be found here http://ola.hallengren.com/
If you want to stick with the native maintenance plans then I would just drop the update statistics job.
As James says, step 2 is redundant. Rebuilding indexes will automatically update statistics. An explicit update of statistics will only sample a percentage of the rows in the table. Consequently the statistics will be inaccurate by a small amout. An index rebuild has to visit all the data in the table. The resulting statistics will be an absolutely accurate reflection of the table's content at that time.
The integrity check should come first. If the table's broken there's no point going any further after all.