I have a table with a clustered index, which shows ~70% fragmentation on a SQL Server 2008 database. I'm trying to rebuild this index:


The statement returns immediately with success, however the fragmentation is not changing. If I review the fragmentation in the SSMS, it remains the same.

I'm suspicious that it returns immediately, so it seems as if it's not doing anything. Since a clustered index determines the table layout on disk, I'd assume that this would have to be an offline task?

I guess I'm missing something and was hoping somebody could shed some light.

  • How big is the table? – gbn Sep 16 '11 at 15:14

How big is the table?

A small table that is only a few pages/extents rows will always show high fragmentation

  • This particular table is 199 rows. Data space: 0.023Mb, Index space: 0.016Mb. So yes, it's pretty small. So you are saying there is no way to improve that, and it doesn't matter? – Lucky Luke Sep 16 '11 at 15:52
  • It doesn't matter. This 5 8k database pages and fragmentation is measured over extents (simply) which is 8 x8 8k pages. Normally these tables should be filtered out. See dba.stackexchange.com/q/5365/630 – gbn Sep 16 '11 at 16:22
  • Thanks, this makes sense now. For some reason I thought that the tables were larger, but so far they are all small. – Lucky Luke Sep 17 '11 at 4:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.