question

Ashwan1234 avatar image
0 Votes"
Ashwan1234 asked DerrickGodefroijHiKOKI-9673 published

Index frgmentation still on high level with allengren scripts

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-general
capture.png (26.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered DerrickGodefroijHiKOKI-9673 published

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You will find these default parameters within the stored procedures. Ola hallengren skips tables by default that from memory have not changed by 10% rows or 1k/10k pages

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered TomPhillips-1744 commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Ola I got your point and I discussed with the same with microsoft. With this same condition Ms commented ,that lot more fragmentation and require to rebuild . So That is the reason I am look to change the script.
thanks

0 Votes 0 ·

Those indexes are too small to worry about the fragmentation.

0 Votes 0 ·