Answered by:
unable to de-fragment a table index

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
- Edited by Praveen Rayan D'sa Tuesday, January 7, 2014 4:35 AM
- Proposed as answer by Shanky_621MVP Tuesday, January 7, 2014 5:13 AM
- Marked as answer by Fanny Liu Thursday, January 16, 2014 4:37 AM
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
- Edited by Praveen Rayan D'sa Tuesday, January 7, 2014 4:35 AM
- Proposed as answer by Shanky_621MVP Tuesday, January 7, 2014 5:13 AM
- Marked as answer by Fanny Liu Thursday, January 16, 2014 4:37 AM
Tuesday, January 7, 2014 4:26 AM