SQL Server Performance

SQL 2000 Memory Usage

Discussion in 'Performance Tuning for DBAs' started by 99redcamaro, Oct 4, 2007.

  1. 99redcamaro New Member

    i'm currently performing some memory measurements in one of our SQL 2000 box to make sure the server is not suffering from memory performance issues. server currently have 2gb on physical ram and is running SQL 2000 standard edition, dual core CPU. % CPU usage is only 1%
    This mem values have caught my attention
    SQLServer: Memory Manager - Target Server Memory (KB): 1063432
    SQLServer: Memory Manager - Total Server Memory (KB): 980536
    i wonder if the total server memory is supposed to be equal to the target server memory ? does this mean my SQL box needs more physical memory to perform better?,
    this box is not a SQL dedicated box, and from what i can see from the Task Manager, i still have 440mb availbe from physical ram, why SQL is not using all this memory then ??
  2. thomas New Member

    How big is/are your database(s)? What is your Buffer Manager:Buffer Cache Hit Ratio counter like?
  3. 99redcamaro New Member

    Database is 3GB,
    Buffer Cache Hit Ratio is 99.863
  4. thomas New Member

    That sounds to me like a very healthy cache hit ratio, sql server probably has as much memory as it needs.
  5. evilDBA New Member

    When SQL server is running slowly, there is always one reason of the following list: CPU I/O system (Reads, Writes) Locks You should check what SQL server is waiting for analyzing SQL server latch waiting statistics manually or automatically
  6. MohammedU New Member

    In SQL server 2000, SQL server should take all the available memory when you have not set MAX server memory in your case it should take at 1.8 gb....
    Check what is MAX SERVER MEMORY configured on this server using sp_configure...If it is set to 1GB and the server is dedicated SQL server then change it to 2gb...
  7. thomas New Member

    The OP stated that is was not dedicated - hence 1GB is probably all SQL Server will get. Setting a fixed amount of RAM too high for sql server may compromise other applications on the server.

Share This Page