Ola scripts for index maintenance work really well for smaller databases. For multi terabyte databases with big indices, many partitions, and a small interval of schedule times I observed that the script always tends to rebuild same indices and never finishes.
How can we implement the solution to capture and then run the index optimization for certain duration of days and then reset. Can we also have it run in multiple threads?
We did develop a solution to achieve it, but the only problem we have is updating it on regular basis.
Best Answer
This is very common for VLDBs. You can be a bit smatter and follow Paul Randal's Quick list of VLDB maintenance best practices. You can rebuild indexes ONLINE in Enterprise edition.
In your case since you are using Ola's Index Optimization scripts, you are running into endless index defragmentation which you can solve by being analyzing
CommandLogtableI have given few more thoughts on Is a weekly rebuild of indexes a good idea? covering maxdop option.
I would say, if your databases are extremely big (TB size), atleast do a reorg instead of rebuild and manually update stats. This will put you in a good reasonable state since a reorg will start from where it got killed.
For running in parallel threads, you can drive of
commandLogtable and use powershell runspace (be careful of not spawing many threads - see Notes on usage of using runspace).