Please Review My Db Maint Plan 1

[JohnBates]

hi all,

This 2005 Standard Maint Plan performs these steps in this order:

1. Rebuild Index task - changes free space per page to 10 % and DOES NOT keep the index online while rebuilding

2. Reorganize Index task - all tables and views. Compact large objects

3. Update Statistics

4. Update Usage

Generally, the tables look pretty good, based on the DBCC SHOWCONTIG with all_indexes.

?? Should I be doing the Reorganize Index BEFORE Rebuild Index ?

?? I do see some high Extent Scan Fragmentation

Please advise.
Thank you.
John

 

[Mich]

I'm assuming these steps are performed consecutively.

I wouldn't do a rebuild and a reorg. Rebuilding drops and recreates the indexes. There is no need to reorg before or after the rebuild. If you've got a good bit of fragmentation (run the dm_db_index_physical_stats sp to check) run the rebuild, otherwise just reorg.

Running the update stats will automatically reset your stats to autoupdate. Be sure to use the NORECOMPUTE clause or just set your stats to autoupdate. Also, rebuilding an index causes the statistics to be updated.

You only need to update usage on SQL prior to 2005. I've only used it when upgrading. Do you run a lot of sp_spaceused?

My suggestion is to just run the rebuild as long as you have the ability to run it offline. You can run the rebuild online if the circumstances are right.

-If it ain't broke, break it and make it better.

 

[JohnBates]

Thanks, Mich.

I removed the reorg step today and ran it. this is the Std edition which does it while table/index is online.

I agree I'm doing some overkill by updating usage.

I'm finding lots of tables that have PKs that are 4 or 5 columns and clustered. These keys generally dont hold up well. We have some redesign work ahead I think.

thanks for the tip.

John