July 7, 2012 at 7:44 pm
We have a number od SQL Server databases that range from 1 MB to 100 GB. I want to add Index Rebuild to the weekly Maintenance Plans. When I tried to Rebuild one of the largest databases... it blew off the disk because it created a file larger than the original. Would like to knwo how others handle Index Rebuilds in Maintenance Plans. Thank you in advance.
Charlie
July 8, 2012 at 3:58 am
First of all, are you talking about the maintenance plans you build using SSMS using SSIS? I don't. I use custom scripts that berform the necessary index maintenance based on the fragmentation of the indexes. I programmatically decide if each of the individual indexes should be left alone, reorganized, or rebuilt.
There are several widely used scripts out there in the wild that can do this for you, I just chose to write my own.
July 10, 2012 at 12:57 am
Here is a good one that I use it on a lot of instances. It only rebuilds or reorgs indexes as needed, based on fragmentation levels you control. The defaults are a good starting point for most systems.
SQL Server Index and Statistics Maintenance by Ola Hallengren
I would recommend familiarizing yourself with the @LogToTable and @Execute parameters initially. Setup the log table and run the proc with @LogToTable = 'Y' and @Execute = 0 during off hours. Then look in the log table to see what the proc decided it thinks it should reorg or rebuild. Is your database in full recovery mode? I would recommend only rebuilding or reorging a few large indexes at a time to see what it does to your log and data file in terms of growth. If in full recovery only do a small amount in between log backups until you get on top of all the rebuilds/reorgs it wants to do.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 10, 2012 at 3:24 am
Recommendation by Microsoft.
1) Fragmentation less than 30%. Reorg.
2) Fragmentation greater than 30% Rebuild.
3) If the table has got less than 8 pages don't take any action. Actually query optimizer will read the entire table much faster than indexes.
There are hundreds of scripts in the market to handle this.
July 10, 2012 at 5:29 am
Thanks for the information Lynn.
July 10, 2012 at 5:31 am
opc.three,
Thank you for the information. Some of the databases are FULL, some are SIMPLE. I will read the information you provided and create a new plan.
Charlie
July 10, 2012 at 5:32 am
Baabhu... Thanks for the information.
Charlie
July 10, 2012 at 5:40 am
baabhu (7/10/2012)
Recommendation by Microsoft.1) Fragmentation less than 30%. Reorg.
2) Fragmentation greater than 30% Rebuild.
3) If the table has got less than 8 pages don't take any action. Actually query optimizer will read the entire table much faster than indexes.
There are hundreds of scripts in the market to handle this.
Text books may say that..but in practice that number is usually a lot bigger say 1000.
July 10, 2012 at 6:34 am
clayman (7/10/2012)
baabhu (7/10/2012)
Recommendation by Microsoft.1) Fragmentation less than 30%. Reorg.
2) Fragmentation greater than 30% Rebuild.
3) If the table has got less than 8 pages don't take any action. Actually query optimizer will read the entire table much faster than indexes.
There are hundreds of scripts in the market to handle this.
Text books may say that..but in practice that number is usually a lot bigger say 1000.
Ola's process uses 1000 pages as a threshold I believe.
By fault his process chooses to rebuild if frag level is >30%, and reorg if > 5% and < 30%.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 10, 2012 at 7:08 am
don't take these threshold values too literally, they can and will change for each and every index.
Ensure you target your indexes based on a wider view of the index states, this details the fragmentation levels that have been persisted since the dawn of time 😀
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 10, 2012 at 8:59 am
I agree 100%. Those numbers are nothing more than a fair starting point for most systems.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply