I noticed that when I right click manually on an index and choose "Rebuild" or "Reorganize", its not giving any indication that its doing so, then I click on properties again for the same index, I see the fragmentation level is still at the same percentage even after refreshing!

Am I missing something?

I'm using the SQL Server 2008 R2 Standard edition

Thanks in advance

  • How many pages are there ? If the index is not having more than 1000 pages, it does nit really matter. – Kin Shah Jul 3 '14 at 20:03
  • @kin from the properties page for that particular index, it has 4 pages, Page fullness is 97% and the fragmentation is 75% .. so you mean if the pages count below 1000, I shouldn't worry about the rebuilding or reorganizing? I will check the link you provided thank you so much. – Shayma Ahmad Jul 3 '14 at 20:20
  • 1
    4 pages! Why are you bothering with a table that only has 4 pages? – Aaron Bertrand Jul 3 '14 at 21:25
  • 1
    Stop worrying about fragmentation for small tables. In fact, stop worrying about fragmentation for any table unless you know for sure that fragmentation is causing a performance issue. If your table is in memory (which should be the case for most or all tables if you're set up right), fragmentation doesn't matter. If you really, really, really care about fragmentation, buy a tool (like ours!) or get a free tool (like Ola's) and stop worrying about it. – Aaron Bertrand Jul 3 '14 at 22:18
  • 1
    Have you read Brent's post? – Aaron Bertrand Jul 3 '14 at 22:19