THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

Technical content about Microsoft data technologies. All opinions expressed are purely my own and do not reflect positions of my employer or associates.

Reindexing? Check your DOP.

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 ( 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.

Published Sunday, January 13, 2013 12:26 PM by merrillaldrich

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Ola Hallengren said:

You can set the max degree of parallelism in the ALTER INDEX command, so there is no need to change the global option.

January 14, 2013 4:43 PM

Ola Hallengren said:

It works also for the CREATE INDEX command.

January 14, 2013 4:53 PM

merrillaldrich said:

Yes indeed. I had the scripts all prepped in this case (plus exclusive access to the server) so it was easier, but putting it in the statements would be better in general. That was me being lazy I guess :-).

January 14, 2013 6:00 PM

Merrill Aldrich said:

As I talked about in my last post , I just went through a re-indexing project that took the partitioned

January 14, 2013 7:30 PM

Digital Dog said:

Yes, it's the indexing that can easily max out all CPUs at 100%. If you have Standard Edition, however, that's a different story, as you noticed, because index operations are all MAXDOP 1 (even in case of usual INSERTs, UPDATEs or DELETEs).

January 24, 2013 8:41 AM

Leave a Comment


This Blog


Privacy Statement