?
Solved

sqlservr.exe going very high

Posted on 2006-04-28
15
Medium Priority
?
1,208 Views
Last Modified: 2011-09-20
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?
0
Comment
Question by:Pilky
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16561202
I guess the problem is that you did not update the statistics on the tables after the database moved.
0
 
LVL 8

Expert Comment

by:JMattias
ID: 16561222
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
 

Author Comment

by:Pilky
ID: 16561324
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 16561339
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
 

Author Comment

by:Pilky
ID: 16561367
It is standard edition with SP4
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16562921
>> 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
 

Author Comment

by:Pilky
ID: 16563131
Sorry, apparently it was just a straight copy so statistics were not updated before or after the transfer, would this be wrong?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16563149
highly probable.
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 16563196
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
 

Author Comment

by:Pilky
ID: 16563224
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
 

Author Comment

by:Pilky
ID: 16563257
Apparently all databases were auto-updated upon creation so the programmers are here are saying that is why the statistics havent been updated.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16563339
It certainly won't hurt anything to re-update the stats and then you'll know for sure if that is a factor  :-) .
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16563835
Just to be sure, make sure the db is not set to "autoclose".
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question