I appreciate the response, and yes I don't expect someone to volunteer for a remote DBA session and solve my problem for free. I just tried to flesh out a full background context and asked for some general pointers on approaches to take to solve this problem.

I was hoping for insights on some general approaches like "I would watch DMV 'abc' or you really need to boot up a profiler trace and watch 'xyz'." I know I've seen helpful general hints like this on this forum before. I'm trying to understand reasons why the optimizer will choose a completely different plan or disregard an index. Yes I know the stock answer is always "it depends", but that list of things it depends on is not infinite. Isn't the point of this forum to encourage learning and foster understanding?