0

I'm using Hallengreen's Index maintenance To reorganize/rebuild indexes on my databases.

The problem is, when I execute it:

EXECUTE dbo.IndexOptimize
@Databases = 'MyDatabase',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

It only finds 2 or 3 indexes (On Messages Window). And I have a lot of them with more than 90% fragmentation:

enter image description here

Is there some information I'm missing?

Maybe there are not enough pages, so there's no performance impact, and then the script just skips the index ( the first index has 240 pages)?

  • 1
    There is a variable in the script. Something like 'do not rebuild indexes with less then 1000 pages' - this is the default. You can open the script and see it for yourself. This might be the problem – George K Apr 24 '17 at 15:07
  • 1
    Oh, Maybe @PageCountLevel int = 1000, ? – Racer SQL Apr 24 '17 at 15:09
  • From the documentation regarding parameter "PageCountLevel" - "Set a size, in pages; indexes with fewer pages are skipped for index maintenance. The default is 1000 pages. This is based on Microsoft’s recommendation." - what happens if you manually provide this parameter with a lower value? – Scott Hodgin Apr 24 '17 at 15:10
  • @Rafael Piccinelli, exactly – George K Apr 24 '17 at 15:11
  • Oh I didnt change it. I was just confused. with <1000 it executes with all index. Simple variable I didn't notice. Maybe we can close this question, or answer it @smokingDBA. – Racer SQL Apr 24 '17 at 15:11
2

There is a variable in the script. Something like 'do not rebuild indexes with less then 1000 pages' - this is the default. Its name @PageCountLevel

You can change it to whatever you wish.

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.