Hello experts,
I have some databases in different servers and found the fragmentation numbers are over 90% on the indexes in some tables. Then, the job with master database stored procedure:
to "EXECUTE [dbo].[IndexOptimize] @Databases = '(my each database name)', @Indexes = '(my each index name)', @FragmentationLow =NULL, , @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE', @FragmentationHigh = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30 , @LogToTable = 'Y' "
was setup and runs successfully. This stored procedure works on some indexes, the fragmentation numbers were down. But, some indexes still have >90% fragmentation.
Why this stored procedure works on some indexes and some doesn't work? Is there other way to bring Fragmentation down?
For small tables (less than 500 pages) I've often seen fragmentation stay high, and for tables <100 pages it can remain about 90% not matter what you do, short of creating a new table and inserting into that. The advice from Microsoft and folks like Paul Randal is that fragmentation isn't worth worrying about for tables less than 1000 pages (8 MB).
If there are big tables that won't defragment via Ola's procedure you might want to try defragmenting them directly with ALTER INDEX.
Thank you robert_volk for the reply, I've checked tables and found that those tables with high fragmentation are all small tables. So, you're right and I'll keep tracking them.
The 1000 pages things is total nonsense (the guy himself even stated later that he just made it up to have some answer to give, and that you should not take it as a reliable guideline). Even 500 pages can be an issue if the table is read often enough, and particularly scanned a lot (which it will often be, given that relatively small a table).
A clustered index will defragment a table even if for very few rows in the table. So, just be sure any table > 64K and <= 1M has a clustered index on it; that is, it has an index 1 and no index 0.