can not get optimized execution plans in a faster machine 1

[Shude]

I have SQL server 2000(up to SP3) installed in our QA server which is Windows 2000 server with one CPU, 2G RAM, all store procedures in the database run fine. But once we moved the same database to our production machine(up to SQL server 2000 SP3, 2 CPUs, 3.7G RAM), no matter the database is rebuilt from scratch or restored from a backup copy taken from QA server, store procedures run much slower. I found out that, for the same store procedure, the execution plans generated on both servers are totally different. So I set up database maintenance plan job to 100% update statistics on PRD server every night. Sometimes, it helps, sometimes it does not make any difference, sometimes it gets worse. I tried to use hints on some store procedures, it either does not help much or getting worse. The installation settings, licensing setup, db options on both server are all the same. The only difference is that the PRD server has one more CPU, more RAMs, which is supposed to be faster than QA server. It looks like I just can not control SQL query optimizer to generate the right execution plan on this PRD server. What should I do?
Any advice would be appreciated!

 

[nigelrivett]

Try seting the sql server to use a single processor.

Instead, if you can find the queries that are causing a problem you can use maxdop = 1 in the query.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Shude]

Thanks, Nigelrivett,
Setting Maxdop=1 hint does help a lot.