Ola Hallengren in High I/O Environment with Limited Maintenance Window: How to Optimize?

[viniciuseroico]

I have a question and would like your opinion on the matter. I use Ola Hallengren’s index maintenance routine, but I have a large database, around 4TB in size, with over 35,000 indexes. Additionally, it's a database with high daily I/O activity, meaning the indexes become fragmented very quickly. Another important point is that it's running on SQL Server Standard Edition.

We only have maintenance windows during the early morning hours, from 3:00 AM to 7:30 AM, and this window has not been enough to complete all the reorganize and rebuild procedures. We've considered splitting the executions into blocks, perhaps based on fragmentation level or sets of tables, but that would need to be specified in the maintenance procedure's parameters.

Another issue we've noticed is that sometimes the maintenance completes in about 3 or 4 hours, but other times it processes only a small number of indexes and already exceeds the window, as if something is "holding back" the execution. I'm using the LOCK_TIMEOUT option set to 5 minutes, but even so, it doesn't seem to be enough.

Does anyone have other ideas on how to handle maintenance in this environment in a fast but efficient way?