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 = 
   @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.


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.

| improve this answer | |
  • I just want to identify which indexes that this excludes. Purpose of this I am using this SQL to generate a report to identify which indexes needs to rebuild/re-organize . I use this sql to generate a report gallery.technet.microsoft.com/scriptcenter/… . – LearningSQL Jun 3 '18 at 10:51
  • Run index optimize job from Ola then run the above sql to check whether those indexes are rebuild/reorganized. When i run this I am getting few indexes which suggest me to rebuild/re-organize. I am struggling to write some sql to identify these indexes so that i can exclude from my report or use any parameter in ola's job to rebuild/re-organize – LearningSQL Jun 3 '18 at 10:54

To get an fragmentation level matching Ola's scrips you need to use

sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') 

and filter on index_level=0

I find the general attitude that unexplained fragmented should be ignored until proven to cause catastrophic issues interesting. It explains some of the experiences we in the application team are having.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.