Query execution plan different between production/test - same data

Here are a couple of things to check...
-- Does the production DB/Table(s) has the same amount of
data as the test DB/Table(s) ?
-- The amount of other transactions running at the same
time in Prod/Test.
-- Use Query Analyzer- 'Display Estimated Execution Plan'
and/or Profiler to see where the bottleneck is.

>--Original Message--
>I have a query that runs much much slower on my
production server than my test server. By tweaking the
query I can make it run fast in production. Why can't I
make prod act like test? The difference in execution time
is 15 minutes! (test - 5 seconds/prod - 15 minutes). I
need to make PROD more like TEST, or at least TEST should
be slower than PROD (what is the point of load testing?)
>My test server has the same SQL version/patch level and
OS version/patch level as my production server. The data
is a replica. The query execution plans are different. The
production server has more ram, faster CPUs (dual), and
faster disk. Production is under light utilization (<20%).
It is reindexed each night. I have updated statistics on
all tables involved. I ran a server configuration and
schema configuration comparison with RedGate software and
all items are indenticle (save ram/cpu/names of jobs).
When the slow query runs on production it chews one of the
CPU's @.50% for all 15 minutes. The problem has slowly
gotten worse of the past month. The database is only 120mb
and is the only db so far on this server. The DB server is
dedicated, there are no other uses for it. Its paired app
server is under very low utilization also. As I said, by
tweaking the query the query time goes from 15 minutes
down to 4 seconds without changing the result set. The
main gotcha is that my test server runs the fast execution
plan with or without tweaking the query. I need it to run
fast without tweaking so my users can run ad hoc queries
without me having to get involved. I know it is possible,
look at my test box!!
>I am stumped on this performance problem. Please help if
you can with any thoughts or suggestions for getting MSSQL
to run consistant with respect to execution plans. Our
Oracle DBA is laughing...
>.
>
Thank you for the ideas Dan...
-- The test db is an exact replica of production
-- I've tried the query during a period of zero utilization on the prod server with no improvement in query speed or change in the flawed execution plan.
-- I've compared the actual execution plans, not estimated. My contention is that they should be identical and they are not. In any case the prod server having two processors and more memory and faster disk should be faster. I've tried turning off the 2nd
processor so that the server configurations and databases are the same. No change in the situation.
I've used RedGate software and sp_config to compare the setups for the two machines. Other than physical characteristics and the trivial names of some DTS jobs they are the same.
"Dan" wrote:

> Here are a couple of things to check...
> -- Does the production DB/Table(s) has the same amount of
> data as the test DB/Table(s) ?
> -- The amount of other transactions running at the same
> time in Prod/Test.
> -- Use Query Analyzer- 'Display Estimated Execution Plan'
> and/or Profiler to see where the bottleneck is.
>