SQL Server Performance

Index defrag seems to not work

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by johnlv, Apr 24, 2006.

  1. johnlv New Member

    As per BOL for 2005 i tried to defrag indexes by both methods

    1.reorganizing an index
    2.rebuilding an index

    but still some of my tables indexes are at the same avg_fragmentation_in_percent that they were before i did my defrag using the above 2 method.

    Any pointers ? thanks

    John

  2. Luis Martin Moderator

    Can you post DBCC SHOWCONTIG results?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  3. johnlv New Member

    well i used sys.dm_db_index_physical_stats instead


    database_id object_id indexe_id partition_number alloc_unit_type_desc index_depth index_level avg_frag_in_perfrg_cnt
    10118511CLUSTERED INDEXIN_ROW_DATA2058.333333333333381.512NULLNULLNULLNULLNULLNULLNULLNULL


    Let me know if you still want me to run DBCC SHOWCONTIG ?

    thanks
  4. satya Moderator

  5. johnlv New Member

    There are 3 indexes on the tables. they are

    nonclustered located on PRIMARY
    clustered, unique, primary key located on PRIMARY
    nonclustered located on PRIMARY

    And all of them have same avg_fragmentation_in_percent after defrag.

  6. Luis Martin Moderator

    Did you user DETAILED mode?

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  7. mmarovic Active Member

    Is there text or ntext column?
  8. johnlv New Member

    i used the LIMITED mode for sys.dm_db_index_physical_stats.


    there are no text or ntext column.
  9. Luis Martin Moderator

    The LIMITED mode is the fastest and scans the smallest number of pages. It scans all pages for a heap, but only the parent-level pages for an index, which are the pages above the leaf-level.

    The DETAILED mode scans all pages and returns all statistics.

    Try with DETAILED.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  10. jaybyoun New Member

    I'm having the same problem, dm_db_index_physical_stats is showing the same info after index rebuild/re-org, under sql 2005 sp1..
    Anyone has an answer ?
  11. Luis Martin Moderator

    Can you post DBCC showcontig, please.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  12. jaybyoun New Member

    thanks for the quick response, here're my findings.
    When page count is LESS than 8, there's no difference after rebuild/re-org. the more page count gives the better result after rebuild/re-org.. I also found a interesting table with two NC indexes (no CL) which has 60 pages of Heap w/ 78% frag and 18 pages of NC index w/38% frag and 38 pages of NC w/ 0% frag, this table refused to update the frag pct no matter what I did, and it shows the same results on dbcc showcontig as well..
    Here's my conculsion, in my smart rebuild/re-org scripts I decided to check the page count > 16 and frag pct > 30% and space used > 75% to pick up the tables to work on.
    Any more info will be appreciated.
  13. druer New Member

    Not sure if this helps or not, but I just found a situation on my system (sql 2000) where dbcc dbreindex was not working to clean up extent fragmentation, so perhaps the same could apply in your situation. My datafile was nearly 100% full when I saw no improvement. I increased the filesize so that new contiquous space was available and sure enough this time the rebuild removed the extent fragmentation. Not sure if the filesize itself could be an issue for you or not.

    Hope it helps,
    Dalton

    Blessings aren't so much a matter of recieving them as they are a matter of recognizing what you have received.
  14. Luis Martin Moderator

    Also be sure that index is not ID 0. This is table itself no index.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  15. OutOfTouch New Member

    When you say datafile do you mean the .mdf file? What was it set at orginally and what did you increase it too?
    Thanks!
  16. Ola Hallengren New Member

    Microsoft has as whitepaper on this.
    "Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages)."
    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
    Kalen Delaney has also written a blog post about this.
    http://sqlblog.com/blogs/kalen_delaney/archive/2008/02/28/fragmentation-revisited.aspx
    I think that you shouldn't rebuild / reorganize these small indexes. I have a stored procedure that you can use if you like. It does index rebuild online or offline, index reorganization, statistics update, index reorganization and statistics update or nothing based on fragmentation level, LOB existence and index size.
    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
    Ola Hallengren
    http://ola.hallengren.com

Share This Page