locked
unable to de-fragment a table index RRS feed

  • Question

  • I have a table with a single PK index that shows a 75% fragmentation.

    I've tried rebuilding the index and I've tried reorganizing the index but neither changes the reported fragmentation for the table.

    I don't see any errors reported. I'm at a loss as to what I need to do to defragment this index.

    Any ideas what I'm doing wrong?

    BTW it's SQL 2008 R2 standard edition if that makes a difference.


    Developer Frog Haven Enterprises

    Monday, January 6, 2014 11:18 PM

Answers

  • Just out of curiosity, what is the page count for this index?  In my experience, if it's very small, it takes almost nothing for the index to show as being "heavily fragmented."  But, if it's really that small, I'm not sure it matters.  Others might disagree, but personally I don't bother to defrag indexes smaller than 1280 pages (~10MB).

    Jason

    • Proposed as answer by Shanky_621MVP Tuesday, January 7, 2014 5:12 AM
    • Marked as answer by Fanny Liu Thursday, January 16, 2014 4:37 AM
    Tuesday, January 7, 2014 12:17 AM
  • as Jason mentioned you should also consider index page count while defragmenting indexes, Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. you can refer below article for more info

    Microsoft SQL Server Index Defragmentation Best Practices

    you can also refer Ola hallengren's index maintenance scripts.

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page



    Tuesday, January 7, 2014 4:26 AM

All replies

  • Just out of curiosity, what is the page count for this index?  In my experience, if it's very small, it takes almost nothing for the index to show as being "heavily fragmented."  But, if it's really that small, I'm not sure it matters.  Others might disagree, but personally I don't bother to defrag indexes smaller than 1280 pages (~10MB).

    Jason

    • Proposed as answer by Shanky_621MVP Tuesday, January 7, 2014 5:12 AM
    • Marked as answer by Fanny Liu Thursday, January 16, 2014 4:37 AM
    Tuesday, January 7, 2014 12:17 AM
  • as Jason mentioned you should also consider index page count while defragmenting indexes, Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. you can refer below article for more info

    Microsoft SQL Server Index Defragmentation Best Practices

    you can also refer Ola hallengren's index maintenance scripts.

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page



    Tuesday, January 7, 2014 4:26 AM