I work for a company which provides online questionnaires targetted at kids, the user logs on, downloads a renderer about 0.5MB in size, and then they start the questionnaire. We host the site on a dedicated server with an external hosting company, and the server is Server 2003, with SQL 2000 standard (SP4), with P4 2.8GHz processor (not dual core) and 2GB RAM. We have just moved from another web hosting company and the only difference between the spec of the 2 servers is that the previous one had a dual core processor.
The problem is that on the new server, when just one person accesses our login page the sqlservr.exe process in task manager shoots up to 90+%, whereas on the previous server the same action would cause sqlserver.exe to go up to 2%. When a person actually logs in and attempts to download the renderer on the new server sqlserver.exe will again go up to to 90+% and will stay there longer. As part of our business we are used to having a classroom full of kids logging on at the same time, but at the moment we are unable to serve any more than 5 people logging on at once, CPU usage will go up to 100% and will stay there, ultimately resulting in SQL servce being restarted.
We are mystified by this, as the programmers say that the 2 servers are identical in terms of SQL config and code, so the difference must be due to the fact that the current server doesnt have a dual core processor, but I think that the hardware is more than sufficient to support 30+ users, so the problem has to be with SQL config.
Does anyone have any ideas on where the problem could lie, and if it is with SQL config, where I should be looking?