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

Answer 1

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

Answer 2

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