MSSQL maxes out dual CPUs, but not memory...

[Guest_imported]

I'm running MSSQL 7.0 on a Compaq Proliant with a gig of RAM and dual 1 GHz Pentium III processors. We have approx. 15 users, using third party software (Compuware)to hit the database as a back-end. We have been monitoring CPU and RAM usage because users have been complaining of slowdown and we have found that, at times, both CPUs spike to 99% usage by SQL server, yet the memory usage never exceeds 512 MB. The documentation says that you do not need to specify the amount of memory to use, yet I don't understand why the server would peg the CPUs while not using the available RAM. I know there is a way to specify your particular RAM usage, but in this case, would it help? Or does it sound like we may have to investigate purchasing a newer server?

 

[tlbroadbent]

Let SQL Server manage the memory. In nearly every instance where we've encountered maximum CPU usage, the culprit has been...

1) a poorly designed query
2) a poorly designed stored procedure using cursors
3) table(s) without indexes or with inadequte indexes to cover the queries.

I've not encountered many performance problems in the six years I've used SQL server that couldn't be traced to the application or database design. The occasional exception is when we run out of disk space.

The first thing I would do is create a TRACE using SQL Profiler to capture the SQL statements being sent to the server. Try to determine what is running when CPU usage maxes out. Analyze the queries to determine if the joins are properly defined, where clauses exist, etc.

If you set it up properly, the trace can be fed into the Index Tuning Wizard to see if table(s) have adequate indexes. Or you can analyze queries one at a time in Query Anlyzer.

I would definitely look for application problems before trying to reconfigure SQL Server.
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.