asked
Index Rebuild Not Working?
Running SQL Server Standard (64-bit) and setting up some new maintenance jobs for various tasks, one of them being index optimization. My agent job shows as successful but when I check the fragmentation levels the next day after the job runs, my index fragmentation levels are still the same, most being 80% or higher fragmented. I've also tried running a rebuild on them via t-sql and I get zero errors but still the index fragmentation levels are high. Any idea what could be causing that or am I just missing something stupid? Note, I believe because I am running standard and not enterprise, I cannot rebuild online, so not sure if offline makes a difference. Really it shouldn't matter, it's just how sql locks the indexes and tables while rebuilding if I recall...But I have been wrong before!
Yes, tables are mostly 10MB or less, total database sizes never get above 500MB.
How big are the indexes getting rebuilt. If they are less than one extent in size (8 pages) you won't see any defragmentation at all and may even see fragmentation increase. If it's less than 3-4 extents, you may still not see any defragmentation. The standard recommendation is to only defrag on indexes greater than 1000 pages. I'd probably take that down to 100 pages in some cases, but not below that.
The other question is whether the data allows for defrag - there is a limit to what can be done if an index is very wide, only so many rows can fit on a page. It also depends on what kind of data is being discussed. And as Grant said, mixed extents complicate things even more.
No, it doesn't ignore them by default, but if you're only dealing with one or two extents, probably mixed extents too, you're not likely to see any defragmentation. It's OK.
Because most of the tables are small, the indexes are relatively small as well (<= 24 pages). But the fragmentation is extremely high and even after running index optimization, it doesn't change any of the indexes fragmentation. Does SQL server ignore by default any indexes with page sizes less than 1000?
So really, because the size of the indexes are so small, even though the fragmentation level is showing at 90% for example, it really doesn't matter because there isn't enough data for it to really make an impact?
If that's the case, it really makes sense but it still doesn't answer the original question of why won't the indexes rebuild and defrag, even if they are small? That concerns me more just due to the nature of the databases will eventually be growing. I really want to make sure there isn't another underlying issue that I'm missing.
16 People are following this question.
