February 19, 2009 at 12:41 pm
I have several tables in a database (not every table) that are reloaded nightly through an SSIS package. Obviously, this causes my non-clustered indexes to be continually fragmented on these tables.
Does anyone have recommendations or know what best practices are for this? Do I drop the indexes before the load and rebuild them or should I run my defrag job nightly when the load is complete. I think either would work, but I'm looking for the best solution. I'm a little concerned with the maintenance that would be involved in recreating the indexes (namely ensuring that any new indexes created are included in the rebuild).
Thanks for any advice,
Kim
February 19, 2009 at 1:58 pm
[font="Verdana"]We get this scenario in the staging tables of our data warehouse.
The solution we use is to drop all of the indexes except for the clustered index, do the insert, recreate the other indexes.
This increases the speed of the insert, and eliminates the necessity to defragment.
[/font]
February 19, 2009 at 2:36 pm
kim.talley (2/19/2009)
I have several tables in a database (not every table) that are reloaded nightly through an SSIS package. Obviously, this causes my non-clustered indexes to be continually fragmented on these tables.Does anyone have recommendations or know what best practices are for this? Do I drop the indexes before the load and rebuild them or should I run my defrag job nightly when the load is complete. I think either would work, but I'm looking for the best solution. I'm a little concerned with the maintenance that would be involved in recreating the indexes (namely ensuring that any new indexes created are included in the rebuild).
Thanks for any advice,
Kim
It depends on how what is the percentage of fragmentation you are experiencng. You can query the tables once the inserts have been done. If the fragmentation percent is less than 30% then you might think of just defragging the indexes, if it is more than 30% then you might want to build the indexes completely. You have to be careful with the log size when building the indexes. if it is an OLTP you might want to change the recovery model untill the rebuilding is done.
Would you be able to tell us the FILLFACTOR value you have set to? Just curious:P
Let us know
February 19, 2009 at 3:39 pm
The tables are 90% fragmented after the load. My fill factor is set to 80% - these tables are loaded fresh nightly but are updated/inserted/deleted from throughout the day as well.
February 19, 2009 at 5:33 pm
kim.talley (2/19/2009)
The tables are 90% fragmented after the load. My fill factor is set to 80% - these tables are loaded fresh nightly but are updated/inserted/deleted from throughout the day as well.
What is your tables size in which you have this fragmentation? Can you check and let us know please? because in smaller tables fragmentation is quite normal and its usually 75%.
Fill Factor- 50% for higher activity or lower for more- 70% for medium activity- - 100% for no activity at all.
February 20, 2009 at 7:14 am
These are not small tables - they have 500,000-1,000,000 rows on them so the fragmentation is a problem. If I manually run the defrag on the tables after the loads, the fragmentation percentages go down to almost nothing. The problem is that the next night, the tables are terribly fragmented again. The issue for me now is just deciding whether to defrag immediately after the load or to drop/recreate indexes as part of the load.
February 20, 2009 at 8:22 am
I'm with Bruce. We drop & recreate the indexes when there are massive inserts every night. It usually speeds up the process and you don't get fragmentation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 20, 2009 at 1:26 pm
kim.talley (2/20/2009)
These are not small tables - they have 500,000-1,000,000 rows on them so the fragmentation is a problem. If I manually run the defrag on the tables after the loads, the fragmentation percentages go down to almost nothing. The problem is that the next night, the tables are terribly fragmented again. The issue for me now is just deciding whether to defrag immediately after the load or to drop/recreate indexes as part of the load.
OK!, I am sure you would have analyzed the situation now.
Fragmentation percentage >80%, Large tables
You can set up a job to run everyday to drop the indexes and recreate them every night the insert process is done. Rebuilding indexes will not hep you much and Defragging doesnt at all.
Last thing you want to look at Fill Factor value as well may be? I am not so sure what would be the ideal percentage for your environment, I will red through some articles and will post if I find any good information to think about.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply