August 21, 2010 at 11:54 pm
One of our DBA says that she does not do reorganizing and rebuilding of indexes on daily and monthly schedule througth maintainance plan, because it will grow the transaction log and will create blocking in the system. So she does maintance manually one by one indexes occasionally.
But is that true? If it is then cannot we just set up another job to backup and shrink the transaction log immdiately after the rebuilding process completes? And regarding to blocking, cann't we do at might night when noone is using system?
How do you guys do at your company?. I have database of about 300GB and there are about 2,000 tables.
Thanks
August 22, 2010 at 7:13 am
SqlServerLover (8/21/2010)
One of our DBA says that she does not do reorganizing and rebuilding of indexes on daily and monthly schedule througth maintainance plan, because it will grow the transaction log and will create blocking in the system. So she does maintance manually one by one indexes occasionally.But is that true? If it is then cannot we just set up another job to backup and shrink the transaction log immdiately after the rebuilding process completes? And regarding to blocking, cann't we do at might night when noone is using system?
How do you guys do at your company?. I have database of about 300GB and there are about 2,000 tables.
Thanks
It depends on your environment. Consider following things:
1. How busy is your system?
2. When is minimal activity on your system ? Schedule the maintenance jobs at that time.
Also, it is not necessary to rebuild all the indexes on all the tables, as it also depends on how fragmented your indexes are. Then you can decide based on your fragmentation level, whether to rebuilt or reorganize the indexes.
Look in scripts section on this site and you will find loads of scripts related to index fragmentation. Popluar one is from Ola (It has everything you need !)
HTH,
Cheers !
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
August 22, 2010 at 9:20 pm
Yes the rebuild will increase the transaction log. however, if you carefully do the index maintenence, the increase can be controlled. there is no need to do index maintenence on each index. research on sys.dm_db_index_physical_stats, which shows the fragmentation level of an index, and therefore appropriately take the action.
Doing one index daily can impact performance, depending upon the insertion / update rate of your database. if your database has large number of indexes, it will mean the index would be done once in a couple of months, which is not a good idea.
Yes index rebuild does block the index. however, if you have enough space in your disk, you can use the option of online=on, which can reduce the level of blocking.
hope this helps.
August 23, 2010 at 8:44 am
[highlight=#ffff11]It depends on your environment. Consider following things:
1. How busy is your system?
2. When is minimal activity on your system ? Schedule the maintenance jobs at that time.
Also, it is not necessary to rebuild all the indexes on all the tables, as it also depends on how fragmented your indexes are. Then you can decide based on your fragmentation level, whether to rebuilt or reorganize the indexes.
Look in scripts section on this site and you will find loads of scripts related to index fragmentation. Popluar one is from Ola (It has everything you need !)[/highlight]
--************************************************************************--
Thanks for you reply.
Right now total DB size is about 300 GB, but company is pretty new on that system and DB.
Based on the past history, our database growth in about 1 GB in a business day (Never more then 1 GB).
We won't have many activities after 10 pm.
I have one script to check fragmentation percentage, but i will check one with OLA. Thanks
August 23, 2010 at 9:10 am
I agree with the idea that you rebuild what needs to be rebuilt. If you do each one individually, it will need log space for that index, but then it will commit and if you have log backups, the backup will clear the log and the space is reused, minimizing log growth.
August 24, 2010 at 12:10 pm
I agree that you should only rebuild or reorganize indexes periodically. And only do the ones needed! I found some good scripts on this site to do just that. I like the option of doing them 'Online' because you are running Enterprise Ed. SQL Server!
August 24, 2010 at 3:26 pm
+1 for ola.hallengren.com!!!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 24, 2010 at 9:29 pm
I'm a fan of the index script from Michelle Ufford, aka SQLFool.
http://sqlfool.com/2010/04/index-defrag-script-v4-0/
Its well documented in the comments, and has a number of options for setting thresholds for when to rebuild, when to reorganize, and when to leave the index alone.
June 13, 2011 at 11:34 am
The SQLfool script is awesome, just used it for the first time on an inherited SQL 2008 ERP system this weekend and it worked great. No issues at all, I played around on a test database first to get the parameters worked out. Alot of options which is a big help!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply