asked
Index Fragmentation on RAID 6 (DS)
Ok, I have a question, and I will try my best to ask it. It is fairly technical, so it could get messy quick. First, I have two SQL Servers. Both running SQL Server 2005 EE SP4. System "A" is 32-bit and System "B" is 64-bit. I copied a large database from system A to B, so that both SQL Servers have identical databases. I wanted to test index fragmentation (among other things) so I forced all indexes on the largest table in the database to be rebuilt using CREATE WITH DROP_EXISTING = ON. The next morning, I came in to check the health of the indexes, and was a bit surprised by what I found. The job itself ran quicker on the 64-bit machine (System B), which was no shocker, but the level of fragmentation through me for a loop. I ran the Physical Stats DMV with DETAIL to look at the leaf-level fragmentation. What I found was most of the indexes at the root level were at or close to 0%, however, the first leaf-level most times, was greater than I would like to see, being 50-70%, and in some cases 90% or higher. (On an index that was just rebuilt) I used the same code on System A, and leaf-level fragmentation was minimal there. Now, the underlying disk subsystem is RAID 6 (DP) on System B and RAID 10 on System A. My question is; would the underlying disk subsystem be responsible for leaf-level fragmentation in System B? Why else would two otherwise very similar systems be reporting such a vast difference in leaf-level fragmentation?
I would say no, it doesn't have anything to do with the RAID. What I would take a look at: - is the number of CPU cores and the parallellism settings. - the settings for ALLOW_PAGE_LOCKS settings When you run an index rebuild in parallell and don't have ALLOW_PAGE_LOCKS setting to on, you actually will increase the fragmentation. The raid level will only affect the physical fragmentation, not the logical.
Thank you for your reply. There are eight cores, and MAXDOP is 0. These were not parallel. I explicitly named each index, one at a time. The processing of each index rebuild may have employed parallelism, but the index rebuilds were sequential. The default for ALLOW_PAGE_LOCKS is on. I did not change that. The reason that I am leaning toward external fragmentation is because I performed the same EXACT operations on the same EXACT database and indexes, on a different server. The two main differences were that one was RAID 6 and 64-bit, and one was RAID 10 and 32-bit. I will be performing more testing today.
With maxdop=0 you will get multiple threads trying to rebuild one index at a time, but with multiple threads and allow page locks off you get more fragmentation. Do you have eight core on both servers? Maybe you'll get more fragmentation because the 64 bit environment can address more memory and executes more parallel threads
