I've tried restricting Q2 to single threaded execution, and that didn't help, it just eliminated the parallelism from the plan and extended the duration time a trivial amount.

I haven't checked ad-hoc optimization but I will.

The row est. vs row act. is also funky, but not the Saturday Night Fever way.

In the case of Q1, the est. rows is something like 3, but the actual is 95.

In the case of Q2, the est. rows is in the 100K range, but the actual is 95.

I had assumed that the statistics update job that ran last night would have resulted in the est. vs act. rows being pretty much the same.