July 4, 2019 at 8:11 am
Hi all,
Usually, when doing large INSERT SELECTs in tables, I drop compression, all constraints and disable indices. After successfull loading, I compress the table and rebuild the indices BOTH with DATA_COMPRESSION = ROW/PAGE (whatever is more suitable).
What I have noticed is that a table rebuild (either it is clustered or heap), the indices, which have been disabled, get rebuilt with COMPRESSION NONE (which takes time). When applying a INDEX REBUILD with DATA_COMPRESSION, practically, I do the job twice, once uncompressed during table rebuild and second, during index rebuild.
I haven't found a solution, but is there a way to compress an existing index without rebuilding it? No changes in data happens so there is no risk in fragmentation of the data.
Otherwise, I just could drop the index completely and recreate it but I have read that this would take much longer.
What would you do?
Thanks,
Sven
July 4, 2019 at 3:36 pm
I don't understand why you drop compression before a load process - can you explain why that is necessary? I also do not see any advantage to removing constraints - unless your load process can/will be loading data into this table where the related data does not exist (yet). Then again - I am not sure why you would be performing large inserts to tables with lots of FK constraints anyways.
Because you are already going to rebuild after the load - then any fragmentation caused by compression will be corrected with the rebuild operation. The load performance will be minimally affected with compression - so I see no value in disabling it. BTW - I load a 6GB+ file to a compressed table and rebuild the indexes in less than 20 minutes.
Finally - you state that your are rebuilding the table...how exactly is that done? You should be rebuilding the indexes using ALTER INDEX ... REBUILD and if you are removing compression then your rebuild code should include the compression option for those tables that were previously enabled.
If the rebuild is ALTER TABLE ... REBUILD then that is only needed for HEAPS and should not enable any indexes that were previously disabled.
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
July 5, 2019 at 7:05 pm
I too would not drop compression to do a load, especially a large one. Page compression can help data load much faster by reducing I/O (compression takes more CPU but generally that is readily available while I/O is less so).
You also need to consider all the extra logging your current process is doing, especially if the underlying db is in FULL recovery model. Fully logging all of those index builds and rebuilds will be big overhead.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 10, 2019 at 10:31 am
Hi guys,
sorry for the late reply. I had email notification turned off and the post was out of my mind.Thanks for the input about not dropping compression. I thought that this would be a performance optimizer. As this caused the issue in the first place, my original question has been solved indirectly. As I do not do any rebuilds, the uncompressed index rebuild is not triggered. Thus, it's indeed faster than before.
Many thanks again!
July 10, 2019 at 1:13 pm
Hi guys, sorry for the late reply. I had email notification turned off and the post was out of my mind.Thanks for the input about not dropping compression. I thought that this would be a performance optimizer. As this caused the issue in the first place, my original question has been solved indirectly. As I do not do any rebuilds, the uncompressed index rebuild is not triggered. Thus, it's indeed faster than before. Many thanks again!
If you're still disabling constraints and indexes, then you'll need to rebuild the indexes to bring them back online.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply