locked
Index Fragmentation RRS feed

  • 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

    http://ola.hallengren.com/


    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 AM
    Answerer
  • 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

    http://ola.hallengren.com/


    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 AM
    Answerer
  • 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
    Fragmentation
    Page
    Counts
    AdventureWorks 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
    Fragmentation
    Page
    Counts
    AdventureWorks 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 - heavy
    fragmentation can reduce performance. Such reduced performance might be
    the case if you run a query that has to return (or look up) a large
    percentage of rows, and during that process, because of the
    fragmentation, the disk head has to move large distances back and forth
    to read each individual page.
     
    This fragmentation might be caused by fragmentation within the database
    file(s), or because the database files themselves are not contiguous on
    the Windows file system. You can eliminate the first type of
    fragmentation by rebuilding the clustered index of the table. The second
    type of fragmentation requires an SQL Server shutdown and a
    defragmentation at the Windows file system level.
     
    If your table does not have (at least) hundreds of pages then
    defragmenting the table will not help performance. If you have no
    queries that select large amounts of data from the table, then
    defragmenting the table will not help performance.
    --
    Gert-Jan
     
    Tuesday, 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