Query execution plan different between production/test - same data

I have a query that runs much much slower on my production server than my te
st server. By tweaking the query I can make it run fast in production. Why c
an't I make prod act like test? The difference in execution time is 15 minut
es! (test - 5 seconds/prod
- 15 minutes). I need to make PROD more like TEST, or at least TEST should b
e slower than PROD (what is the point of load testing?)
My test server has the same SQL version/patch level and OS version/patch lev
el 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 s
tatistics on all tables involved. I ran a server configuration and schema co
nfiguration comparison with RedGate software and all items are indenticle (s
ave 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 minu
tes. 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 use
s for it. Its paired app server is under very low utilization also. As I sai
d, by tweaking the query the query time goes from 15 minutes down to 4 secon
ds without changing the re
sult set. The main gotcha is that my test server runs the fast execution pla
n 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 kno
w it is possible, look at m
y test box!!
I am stumped on this performance problem. Please help if you can with any th
oughts or suggestions for getting MSSQL to run consistant with respect to ex
ecution plans between my two servers. Our Oracle DBA is laughing...There are many explanations for difference in query behaviour and
execution. The same query can behave differently on different boxes
depending on configuration and general environfment specific to a
particular box.
You might want to refer to
243588 HOW TO: Troubleshoot the Performance of Ad-Hoc Queries
http://support.microsoft.com/?id=243588
Things you might want to pay attention to :-
* The tables or views the query touches - are they identical on both the
'good' (Test) and the 'bad' (Production) sql servers? Meaning, do they have
identical indexes (you can check doing a sp_help on the tables) OR do they
have the same kind of data and data distribution?
* How often are statistics updated on the good and bad sql servers? Try
doing an UPDATE STATISTICS myTable WITH FULLSCAN on each of the tables that
are accessed by this query. (You might want to do it off peak hours though).
* run the query through the Index Tuning Wizard - see if it recommends any
additional non clustered or covering indexes.
* General load on the server(s), are they the same?
Hope that helps.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.