December 22, 2008 at 6:04 pm
Now that I have been reviewing our logs for our weekly maintenance tasks we put in place on all our SQL Server 2005 servers, I have a few questions. On a weekly basis, we are running a maintenance plan that will perform the following tasks:
1. Check Database Integrity
2. Reorganize Index
3. Rebuild Index
4. Update Statistics
5. Cleanup history (logs older than 30 days)
I thought that I had read something in a SQL Magazine that stated that if you used the default maintenance plan wizard, SQL Server would create the logic around whether it is more optimal to reorganize the index or rebuild the index. Does anyone know if that is true? If so, do you know what the key is that triggers which action SQL will take (ie: fragmentation > 30% = rebuild, etc)?
If this is not the case and you need to add in the logic within the SSIS package, can someone give me some best practices for doing this manually?
Thanks!
Brian
December 22, 2008 at 7:33 pm
No, the maintenance plan plug-ins do not have any real logic behind them. The rebuild plug-in generates the code to rebuild every single index in the database(s) selected. The only logic available is what the fill factor will be, whether or not sort operations are done in tempdb and whether or not the online option will be used (note: online option is selectable, but will fail if you are not running Enterprise Edition).
You need to build the procedure to select which indexes should be selected. There is an example in BOL that you can get started with. You can find the example under the topic for the dynamic management view: sys.dm_db_index_physical_stats.
BTW - it does absolutely no good to reorganize the indexes and then rebuild as rebuilding does the same thing as reorganizing.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 22, 2008 at 9:10 pm
There are quite a few scripts on this site that will rebuild indexes based on fragmentation and fillfactor that you can use.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply