Prev: Read Uncommitted Data
Next: Theoretical question about index fragmentation in filegroup partitions
From: new DBA in '09 on 22 Feb 2010 19:07
Hello,

Question: Why does index fragmentation for one nonclustered table
index vary so much by filegroup partition?

Background: We have several very large tables. One of those tables,
"SalesEntry," has a partitioned index. Each index partition is in a
separate filegroup, and each filegroup is on a separate hard drive.
(Forgive me for including the obvious; partitioned indexes are
somewhat new to me.) Results of sys.dm_db_index_physical_stats shows
me greatly varying fragmentation percentages for some of the index
partitions. Although most of them are similar at 99%, there are a few
with less than 50% fragmentation.

In theory, why would some of the other index partitions have far lower
fragmentation percentages?

Thanks,
Eric
From: Jay on 22 Feb 2010 20:08
Take a simple case:

partition 1 in fg1 stores all sales before 1/1/2010
partition 2 in fg2 stores all sales since 1/1/2010

You do a full index rebuild on 1/2/2010

You look at index fragmentation on 2/22/2010 after your big beginning of the
year sale and have processed all returns and exchanges. Then you marvel at
the fact that there is little to no fragmentation in partition 1 and lots of
fragmentation in partition 2.

Do I need to continue?

OK, just for completeness.

Index fragmentation was created in partition 2 because of the
insert/update/delete activity in partition 2; it is expected. Partition 1
had no changes and therefore has no fragmentation.

In this regard, each fragment is just like a normal table.

"new DBA in '09" <ericbragas(a)gmail.com> wrote in message
news:37668c9f-6eec-42c7-b160-5ddeff52b555(a)g23g2000vbl.googlegroups.com...
> Hello,
>
> Question: Why does index fragmentation for one nonclustered table
> index vary so much by filegroup partition?
>
> Background: We have several very large tables. One of those tables,
> "SalesEntry," has a partitioned index. Each index partition is in a
> separate filegroup, and each filegroup is on a separate hard drive.
> (Forgive me for including the obvious; partitioned indexes are
> somewhat new to me.) Results of sys.dm_db_index_physical_stats shows
> me greatly varying fragmentation percentages for some of the index
> partitions. Although most of them are similar at 99%, there are a few
> with less than 50% fragmentation.
>
> In theory, why would some of the other index partitions have far lower
> fragmentation percentages?
>
> Thanks,
> Eric


From: Gert-Jan Strik on 23 Feb 2010 14:45
new DBA in '09 wrote:
>
> Hello,
>
> Question: Why does index fragmentation for one nonclustered table
> index vary so much by filegroup partition?
>
> Background: We have several very large tables. One of those tables,
> "SalesEntry," has a partitioned index. Each index partition is in a
> separate filegroup, and each filegroup is on a separate hard drive.
> (Forgive me for including the obvious; partitioned indexes are
> somewhat new to me.) Results of sys.dm_db_index_physical_stats shows
> me greatly varying fragmentation percentages for some of the index
> partitions. Although most of them are similar at 99%, there are a few
> with less than 50% fragmentation.
>
> In theory, why would some of the other index partitions have far lower
> fragmentation percentages?
>
> Thanks,
> Eric

All fragmentation is the result of inserts and/or updates. Whenever an
update or insert is done that does not fit the page, a page split
occurs. If the page split is at the beginning or end of the clustered
index, there is a relatively low chance that this results in
fragmentation. If it occurs somewhere "in between" the chance is high
that this adds fragmentation.

So if the rows for a particular partition were inserted in the order of
the nonclustered index, then there might be very little or even no
fragmentation.

Of course, Jay also provided a possible explanation. If you ever rebuild
the index, and did not have a lot of changes on that partition since,
then the fragmentation would also be lower.

--
Gert-Jan
From: new DBA in '09 on 23 Feb 2010 15:16
Thanks, Jay, that's an excellent explanation.

Ironically, the partition fragmentation wasn't due to regular business
activity. Since I was ultimately unable to explain why the
fragmentation varied so much, it was explained: the fragmentation is
due to massive imports of data wasn't properly scrubbed, and so large
amounts of data had to be manually updated.

Thanks again,
-Eric
From: Jay on 23 Feb 2010 15:36
You're welcome.

"new DBA in '09" <ericbragas(a)gmail.com> wrote in message
news:14f9cc83-d854-4569-9a8c-df07eafb03a5(a)a1g2000vbl.googlegroups.com...
> Thanks, Jay, that's an excellent explanation.
>
> Ironically, the partition fragmentation wasn't due to regular business
> activity. Since I was ultimately unable to explain why the
> fragmentation varied so much, it was explained: the fragmentation is
> due to massive imports of data wasn't properly scrubbed, and so large
> amounts of data had to be manually updated.
>
> Thanks again,
> -Eric


 | 
Pages: 1
Prev: Read Uncommitted Data
Next: Theoretical question about index fragmentation in filegroup partitions