Index frgmentation still on high level with allengren scripts

Question

hi I used to run https://ola.hallengren.com/ database maintenance task for index rebuild /reorg as follows
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE',
@FragmentationLevel1 = 3,
@FragmentationLevel2 = 20

This job is running good . However my database fragmentation still on high .

Any one can help why this script has an issue or the way we use is wrong ?

SELECT Indx.name AS Index_Name,
OBJECT_NAME(Indx.OBJECT_ID) AS Source_Table_Name,
Index_Stat.index_type_desc AS TypeOfIndex,
Index_Stat.avg_fragmentation_in_percent Index_Fragmentation_Percentage,page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) Index_Stat
INNER JOIN sys.indexes Indx ON Indx.object_id = Index_Stat.object_id
AND Indx.index_id = Index_Stat.index_id
ORDER BY Index_Fragmentation_Percentage DESC

Answer 1

In addition to other posts, note that the point with Ola's script is not that he rebuilds things in a different way. It is still the same command as if you would do to this manually. He just packages it for you, so that it is easier to administrate.

I would guess that in this case, by default he skips tables with fewer than 1000 pages, and I guess there is a parameter to control this.

I would say that today there are many workloads that can do well without any index rebuild at all (as long as you still update stats!). Fragmentation matters little SSDs, and fragmentation has never mattered much in well-designed OLTP databases. At least not what is known as logical fragmentation. Page density is a different matter, but it is also more expensive to measure.

Answer 2

Hi,

I am not familiar with the Ola’s scripts. But I saw that the Frequently Asked Questions document about this script mentioned these:

By default, IndexOptimize does not reorganize or rebuild indexes with less than 1000 pages. If you want to change this setting, you can use the @MinNumberOfPages parameter.

https://ola.hallengren.com/frequently-asked-questions.html

Answer 3

Please have a look at the "page_count" values. The highst is 340 pages, a page is 8 kb in size = 2,720 kb.
You can ignore the index fragmentation completely here and it's quite normal that such small indexes have high fragmention ratio.