What happens when I Change Index in Ola Hallengren?
Index maintenance changes data pages, too. This also sounds harmless, but if you’re doing differential backups or storage replication, boom, you just inflated your storage and bandwidth costs. The larger your database becomes, the more you’ll start to investigate these recovery options, and the more index maintenance starts to backfire.
Which is better Ola Hallengren or SQL server maintenance?
If you’re a production database administrator responsible for backups, corruption checking, and index maintenance on SQL Server, try Ola Hallengren’s free database maintenance scripts. They’re better than yours (trust me), and they give you more flexibility than built-in maintenance plans.
How to configure Ola Hallengren’s maintenance scripts?
The defaults on some of these parameters are a little tricky: When an index is between @FragmentationLevel1 (5%) and @FragmentationLevel2 (30%), the scripts will perform the actions listed in @FragmentationMedium, in order. First it’ll try an index reorganize, which is an online operation.
Why do offline index rebuilds cause block queries?
Offline index rebuilds cause blocking. As low as 5% fragmentation, the script defaults have the potential to take tables offline and block queries. I take kind of an extreme view on this: I would rather not take tables offline when there’s not a human being around to watch what’s happening. So here’s the defaults I prefer:
Why does Ola Hallengren take so long to run?
I run the Ola Hallengren scripts on all of my servers for index and statistics maintenance. When I look through the command log table, I notice long periods of time between a command ending, and the next command starting. Sometimes this gap is over an hour. Does anyone else observe this on their systems?
How to index Ola Hallengren’s SQL server maintenance solution?
The article will cover the IndexOptimize stored procedure in more detail, along with the index optimization jobs created by Ola Hallengren’s scripts. To get the detailed guide on installation procedure and solutions for database backup and integrity check, feel free to first review the first two articles of the series: