Timeline for Dropping and recreating indexes for performance reasons?
Current License: CC BY-SA 3.0
16 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 21, 2012 at 10:02 | vote | accept | Shaul Behr | ||
| Mar 15, 2012 at 7:41 | answer | added | Shaul Behr | timeline score: 3 | |
| Mar 14, 2012 at 21:40 | comment | added | swasheck | @ConcernedOfTunbridgeWells Good point. | |
| Mar 14, 2012 at 20:12 | comment | added | ConcernedOfTunbridgeWells | @swasheck - a clustered index isn't so bad if the data is bulk loaded in the same order as the cluster key. However, you would have to either guarantee it comes out in the correct order or sort it before you load. | |
| Mar 14, 2012 at 19:27 | answer | added | mrdenny | timeline score: 3 | |
| Mar 14, 2012 at 14:44 | comment | added | swasheck | Especially if those indexes are clustered. Seek->Split->Insert->Repeat. Then you get fragmentation. | |
| Mar 14, 2012 at 14:08 | comment | added | ConcernedOfTunbridgeWells | @MartinSmith - a bulk load using bcp into a staging table took 8 minutes without indexes on the table and 30 minutes with indexes in place. Dropping the indexes and rebuilding them after the load took about 2-3 minutes. | |
| Mar 14, 2012 at 14:05 | comment | added | Martin Smith | @ConcernedOfTunbridgeWells - From 8 hours to 30 mins or from 30 mins to 8 mins? If the former how do you account for this? Were you getting a per row plan rather than a per index plan or effect of page splits, or logging, or something else (?) | |
| Mar 14, 2012 at 13:58 | comment | added | ConcernedOfTunbridgeWells | @MartinSmith - Index rebuilds can be pretty quick. Often it's quicker to drop and rebuild indexes on staging tables then to do a large bulk load with the indexes in situ. If you've got a small increment it's probably quicker to leave the index in place, but I've seen indexes take a bulk load from 8 to around 30 minutes, and the index rebuld just take a few minutes. It was quicker to drop and rebuild the indexes. | |
| Mar 14, 2012 at 12:10 | comment | added | Philᵀᴹ | @MartinSmith Agreed, doesn't seem right for such small amounts of data change/growth | |
| Mar 14, 2012 at 12:05 | comment | added | Shaul Behr | @MartinSmith - I think this was the original assumption... until we came across customers who work 24/7. And, as you say, this is where things kinda fall apart. | |
| Mar 14, 2012 at 12:01 | comment | added | Martin Smith |
Dropping and recreating the entire indexes for a growth of 0.1% seems like complete overkill to me unless this is running out of hours at a time when it doesn't really matter.
|
|
| Mar 14, 2012 at 11:55 | comment | added | Shaul Behr | @MartinSmith - depending on the installation, the archive table can be holding 10s or 100s of millions of records. The process adds 10s of thousands of records. Forgot to mention, our product is installed on multiple customer sites. Edition can vary from customer to customer - they choose which edition of SQL they want. Process is scheduled to run out of hours, but some customers work 24/7, so no such concept exists... | |
| Mar 14, 2012 at 11:44 | comment | added | Martin Smith | Dropping and recreating the whole indexes just because you are appending some additional records to the table doesn't seem a good strategy to me either. How large is the table? How many rows does the process add? What edition are you on? Is partitioning available (and if so is it suitable for your archiving pattern?). Is this process running out of hours or potentially whilst the live processes that include the archive table are running? | |
| Mar 14, 2012 at 11:44 | comment | added | Philᵀᴹ | Dropping & recreating indexes is standard practice before & after significant ETL load jobs. | |
| Mar 14, 2012 at 11:23 | history | asked | Shaul Behr | CC BY-SA 3.0 |