Database Performance after moving it from one sql server 2000 to another sql server 2000

I have this crazy problem. I moved a database from one SQL server 2000 to another. The old one is a much slower server with a single processor. The second one is new fast and has 8 Processors. Generally the server perform better. Specially with my websites. However, one query in particular which is the most complex one is very slow.... 10 to 16 times slower. I can't figure why. I rebuild the indexs and both servers are running server 2003 enterprise edition.
Rebuilding indexes improved the performance overall, but not that query. Slow server gets back results between 1-4 seconds... the fast new server gets back results in about 16-16.785 seconds. Any clue why? Apperantly my servers settings must be different or memory cash id different but I can't tell exactly the problem. Small queries with large results are much faster.... but complex queries with similar results are slow!!

You may want to update the statistics on the affected tables, the queries could be using incorrect execution plans.
UPDATE STATISTICS tblTableName WITH FULLSCAN
Look in Books Online for other options with UPDATE STATISTICS.

HI,You can do one more thing, try to analyze the Execution Plans of both the server for the same query. Try to identify the costing difference.Performance issue may happen due to hardware issues like driver mismatches while configuring controllers, this may cause IO. You can identify this adding performance counters in both the servers and compare it.-Johnson

Ok. I have done the suggested ideas. It didn't really make a difference for the statistics. So I looked closely at the execution plan. I found out that changing the number of processors to use in the parallel execution plan mad a difference. When I sent it to 1, 2, 3, 4 it didn't make a difference. My number of seconds is 5. When I have it to use more than 4, like Max number of processers or another number, I see a drop. Especially when I go over 5. Why I don't understand it. If the server knows that this query uses under 1 second time, why does it make a difference of how much I set the # of Parallelism processors? why does it even care from going to .3 seconds to 16.5 seconds by using more processors? am I still optimized for using all my processors?
The execution plan shows difference between when it uses Parallelism or not between 6 processors or under. Any good explanation?

Is there a stored procedure involved?Stored procedures can sometimes be badly optimised, with the cure being to take a local copy of parameters.

I never heard of a SP being a problem. But no, it is just a query. It is very optimized. It has the correct indexs, and it run very fast on a very slow machine. In fact, one of the eight processors in the new machine has more power than the old machine. Also, the old machine has a total of 1.5 GB of Ram, the new one has 8. Disk drive is faster.... just whole a lot better.

Ok, sounds like it is mainly a problem with too much parallelism. Have a read herehttp://www.mauvais.com/publish/ZD-Parallel.htmPossibly you should also check whether you have disk fragmentation.I also wonder about the memory cache of the new machine.http://en.wikipedia.org/wiki/CPU_cacheMaybe you could post details of the old and new hardware.Possibly the large number of processors has some drawbacks.

Ok. The first article you sent looks pretty good. This is probably the problem. But I have to wait until Monday to try it. The reason is b/c nothing can be done on the servers until Monday. I will let you know. I have not read the second one yet. What I have to do is to enable AWE on the sql server and then allow the Enable the Lock Pages in Memory Option on the operating system. I noticed that my server never runs over 2 GB which may be the problem. He has 8 GB. Parallelism appears to need more than 2 GB to run.... so To that I have to run the sql server under the sql server account on the domain. It is currently running under the SA account. I don't know what that will involve with it. What kind of issues I will run into once I do that. Any ideas? Is it a problem changing the account the sql server runs under?
I do think it is too much parallelism and that is why when I dropped the number of max Proccessors to use it did ok. However, I still should be able to use Max parallelism and not affect anything. I can't reduce the performance of my SQL server b/c one query in one database is not correct! Don't you think so?
Now this is what in the Article (http://www.mauvais.com/publish/ZD-Parallel.htm ) what made me thinking that is the problem... this section:
The server must have sufficient memory available because executing a query in parallel requires significantly more memory than executing the same query serially. The higher the DOP, the more memory required. If the server doesn't have enough memory available, the Query Optimizer backs off the DOP-all the way down to 1 (serial execution)-until it finds the server has sufficient memory to run the query. You can see the additional memory requirements for yourself by examining the Granted Workspace Memory counter of the SQL Server:Memory Manager object in Performance Monitor.

OK. I enabled AWE, and ran the sql server under an account that has Enable the Lock Pages in Memory Option (Windows) as this article talks about: http://msdn.microsoft.com/en-us/library/ms190730.aspx
Sadly enough not results. I didn't not see any performance improvement when I switch back to Use Max Parallisim. I have to go to 4 or 5 Processors only.
The old computer specs are:
Intel(R) Pentium(R) III
927 MHZ, 1.5 GB of Ram
It was running server 2000 Standard
and the page file Min = 2046 Max = 4096
the new computer hardware are:
Intel(R) Xeon(R0 CPU
E5405 @2.00GHz
2.00 GHz,7.99 RAM
Physical Address Entension
running windows server 2003 Enterprise edition SP2
and the page file Min = 12279 Max = 12279. It is on the Log Partition. C partition has no pagging. F partition which has the databases has no pagging.
Any Idea what in the world is going on?????? Why I have to use less processors to get as good performance as the old machine that running one.

Do not change the server setting for maximum parallellism on account of a single query.
On the problem query, add OPTION (MAXDOP 1) as the very last part of the query statement. This will force this particular query to be executed by a single processor.
Without any details about the problem query and its underlying tables, there isn't much we can suggest otherwise.

Adriaan... I think you got this idea confused. I don't know why you are saying do not... In fact it seems that I should set it to 1 unless in the query it will work better then we can ad the option to use more cpus... so MAXDOP on the server should always be one. Only if the query has showed better performance then it should set it to higer number in the query not in the server. Unless you explain to me why I should not change it... then it sounds like a person just saying something to sound smart.

I disagree. From what I've read on the thread, there is only one query that has issues, and the rest all seems to benefit from the new server.
So you only need to tackle the parallellism issue for that one query.
If you change the server setting, it may negatively affect all the other processes.