SQL Server Index Maintenance - OLA Hallengren Index Optimize

[@annwilson3840]

This is a better update, thanks SQL

[@dirtydevver]

so if i understand it correctly a vanilla install wont update statistics in any capacity unless we add a Y to that parameter instead of NULL ?

[@sqlanswers]

Yes, that is correct! You need to provide a value for @UpdateStatistics parameter in order for statistics to be generated.

[@leonkranjcevic4877]

I use SQL Express so I dont have SQL Agent but I want to change the time when the IndexOptimize executes. Where do I change it?

[@sqlanswers]

Are you scheduling IndexOptimize now or are you looking to schedule it? If you already have a scheduler you would change the time there. If you are looking for a scheduler, you could use a Windows Task Scheduler. (Control Panel > Administrative Tools > Task Scheduler)

[@leonkranjcevic4877]

@@sqlanswers Thank you so much you saved me with this.

[@sqlanswers]

Glad I could help!

[@maheshkharawatkar3847]

I wanted to only configure index reorganize using the olla hallengren. considering my database size is 10 TB should I keep the sort in tempdb option yes? Is there a way to reorganize the index without hampering the performance? How Can I configure olla hallengren scripts only for index reorganize even if the fragmentation level is above 30 %?

[@sqlanswers]

Thanks for your questions! I will try and follow up with a video, but here are some quick answers should I keep the sort in tempdb option yes? Depends on how much tempdp space you have available and how big your largest index is Is there a way to reorganize the index without hampering the performance? There is no free lunch, but you can use strategy to run index maint during off hours, split the work up, and use time limits! How Can I configure olla hallengren scripts only for index reorganize even if the fragmentation level is above 30 %? You can specify the same action for all thresholds. There is no rule that says you have to have a different action. @FragmentationMedium = 'INDEX_REORGANIZE', @FragmentationHigh = 'INDEX_REORGANIZE'

[@boykaredemp6216]

Hi , why Ola Hollagren index optimization job is filling up log space??

[@sqlanswers]

Hi - Index Maintenance does use a lot of log space because they generate a lot of changes to the index structures. Each index structure is modified as a single transaction, so depending on the indexes that were rebuilt that could require significant transaction log space. Transaction log space cannot be released to be reused until transactions are completed.