0

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

Shayma Ahmad
  • 645
  • 7
  • 18
  • 27
  • How many pages are there ? If the index is not having more than 1000 pages, it does nit really matter. – Kin Shah Jul 03 '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 03 '14 at 20:20
  • Yes... In this case you do not worry about fragmentation. – Kin Shah Jul 03 '14 at 21:22
  • 1
    4 pages! Why are you bothering with a table that only has 4 pages? – Aaron Bertrand Jul 03 '14 at 21:25
  • @AaronBertrand I just wanted to see how fragmented all my database indexes are through a query I found online using the sys.dm_db_index_physical_stats, so I got different results that the first 10 indexes frag% are between 50-95%. Online sources talk about a good practice of rebuilding them if its more than 30% but I didn't know about the page counts!

    I tried to create a maint. plan for it and ran it through the SQL Agent Job, it was successful, but I see couple of indexes still have the same percentage of 75% but with very low page# of less than 8, while others are 0 frag, but 400 page

     – Shayma Ahmad Jul 03 '14 at 22:03
  • 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 03 '14 at 22:18
  • 1
    Have you read Brent's post? – Aaron Bertrand Jul 03 '14 at 22:19
  • @AaronBertrand very interesting article from Brent Ozar, thanks for sharing the tools too. – Shayma Ahmad Jul 03 '14 at 23:10

0 Answers0