This question already has an answer here:
I have a stored procedure running fine before 4PM every day. After that it runs like a dog.
I identified which query inside the sp caused the problem. In a good run, the logical IO is about 12 million. In a bad run, it jumps to 40 million.
In addition to the logical IO, there is no resource constraints and locking issues.
In addition to modify/create indexes, what else should I look?