Ok, I was thinking it was because the workflows just had too many millions of activities. There must be some theoretical limit to how many activities you can have in a single workflow instance before you hit the timeout.

I will run the Rebuild or reorganize all indexes and Update Statistics scripts from Ola during off hours and see if it fixes the problem, if so will schedule for once a week.

Any limits would depend on hardware and how many of these instances you run and what else is going on in the SQL server at the time. So it's not a hard set number.

Workflows with millions of activities are a bad idea in all cases because Workflow needs to keep track of all those activities and their resulting tokens for the entire duration. It's best if you keep loops under 1000 iterations.

If you run over a set of entries that need the same type of processing, it's fast if you spin off the inner activities into their own workflow that is invoked inside your For Each. That way you take advantage of Workflow's parallel architecture and increase your throughput. On the maintenance side, you end with smaller instances, so less of a chance of time outs when they're being updated or cleaned up from the database.