• Yes sir, the Current production server does it in about 1 min. The new one takes from 4 to 7 mins. I did use the hint which I need to do again and go back and do again and look at the plan because I cannot remember the details, but it still took much longer. I have also tried limiting the columns. I see disk latencies up to 2000 ms during this so it make us wary of migrating until we are sure. That is why we have double checked all the hardware. I just don't get why this server is choosing such a slow plan. I also tried using a plan guide but for some reason I couldn't get it to use the guide. I couldn't get much info about why but I did the profiler to see if it was used or not.

    A bit of interesting info my reporting server which is a replicated copy of many of the tables can do a loop 600 times of this query using a random claimid seeded in 60 seconds. If your wondering how that's possible the server has a PCI SSD card that uses some software called Velobit for disk read caching. It uses the same plan as the Production Server and is 2008R2

    I don't always test my SQL scripts, but when I do, I test in Production.