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.
Most of the indexes are just a clustered default index created on the primary key's, so those shouldn't be an issue. But I guess to your point of whether the data allows for defrag, I'm not sure on that one. Most of the indexes are on integer fields, there are very few non-clustered, user created indexes at this point covering other columns in the tables. I just started at this company and want to start tuning the databases and queries but I'm trying to get some baselines and I'd really like to start with consistency, including the indexes as optimized as possible.
Also, not sure if this is applicable. Here is the query I am running to check index stats. To this point, when I run the index rebuilds, will that be reflected right away, meaning I should be able to see the results when I run this query again, right? Nothing gets cached does it? SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind.name AS IndexName, indexstats.index_type_desc AS IndexType, indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 50 ORDER BY indexstats.avg_fragmentation_in_percent DESC
The indexes don't defrag because they're so small that SQL Server can't stack them into nice neat contiguous extents. It's just an artifact of their size. It's not a shortcoming in the product or even something wrong with your data. Rather than waste space getting all pages for small data stores together, SQL Server will mix up the tiny ones to save space. You're just in that situation here. If you look at Ola Hollengren's defrag scripts or Michelle Uffords, they filter out the smaller indexes for just this reason.
@Flathead_SQL -- A question, off topic! Why would you even worry about the fragmentation of a database that never gets bigger than 500 MB? It should always leave in memory anyway. Considering how fast the reads are in memory, fragmentation is not an issue. Just curious!
Denise, it's really just my inner OCD coming out. I like to think that everything is running as fast as possible and seeing indexes (even tiny ones) 90-99% fragmented, creates this illusion in my head that performance could be increased, but in reality I totally understand what Grant and Blackhawk have said. Makes total sense as to why SQL server wouldn't care about those or waste any time trying to rearrange space when it's not necessary.
16 People are following this question.
