1

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.

LowlyDBA - John M
  • 10,922
  • 11
  • 42
  • 62
Srikar Reddy Kasireddy
  • 19
  • 2
  • Can you include what you have worked on so far? What do you mean by updating it is a problem? – LowlyDBA - John M May 05 '18 at 19:00
  • Keeping up to date with the latest versions. – Srikar Reddy Kasireddy May 07 '18 at 03:49
  • We developed a solution where it captured the index stats in a table and loop the table for the duration of days we setup and rebuild or reorg the indices on everyday schedule. Once the max days is reached, the next run will start capturing the fresh stats of indices and run through same. We achieved parallel threads by running all this with sql agent jobs controleld by a controller job. – Srikar Reddy Kasireddy May 07 '18 at 03:53

1 Answers1

2

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.

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 CommandLog table

I 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 commandLog table and use powershell runspace (be careful of not spawing many threads - see Notes on usage of using runspace).

Kin Shah
  • 62,225
  • 6
  • 120
  • 236