rebuilding index not doing anything

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris - 29 Nov 2007 18:13 GMT

There are 25 indexes in my database which continue to report they are
severely fragmented even after I rebuild them. The operation completes
without error, so I don't understand why it is still fragmented. It does not
matter if I use REBUILD or REORGANIZE, the result is the same. Anyone know
how to fix this? I am using SQL Server 2005.

BTW I should mention that most indexes in the database will REBUILD and
report no fragmentation afterwards. Only 25 of them have this issue.
If I drop and recreate the index, obviously that would fix it, but I'd
rather not.

Chris

Reply to this Message

Kalen Delaney - 29 Nov 2007 18:41 GMT

Hi Chris

What type of fragmentation are you seeing? How many pages are in these
indexes? Small indexes cannot be totally defrag'ed, and the fragmentation
doesn't really matter.

REBUILD is exactly the same as drop and recreate so if REBUILD doesn't help,
drop and recreate won't either.

Can you show us the output from sys.dm_index_physical_stats for these
indexes?

Why is it so important that these indexes be defrag'ed? What operations are
being negatively impacted because of the fragmentation?

Signature

HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com

> There are 25 indexes in my database which continue to report they are
> severely fragmented even after I rebuild them. The operation completes
[quoted text clipped - 8 lines]
>
> Chris

Reply to this Message

Andrew J. Kelly - 29 Nov 2007 19:14 GMT

In addition to what Kalen stated if they are Heaps then rebuilding the
indexes will do nothing for the table itself. You need a clustered index for
that.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> There are 25 indexes in my database which continue to report they are
> severely fragmented even after I rebuild them. The operation completes
[quoted text clipped - 8 lines]
>
> Chris

Reply to this Message

Gail Erickson [MS] - 29 Nov 2007 21:48 GMT

To add to Kalen and Andrew's comments, you should check the size (number of
pages) in the 25 indexes. If the indexes are very small, they're stored in
mixed extents. Mixed extents are shared by up to 8 objects, so your ability
to completely remove fragmentation is somewhat limited in this case whether
you reorganize or rebuild.

You might want to review this whitepaper on fragmentation
(http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx).
It's written for SQL Server 2000 but is still valid for SQL Server 2005.
They recommend not worrying about fragmentation on indexes with fewer than
1000 pages because the workload performance gain isn't significant enough to
warrant it.

Signature

Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx

> There are 25 indexes in my database which continue to report they are
> severely fragmented even after I rebuild them. The operation completes
[quoted text clipped - 8 lines]
>
> Chris

Reply to this Message

TheSQLGuru - 29 Nov 2007 23:14 GMT

I think the most important thing not mentioned yet is how much free space to
you have in the database? If you are like every single client I have been
at the answer is essentially none. You are allowing autogrowth (possibly at
the default setting) to grow your database. If there isn't empty space in
the database, there is no contiguous blocks of disk space in which to
defragment anything. Double the size of the database and try again with the
rebuild. I think you will be pleased with the results.

Signature

Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.

> There are 25 indexes in my database which continue to report they are
> severely fragmented even after I rebuild them. The operation completes
[quoted text clipped - 8 lines]
>
> Chris

Reply to this Message

Andrew J. Kelly - 29 Nov 2007 23:51 GMT

This was part of his original post:

>BTW I should mention that most indexes in the database will REBUILD and
>report no fragmentation afterwards. Only 25 of them have this issue.

While it is absolutely true that you need plenty of free space in the data
files when rebuilding an index it is unlikely the cause here or the results
would not be so consistent. He also mentioned that a REORG has no effect
either. Since that works by swapping on a page by page basis the lack of
free space would not be be much of a factor.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

>I think the most important thing not mentioned yet is how much free space
>to you have in the database? If you are like every single client I have
[quoted text clipped - 16 lines]
>>
>> Chris

Reply to this Message

TheSQLGuru - 30 Nov 2007 01:14 GMT

But does a page by page swap not still leave fragmentation as long as only 1
index is being page swapped at a time? Or does a reorg allow for page moves
for multiple objects? Never really thought about it deeply enough.

For example, say there are index objects A, B and C. They are physically on
disk as follows (the number is the 'correct' Btree order of the index):

A2B1C1C2A3B2A1.

If index A is is currently being REORGd, how do the pages actually get
manipulated during the operation? Let us assume there is no other free
space in the database.

Does B1 get swapped for A1, leaving this: A2A1C1C2A3B2B1

And then A1 and A2 swap, leaving this: A1A2C1C2A3B2B1, etc, etc?

Signature

Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.

> This was part of his original post:
>
[quoted text clipped - 27 lines]
>>>
>>> Chris

Reply to this Message

Kalen Delaney - 30 Nov 2007 02:36 GMT

During reorg, pages are only swapped with other pages from the same index.

Signature

HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com

> But does a page by page swap not still leave fragmentation as long as only
> 1 index is being page swapped at a time? Or does a reorg allow for page
[quoted text clipped - 44 lines]
>>>>
>>>> Chris

Reply to this Message

TheSQLGuru - 30 Nov 2007 13:53 GMT

Which would leave fragmentation in place if there is no free space in the
file. Hmm, come to think of it, if page SWAPPING is all that can be done
then even huge amounts of free space won't do any good. It would need to be
able to move pages to empty space and lay them down contiguously to really
defrag the index, but if it can only swap pages that isn't good enough.
Curious.

Signature

Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.

> During reorg, pages are only swapped with other pages from the same index.
>
[quoted text clipped - 50 lines]
>>>>>
>>>>> Chris

Reply to this Message

Andrew J. Kelly - 30 Nov 2007 14:39 GMT

Defrag or Reorg (depending on the version) does not rebuild the pages and
extents like a Rebuild does. It simply swaps one page with another and this
can happen multiple times before it is done. None of this requires free
space (actually except for the very first first page I believe) to happen.
But since it does the reorg in two phases (compaction & defrag) it can
actually free up some pages if it compacts enough to do so. A reorg can can
remove virtually all of the Logical fragmentation but it not necessarily fix
extent fragmentation in which the next and previous extents are physically
contiguous.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Which would leave fragmentation in place if there is no free space in the
> file. Hmm, come to think of it, if page SWAPPING is all that can be done
[quoted text clipped - 59 lines]
>>>>>>
>>>>>> Chris

Reply to this Message