I had a long night last night of watching Perfmon counters while I coaxed our data warehouse fact data into new files. I learned something through this little project, perhaps dumb and obvious, but important: don’t assume that your re-indexing work, even the biggest flavor, is automatically I/O limited.
Our systems are relatively small, but we do have a nice disk array. This server is just a 2 x 6-core machine with 64 GB of RAM and two HBAs, but it has a mix of SAN Flash and Raid 1+0 FC disks for the SQL Server files. The operation is one I have done a few times. Take the fact data, which is partitioned in to monthly files, and move it into new files, by mapping it into a new partition scheme. We do this about annually to optimize indexes and re-tier the storage.
Basically, I take all the existing tables and indexes, one at a time, and do
CREATE INDEX foo ON dbo.bar ( col1, col2, col3 )
WITH ( DROP_EXISTING = ON, <other options> )
ON aNewPartitionScheme ( col1 )
to move the data into new, pre-allocated files. The data involved is about 1 TB.
Eight Cores Seems like Enough? Wrong.
I thought, wrongly, that if I did this work with 8 cores in play, that the machine would be running it about as fast as it was capable of going, figuring I was sure to be I/O limited. My reasoning was that “create with drop existing” is a really simple pass-through operation, just reading the existing index, which is already in order, and writing it into new pages. It parallelizes very well* because of the arrangement of the data. How could eight cores be kept busy enough with that work to saturate the I/O subsystem?
Instead, the results surprised me: running with DOP = 8 I was getting, depending on the index, between 200 and 450 MB per second reads and writes to and from the disks. Changing the DOP to use all 12 cores on the machine, the throughput actually increased to 600 – 700 MB per second, making the maintenance significantly shorter. As it turned out, the work was CPU limited somehow, even with eight cores allowed. The eight cores would run at 100%, while the disk system didn’t.
For this case I had the luxury of being the only one on the machine during the maintenance window, which meant I could set MAXDOP to any value I wanted without side effects. I set that to 12 for the duration of the window, to use all cores, and then set it back again.
Your mileage may vary, but with this new world of storage I learned to test for optimal DOP when I have any large-scale index operations.
* on Enterprise Edition only, it’s worth noting.