identical plans different performance
The 2 plans are identical – yet one runs much quicker on the lower spec box.
Any ideas to what is going on.
Not sure if this is a tempdb db issue? on the staging box there is one tempdb file for the 2 cores. The production box has 4 tempdb files (did have 4 cores but its just been increased to 8 to see if it would help this situation – it hasnt, so i might add another 1 or 2 files to the tempdb so it has 6 files/8cores.)
A quick way to check this is the Compare Plans feature in SSMS (I don't know if Plan Explorer has something simimar). There are a few properties that will always be different but most should be the same if the plans are actually identical.
(I checked the .pesession file you attached but that one contains just a single execution only and doesn't even specify if it's the prod or the staging version)
If the two execution plans are really exactly the same, and if the performance difference is consistent, I'd suggest tracking wait statistics during execution. Maybe the production query suffers from lots of blocking from concurrent workloads? Maybe other workloads are starving the resources of the box?
If none of these help, then I suggest executing th eprocedure from Plan Explorer, within a single session (change the connection in between). Then save that as a new .pesession file and upload it here. It should include more details than the one you now have, and it will allow people to compare the staging and production executions.