Different Execution Plans -> Same Query, Same Database, Different SQL Server Install

Background: Same database, restored from development to production.

Indexes up to date. Service Pack 3 on Production. Originally RTM on Development, upgraded to Sp4, Execution Plan remained the same.

The query itself is not my concern, but that there is such a wide difference in performance between 2 boxes. Is there a setting on the Production that might be slowing it down? My system seems to have selected a worktable, is there a way I can turn that off to mimic the server?

I want to optimize the queries to perform best on the Production server, which is hard to do if my server's chosing different execution plans.

Is there anyway to determine why the Production machine would choose one plan vs. the development machine?
Development:
|--Nested Loops(Inner Join, OUTER REFERENCES:([CLIENT].[CONSUMER_UUID]))
|--Index Spool(SEEK:([CLIENT].[FIRST_NAME]=.[FIRST_NAME] AND [CLIENT].[LAST_NAME]=.[LAST_NAME]))
| |--Clustered Index Scan(OBJECT:([SAMS2K_ND_STATE].[dbo].[CLIENT].[PK_CLIENT]))
|--Clustered Index Seek(OBJECT:([SAMS2K_ND_STATE].[dbo].[CONSUMER].[PK_CONSUMER]), SEEK:([CONSUMER].[CONSUMER_UUID]=[CLIENT].[CONSUMER_UUID]), WHERE:([CONSUMER].[DOB]=[C2].[DOB] AND [CONSUMER].[RES_TOWN_NAME]=[C2].[RES_TOWN_NAME]) ORDERED FORWARD)

Production:
|--Nested Loops(Inner Join, OUTER REFERENCES:([CLIENT].[CONSUMER_UUID]))
|--Clustered Index Scan(OBJECT:([SAMS2K_ND_STATE].[dbo].[CLIENT].[PK_CLIENT]), WHERE:([CLIENT].[FIRST_NAME]=.[FIRST_NAME] AND [CLIENT].[LAST_NAME]=.[LAST_NAME]))
|--Clustered Index Seek(OBJECT:([SAMS2K_ND_STATE].[dbo].[CONSUMER].[PK_CONSUMER]), SEEK:([CONSUMER].[CONSUMER_UUID]=[CLIENT].[CONSUMER_UUID]), WHERE:([CONSUMER].[DOB]=[C2].[DOB] AND [CONSUMER].[RES_TOWN_NAME]=[C2].[RES_TOWN_NAME]) ORDERED FORWARD)

The following is the output of the statistics on my Local Development
Server:
Table 'CONSUMER'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'CLIENT'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3187 ms, elapsed time = 1877 ms.
This is the output of the statistics on the Production environment:
Table 'CONSUMER'. Scan count 88857, logical reads 274907, physical reads 0, read-ahead reads 0.
Table 'CLIENT'. Scan count 41708, logical reads 48294390, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 690218 ms, elapsed time = 225583 ms.

Have you checked that the statistics are equal for the indexes on both servers?

Look for DBCC SHOW_STATISTICS in the BOL.
|||

Try and rebuild the Indexes on Production Database

DBCC DBREINDEX

|||

The databases were identical, both restored from the same backup resources on the same day.

Thank you for your responses, and I will attempt both, but I don't believe that the statistics or indexes would be different if drawn from the same resource?

|||This is not due to statistics or indexes. It should be identical when you restore from a backup of a database. There are other factors that can affect the query plan - number of processors, memory, maxdop settings etc. Additionally you should try to keep the service pack level the same between the machines. The main reason is that you could get different plans due to changes in service pack and some could be bugs also. So it is best to compare servers that are on the same service pack too. If you are seeing worse performance than SP3 for the same query/machine configuration then it is most probably a bug. I would encourage you to create a bug using the MSDN Product Feedback Center with the repro steps. Also, please use SET STATISTICS PROFILE to check the actual query plan/execution times.|||

It should indeed be identical when you restore but a colleague here has experienced some strange behavior after a restore several times. He claims that after an update of the statistics everything functioned as expected. Now I can't say I fully support this statement as I have no personal experience with this.

We tend to keep our service packs identical to production, I think this should be a rule for every development environment.

You should update your weblog Uma, you still have to answer my NOLOCK question :-)

|||

Thanks for your reply,

The production is running SP3a. I tested the query on development environment at both RTM and SP4 (not realizing the production was still only at SP3) and I did get the same plans between RTM and SP4.

The production machine is Windows 2003 server dual processor,Intel Xeon 3.6ghz, with 3.5 GB Memory, it is part of a cluster server.
The development machine is Windows XP dual processor, Intel Pentium 4 2.6ghz, with 1 GB Memory.

I will compare the profiles in the am.