MS SQL 2000 Performance / Ram Usage

[alastairb]

Hi

I have an SQL Server which is of the following spec

HP ML350 G3
2 * 3Ghz Pentium Xeon
2Gb Ram

1 * Raid 1 set for OS and Logs
1 * Raid 5 set for Database files

All Hdd are 15k rpm

Dual 1gb cards teamed to provide 2gb connection for load balancing

It runs MS SQL 2000 SP4 and acts as a file server. It is not a high usage enviormnet, approx 40 users with light work load.

To put some history on the situation I work for a company that uses a custom application. With a Delphi front end which executes Sculptor (www.sculptor.co.uk) code that interfaces with a custom application on the server which in turn interfaces with MS SQL.

I realise most of my perfomance issues come from the way the package works as above. However I am trying to go through each item in turn to speed up overall usage.

Turning to MS SQL, there are two particular counters the supplier of the software has told me have issues and I need more ram, namley

SQL Server:Cache Manager, Cache Hit Ratio : average of around 87
SQL Server:Buffer Manager, Stolen Pages :approx 85000 at the end of the day

I have checked disk counters and %read time / %write time are avergaing below 1,. Pages/sec is averaging below 0.5. sqlservr.exe itself is aking 1,144,560k with a peak of 1,437,084k , with around 389mb free in Task Manager.

The indexes are rebuilt each night by the maintenance plan as well as disks fragmented via a batch file. Anti virus is Mcafee 8.0 updated and set not to scan ldf, mdf, etc.

I question if adding RAM will make a difference, as to me the server appears to be under very little heavy load and the problem lies with the "middleware" of the application rather than SQL.

I realise that Cache Hit Ratio should be higher, eg above 90, but the server does have spare RAM that SQL is not taking, and its set within Enterprise Manager to use all available ram.

Am I right in this assumption baring in mind the above ?

Below I have attached my dbcc mem stats for info

Buffer Distribution Buffers
------------------------------ -----------
Stolen 2269
Free 6313
Procedures 68882
Inram 0
Dirty 1844
Kept 0
I/O 0
Latched 47
Other 58448

(9 row(s) affected)

Buffer Counts Buffers
------------------------------ -----------
Commited 137803
Target 146641
Hashed 60339
InternalReservation 271
ExternalReservation 0
Min Free 128
Visible 146641

(7 row(s) affected)

Procedure Cache Value
------------------------------ -----------
TotalProcs 28137
TotalPages 68882
InUsePages 46146

(3 row(s) affected)

Dynamic Memory Manager Buffers
------------------------------ -----------
Stolen 71151
OS Reserved 984
OS Committed 962
OS In Use 951
General 2987
QueryPlan 68851
Optimizer 0
Utilities 16
Connection 207

(9 row(s) affected)


Global Memory Objects Buffers
------------------------------ -----------
Resource 1113
Locks 40
XDES 20
SQLCache 1745
Replication 2
LockBytes 2
ServerGlobal 21

(7 row(s) affected)


Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 74166
Maximum (Buffers) 74166

(4 row(s) affected)

Optimization Queue Value
------------------------------ -----------
Optimizing 0
Waiting 0
Available 16
Maximum 16

(4 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.



Thanks
Alastair