identical plans different performance

Pearljammer1 2018-08-14 21:35:18

I am running a stored proc which returns some data. On production it takes over 1 min and on staging it runs in around 15 secs. I have restored production database onto staging and ran the stored proc with identical paramaeters on both servers. The production sql box has 50Gb of memory with 8 cpu cores, whilst the staging box has 2 cores and 8Gb of memory.
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.)

Hugo Kornelis 2018-08-17 19:47:05
Are the plans on production and on staging EXACTLY the same? Or do they have roughly the same shape but differences in properties of some operators?
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.