Answered by:
Index Fragmentation

Question
-
Hi All,
:: SQL Server 2008 ::
I am working on index fragmentation task.. And these are the observations::
1. Most of the indexes even the Avg fragmentation is more than 60% , the index rebuild is NOT reducing the fragmentation :(
2. I surprise to see, that before re-building the index the fragmentation was 14.286 .... And as part of the script, I rebuid (yes REBUILD) the index with ONLINE = OFF and it is increased to 42.857 .
3. Again I changed the REBUILD option ONLINE = ON, the fragmentation came down to 14
SO , after observing these, my idea on the index fragmentation is blurred ..
Please suggest ..
-- Reddy Balaji C.
Tuesday, August 3, 2010 10:22 AM
Answers
-
1. Probably because you have page count (of that tables) less than 1000
2. Looks odd, are you sure you did not run DBCC SHRINKFILE commad
3. Just did some testing and ONLINE --OFFLINE does not 'affect' fragmentation ..
See script to do rebuild/reorg indexes
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/- Proposed as answer by Tom Li - MSFT Thursday, August 5, 2010 9:33 AM
- Marked as answer by Reddy Balaji C Monday, August 9, 2010 5:12 AM
Tuesday, August 3, 2010 11:01 AMAnswerer -
Source BOL: In general, fragmentation on small indexes is often not controllable. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index.
Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!- Proposed as answer by Tom Li - MSFT Thursday, August 5, 2010 9:33 AM
- Marked as answer by Reddy Balaji C Monday, August 9, 2010 5:12 AM
Tuesday, August 3, 2010 11:39 AM
All replies
-
1. Probably because you have page count (of that tables) less than 1000
2. Looks odd, are you sure you did not run DBCC SHRINKFILE commad
3. Just did some testing and ONLINE --OFFLINE does not 'affect' fragmentation ..
See script to do rebuild/reorg indexes
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/- Proposed as answer by Tom Li - MSFT Thursday, August 5, 2010 9:33 AM
- Marked as answer by Reddy Balaji C Monday, August 9, 2010 5:12 AM
Tuesday, August 3, 2010 11:01 AMAnswerer -
Agree with Uri. I feel the table has very less rows.
Pradeep Adiga
My blog: http://www.sqldbadiaries.com- Proposed as answer by Tom Li - MSFT Thursday, August 5, 2010 9:33 AM
Tuesday, August 3, 2010 11:27 AM -
Hi Uri,
Thank you for the reply ..
1) Yes, the page count is less than 1000
2,3) Again I tried with sample database : Please find the below specified data::
Before DatabaseName TableName IndexName IndexType AvgPage
FragmentationPage
CountsAdventureWorks Employee PK_Employee_EmployeeID CLUSTERED INDEX 14.28571429 7 After executing "ALTER INDEX [PK_Employee_EmployeeID] ON [AdventureWorks].[HumanResources].[Employee] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = OFF)" DatabaseName TableName IndexName IndexType AvgPage
FragmentationPage
CountsAdventureWorks Employee PK_Employee_EmployeeID CLUSTERED INDEX 42.85714286 7 -- Reddy Balaji C.
Tuesday, August 3, 2010 11:28 AM -
Source BOL: In general, fragmentation on small indexes is often not controllable. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index.
Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!- Proposed as answer by Tom Li - MSFT Thursday, August 5, 2010 9:33 AM
- Marked as answer by Reddy Balaji C Monday, August 9, 2010 5:12 AM
Tuesday, August 3, 2010 11:39 AM -
You should ask yourself why you want to get rid of the fragmentation?If you have a big table, then - depending on your I/O subsystem - heavyfragmentation can reduce performance. Such reduced performance might bethe case if you run a query that has to return (or look up) a largepercentage of rows, and during that process, because of thefragmentation, the disk head has to move large distances back and forthto read each individual page.This fragmentation might be caused by fragmentation within the databasefile(s), or because the database files themselves are not contiguous onthe Windows file system. You can eliminate the first type offragmentation by rebuilding the clustered index of the table. The secondtype of fragmentation requires an SQL Server shutdown and adefragmentation at the Windows file system level.If your table does not have (at least) hundreds of pages thendefragmenting the table will not help performance. If you have noqueries that select large amounts of data from the table, thendefragmenting the table will not help performance.--Gert-JanTuesday, August 3, 2010 8:42 PM
-
Until your tables cross the 8-page bondary, data will reside in mixed extents containg data from potential other objects as well therfore your "bad" fragmentation.
After your table uses 8 pages and more space will come from dedicated extents and will not go back to use mixed extents.
But fragmentation on a table that consist of only 7 pages should not be a consern to you.
- Raoul
Tuesday, August 3, 2010 9:57 PM