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...
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.