Index frgmentation still on high level with allengren scripts

Ashwan 521 Reputation points
2021-08-09T02:23:03.557+00:00

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
121504-capture.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,894 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 102.3K Reputation points
    2021-08-09T22:04:20.307+00:00

    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.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Cris Zhan-MSFT 6,611 Reputation points
    2021-08-09T06:23:42.497+00:00

    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

    1 person found this answer helpful.

  2. Olaf Helper 41,411 Reputation points
    2021-08-09T06:26:37.697+00:00

    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.