September 5, 2001 at 9:40 am
We have a single SQL Server instance with four databases. About 80 concurrent users.
Our CPU gets pinned (100% utilized) when all users make queries at the same time. The DML is 95% select statements, using stored procedures on top of views. Disk activity is very low, with most reads comming from cache. Procedure run time is fast with one user, less than a second. 10% of queries are returning a single image.
DL 360, with 2 CPUs and 1GB memory.
DB1 20MB
DB2 30 MB
DB3 300 MB
DB4 150 MB
Any help or direction would be appreciated.
- Brendan
September 5, 2001 at 10:25 am
September 5, 2001 at 3:30 pm
Is SQL set to use all 4 processors? Is the OS seeing them? Doesnt hurt to check! Have you set any of the affinity/parallel options? What version and service pack of OS and SQL are you using?
Andy
September 5, 2001 at 3:32 pm
or the name of the application? Don't forget the DL 360 doesn't take xeon procs in it ether. I wouldn't expect wonders from the little 1U unit. We passed them up for heavy DB loads. Also, how many users and have you run any profiling on the SP's yet?
Wes
September 6, 2001 at 7:12 am
Brendan,
How much memory is being utilized with the 80 concurrent users? You may want to troubleshoot this further by looking at the user issue further. You touched on the fact that the processes work great with one user so, that would be where I would start to look.
One other thing you did not mention was what OS you were using and what SQL Server version and Service Pack you were using. Also, is there any other applications running on this box or is it strictly dedicated to SQL Server?
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 6, 2001 at 6:10 pm
Hi
Crank out perfmon and start adding counters. Run this for a few hours every minute. You will be amazed how much information this can give you.
80 concurrent users is a lot of people. I believe your problem is the server is paging heavily, you will probably find high values with %priviliged time and spikes in disk queue lengths. How much "free" RAM do you have? if its 10 to 15Mb then SQL is just keeping enough avail for the rest of the system.
If you are tights on memory, reduce the MAX from dynamic to fixed around 100Mb free) and see what happens to overall performance. It will take sometime for SQL to warm up to the fixed alloc of memory.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply