x

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?

more ▼

asked Oct 13, 2011 at 01:38 PM in Default

avatar image

the_SQL
112 1 3 6

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Oct 14, 2011 at 01:34 AM

avatar image

Håkan Winther
16.6k 37 46 58

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 14, 2011 at 07:38 AM

avatar image

the_SQL
112 1 3 6

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

Oct 14, 2011 at 08:53 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2031
x150
x28
x10

asked: Oct 13, 2011 at 01:38 PM

Seen: 3461 times

Last Updated: Oct 13, 2011 at 01:38 PM

Copyright 2017 Redgate Software. Privacy Policy