We had deployed ola hallengren maintenance solution for one of the customers and scheduled all the jobs.
Customer mentioned to us that Index Optimize job ran fine, however he could still see that there were many indexes with fragmentations percentage around 90% and more. He even sent report for this as evidence.
Then we went into the call with customer and ran below script.
SELECT S.name as 'Schema Name',
T.name as 'Table Name',
I.name as 'Index Name',
IPF.avg_fragmentation_in_percent,
IPF.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS IPF
INNER JOIN sys.tables T on T.object_id = IPF.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = IPF.object_id
AND IPF.index_id = I.index_id
WHERE IPF.database_id = DB_ID()
and I.name is not null
AND IPF.avg_fragmentation_in_percent > 0
ORDER BY IPF.avg_fragmentation_in_percent descWe identified that there were indexes with high fragmentation but page count of all were below 1000.
We mentioned to customer that ola index solution maintains only those indexes where page count is more than 1000. This is based on Microsoft’s recommendation as well. Refer below official link:
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
MinNumberOfPages is the parameter in ola code which controls it. Although customer was not seeing any performance issue, he wanted us to set this value to 5 so that all the indexes were maintained.
We did it and customer was happy seeing results as per his expectations.