Query execution plan different between production/test - same data

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).
The prod server has 2 cpu so I tried restricting parallelism to 1. 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 re
sult 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 m
y 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 between my two servers. Our Oracle DBA is laughing...
Can you show the query? You say you tweaked it? How did you do that? Is
the data the exact same on both machines? What does the query plan look like
for the slow one compared to the fast one?
Andrew J. Kelly SQL MVP
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:33F423DF-1D9C-4227-B1F4-2CAF5142BD9C@.microsoft.com...
> 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). The prod
server has 2 cpu so I tried restricting parallelism to 1. 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 between my two servers. Our Oracle DBA is laughing...
>