|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
rebuilding index not doing anythingThere 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 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? Show quote "Chris" <f***@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 Show quote "Chris" <f***@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 Show quote "Chris" <f***@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  |
|||||||||||||||||||||||
Other interesting topics