Sql-server – Index not re-organised

indexola-hallengrensql server

I am using Ola's INDEX Maintenance solution. Few indexes are not being re-organised after running job.Below is my code

    EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL,
    @FragmentationMedium = 
   'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
   @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
   @FragmentationLevel1 = 5,
   @FragmentationLevel2 = 30,
   @UpdateStatistics = 'ALL',
   @OnlyModifiedStatistics = 'Y',
   @LogToTable = 'Y',
   @PageCountLevel = 0

Few indexes which has fragmentation level 27 % still doesn't get re-organised even after running Index-optimize job.

I have went through frequently asked questions, it suggests about pagecountlevel which is already 0 in my case.

Also it has index_id =1 rather than 0.

Best Answer

My guess is that these indexes are very small.

Aaron Bertrand comments here

Ola's solution is set to ignore indexes with less than 1000 pages (see the @PageCountLevel parameter). You can override that so that it cares about indexes with fewer than 1000 pages, but why? Wasted effort IMHO.

I would stop worrying about small tables like this - let Ola's solution do its job, and worry about fragmentation when you can actually prove it is causing a tangible performance problem for a specific index. "Fragmentation is high" is not a problem on its own.

Additonally, Thomas Stringer comments here

If an index is very small (I believe less than 8 pages) it will use mixed extents. Therefore, it'll appear as if there is still fragmentation remaining, as the housing extent will contain pages from multiple indexes.

Because of this, and also the fact that in such a small index that fragmentation is typically negligable, you really should only be rebuilding indexes with a certain page threshold. It is best practices to rebuild fragmented indexes that are a minimum of 1000 pages.

Related Question