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.
ChrisHi 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?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Chris" <fake@.email.com> wrote in message
news:O0doxNrMIHA.2064@.TK2MSFTNGP06.phx.gbl...
> 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|||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.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris" <fake@.email.com> wrote in message
news:O0doxNrMIHA.2064@.TK2MSFTNGP06.phx.gbl...
> 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|||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.
--
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
"Chris" <fake@.email.com> wrote in message
news:O0doxNrMIHA.2064@.TK2MSFTNGP06.phx.gbl...
> 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|||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.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Chris" <fake@.email.com> wrote in message
news:O0doxNrMIHA.2064@.TK2MSFTNGP06.phx.gbl...
> 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|||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.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13kuhuki5tflk88@.corp.supernews.com...
>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.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Chris" <fake@.email.com> wrote in message
> news:O0doxNrMIHA.2064@.TK2MSFTNGP06.phx.gbl...
>> 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
>|||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?
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OyTW6KuMIHA.2208@.TK2MSFTNGP06.phx.gbl...
> 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.
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13kuhuki5tflk88@.corp.supernews.com...
>>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.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "Chris" <fake@.email.com> wrote in message
>> news:O0doxNrMIHA.2064@.TK2MSFTNGP06.phx.gbl...
>> 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
>>
>|||During reorg, pages are only swapped with other pages from the same index.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13kup01apko432d@.corp.supernews.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
> 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?
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OyTW6KuMIHA.2208@.TK2MSFTNGP06.phx.gbl...
>> 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.
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13kuhuki5tflk88@.corp.supernews.com...
>>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.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "Chris" <fake@.email.com> wrote in message
>> news:O0doxNrMIHA.2064@.TK2MSFTNGP06.phx.gbl...
>> 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
>>
>|||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.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OSG1InvMIHA.2308@.TK2MSFTNGP05.phx.gbl...
> During reorg, pages are only swapped with other pages from the same index.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13kup01apko432d@.corp.supernews.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 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?
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OyTW6KuMIHA.2208@.TK2MSFTNGP06.phx.gbl...
>> 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.
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13kuhuki5tflk88@.corp.supernews.com...
>>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.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "Chris" <fake@.email.com> wrote in message
>> news:O0doxNrMIHA.2064@.TK2MSFTNGP06.phx.gbl...
>> 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
>>
>>
>|||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.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13l05fijdqgpl71@.corp.supernews.com...
> 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.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OSG1InvMIHA.2308@.TK2MSFTNGP05.phx.gbl...
>> During reorg, pages are only swapped with other pages from the same
>> index.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13kup01apko432d@.corp.supernews.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 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?
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OyTW6KuMIHA.2208@.TK2MSFTNGP06.phx.gbl...
>> 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.
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13kuhuki5tflk88@.corp.supernews.com...
>>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.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "Chris" <fake@.email.com> wrote in message
>> news:O0doxNrMIHA.2064@.TK2MSFTNGP06.phx.gbl...
>> 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
>>
>>
>>
>
