Unanswered: Identical Server but different performance puzzle
We have MSSQL 2000 Server instance installed and working well on Windows 2003 Server machine [IBM X series-366] with 16GB RAM, 3.67GHZ cpu power, and 400GB hard disk space.
We further created an identical server instance on a new machine. More specifically, on Windows 2003 Server machine [Intel (R) Xeon (TM)] with 16GB RAM, 3.67GHZ cpu power, and 400GB hard disk space, we installed MSSQL 2000 Server and copied over all the dbs, applications ...
We were expecting same or similar performance (since processor speed, ram, hd, server and database configurations are all the same, with same indexes on same tables. However, for some reason, there is a noticeable difference in performance.
More specifically, I ran Profiler for 30 minutes on both servers simultaneously [same trace parameters]. The trace file of the new server is 3 times as large as that of the old one (i.e. It looks like more items are being processed). However, the average duration of the executed stored procedures is much longer on the new server than that of the old server.
Moreover, when I run same queries on 2 servers. The query on the new server always takes longer than that on the old server. And for tables where we don't have indexes, it takes much longer.
Following advice here(http://support.microsoft.com/kb/274750/), we configured our new server (just as was our old one configured) to use 15GB of RAM. I further compared the configurations of 2 servers by executing sp_configure (with advance options). The only difference I saw was that "remote proc trans" is set to off on the new server and on on the old server. I don't think it could affect this issue though.
Furthermore, the new server appears to have many more locks, as compared to the old server. Could it be because it is processing more items?
I cannot figure what is causing the queries to be slower on the new server.
Where should I start? There is just SO much that still can be different. The amount of disk space is not that important. But What RAID level is it? How many physical disks does it span across? What speed does the disks have? What bandwith you have to the disks? And not to forget, how is the caching configured? What Kind of memory is used in the two servers, and at what speed does the memory run? As you see, there are just SO many other factors that comes into play...