Data Compression and Index Rebuild

Question

Data Compression and Index Rebuild

brian118

Hall of Fame

Points: 3506
More actions
December 14, 2010 at 1:28 am

#232462

Hi
I have 'page' compressed an index on a 250 million row table and did observe better SELECT performance, however an index rebuild is taking almost twice the time to complete! Is this normal behavior, i.e. a side effect of data compression?
I know that this table is quite huge and maybe I should start considering partitioning but is there any way to speed up index rebuild without partitioning the table/indexes? I've tried adjusting the MAXDOP parameter and also the 'Index creation memory' and 'minimum memory per query' params but made almost no difference.
Regards
Brian
Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Nadrek SSC-Insane Points: 20039 More actions · Answer 1

Watch CPU core performance during the rebuild; on a 16 core physical box, using 2008 R2 Enterprise (i.e. parallel rebuild), and up to 500MB/s of sequential disk transfer rate (per Resource Monitor: Start->resmon) I found that an index rebuild was CPU-bound; it spiked all 16 cores at 100%.

This is the first time I've seen normal, reasonable (set based) operations be CPU-bound on a reasonably modern machine (4U rackmount server, in this case).

brian118 Hall of Fame Points: 3506 More actions · Answer 2

Nadrek (12/16/2010)
Watch CPU core performance during the rebuild; on a 16 core physical box, using 2008 R2 Enterprise (i.e. parallel rebuild), and up to 500MB/s of sequential disk transfer rate (per Resource Monitor: Start->resmon) I found that an index rebuild was CPU-bound; it spiked all 16 cores at 100%.
This is the first time I've seen normal, reasonable (set based) operations be CPU-bound on a reasonably modern machine (4U rackmount server, in this case).

Thanks for your feedback!

I have 8 core allocated to the SQL Instance, and although I specify MAXDOP=4 for the index rebuild operation, I'm seeing only ONE core spiking at 100%. However I did notice that my IO is slow, i.e. averaging <10Mb/Sec for both Read and Write Operation with a response time of ~ 38ms. This is extremely low compared to the figure which you provided! Can I therefore assume that since my IO is poor, the engine is not considering parallel operations?

Regards

Brian

Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -

Nadrek SSC-Insane Points: 20039 More actions · Answer 3

brian118 (12/17/2010)
Thanks for your feedback!
I have 8 core allocated to the SQL Instance, and although I specify MAXDOP=4 for the index rebuild operation, I'm seeing only ONE core spiking at 100%. However I did notice that my IO is slow, i.e. averaging <10Mb/Sec for both Read and Write Operation with a response time of ~ 38ms. This is extremely low compared to the figure which you provided! Can I therefore assume that since my IO is poor, the engine is not considering parallel operations?
Regards
Brian

My first guess: You have some serious fragmentation, at the filesystem (OS) and/or the SQL server level. Check on your index fragmentation first, and use something like Defraggler or your favorite defragging tool to check at an OS level.

My second guess: Your data and/or log files were full and are autogrowing at tiny little 1MB increments, which is causing my second guess.

My third guess: You have OS level file compression enabled for some or all of these files.

My fourth guess: Your storage is really slow. Perhaps you're on a SAN where the spindles are actually being shared at a level below the LUN level, and there's contention at the spindle level. Perhaps you've just got an old single disk, or perhaps your RAID is in a degraded state.

If you're only seeing one core spiking, I'd A) make sure your Processor Affinity is set to more than one real core, and B) see what happens when you set MAXDOP to 0, or don't set MAXDOP at all.

Also, try some smaller, newly rebuilt indexes as well.

brian118 Hall of Fame Points: 3506 More actions · Answer 4

Nadrek (12/17/2010)
brian118 (12/17/2010)
Thanks for your feedback!
I have 8 core allocated to the SQL Instance, and although I specify MAXDOP=4 for the index rebuild operation, I'm seeing only ONE core spiking at 100%. However I did notice that my IO is slow, i.e. averaging <10Mb/Sec for both Read and Write Operation with a response time of ~ 38ms. This is extremely low compared to the figure which you provided! Can I therefore assume that since my IO is poor, the engine is not considering parallel operations?
Regards
Brian
My first guess: You have some serious fragmentation, at the filesystem (OS) and/or the SQL server level. Check on your index fragmentation first, and use something like Defraggler or your favorite defragging tool to check at an OS level.
My second guess: Your data and/or log files were full and are autogrowing at tiny little 1MB increments, which is causing my second guess.
My third guess: You have OS level file compression enabled for some or all of these files.
My fourth guess: Your storage is really slow. Perhaps you're on a SAN where the spindles are actually being shared at a level below the LUN level, and there's contention at the spindle level. Perhaps you've just got an old single disk, or perhaps your RAID is in a degraded state.
If you're only seeing one core spiking, I'd A) make sure your Processor Affinity is set to more than one real core, and B) see what happens when you set MAXDOP to 0, or don't set MAXDOP at all.
Also, try some smaller, newly rebuilt indexes as well.

Yeap, the problem is the storage! In fact it's a SAN and I did confirm with the network admin that there are issues with it's performance. The drive is not compressed and the DB is set to autogrow in large increments...

The processor affinity is set to all processors and no there is no difference in setting the MAXDOP to either 0 or >0!

Thanks again for your help. Although I knew that a slow storage, i.e. slow IO does affect performance, I wasn't aware that it will also determine wether or not the engine will use multiple processors.... something which is obviously happening!

Brian

Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -

Nadrek SSC-Insane Points: 20039 More actions · Answer 5

brian118 (12/20/2010)
Yeap, the problem is the storage! In fact it's a SAN and I did confirm with the network admin that there are issues with it's performance. The drive is not compressed and the DB is set to autogrow in large increments...
The processor affinity is set to all processors and no there is no difference in setting the MAXDOP to either 0 or >0!
Thanks again for your help. Although I knew that a slow storage, i.e. slow IO does affect performance, I wasn't aware that it will also determine wether or not the engine will use multiple processors.... something which is obviously happening!
Brian

When you're done, please let us know the final tally of contributing factors/root causes, what you did to fix them, and the results afterward.