sqlservr.exe going very high

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?
PilkyAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will have to identify the queries that run with so much CPU (use the sql profiler for that), and use the query analyser (with the Show Execution plan) for the relevant query what "goes wrong". possibly an index missing..
you can try to rebuild the indexes
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I guess the problem is that you did not update the statistics on the tables after the database moved.
0
 
JMattiasCommented:
Hi,

What version of SQL Server 2000 is it?
They have to update statistics etc so that the indexes are up to date.

Otherwise they have to check what the server does, profiler, perfmon etc.

Regards
/Mattias
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PilkyAuthor Commented:
I know next to nothing about SQL, but apparently we updated the statistics before detaching the DBs from the previous server, then copied them to new server and reattached, is this wrong?
0
 
PilkyAuthor Commented:
It is standard edition with SP4
0
 
Scott PletcherSenior DBACommented:
>> apparently we updated the statistics before detaching the DBs from the previous server, then copied them to new server and reattached, is this wrong? <<

No, but you also have to update the statistics *after* putting them on the new server.
0
 
PilkyAuthor Commented:
Sorry, apparently it was just a straight copy so statistics were not updated before or after the transfer, would this be wrong?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
highly probable.
0
 
Scott PletcherSenior DBACommented:
No problem, it's very easy to correct (although it may take some time to run, depending on table sizes):

--from Query Analyzer

USE yourDbName

EXEC sp_updateStats 'RESAMPLE'
0
 
PilkyAuthor Commented:
I think my last comment was a bit vague, and apologies for sounding stupid, but I take it you would strongly recommend updating the statistics?
0
 
PilkyAuthor Commented:
Apparently all databases were auto-updated upon creation so the programmers are here are saying that is why the statistics havent been updated.
0
 
Scott PletcherSenior DBACommented:
It certainly won't hurt anything to re-update the stats and then you'll know for sure if that is a factor  :-) .
0
 
Scott PletcherSenior DBACommented:
Just to be sure, make sure the db is not set to "autoclose".
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.