SQL Server Performance

SQL2005 Memory

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by pbrown, May 22, 2009.

  1. pbrown New Member

    Hi all. This is my first post. First off, I am not a DBA and my company does not have one on staff.
    I have several SQL servers and trying to figure out some memory issues. The server in question is speced out as follows:
    • HP Proliant DL580 G5
    • Four quad core processors
    • 64 GB DRAM
    • Internal RAID 1 disks for OS.
    • Seperate internal RAID 1 disks for pagefile
    • Dual 4GB HBA for SAN
    • Seperate volumes for data, logs, and tempdb spread accross 48 15K drives in a RAID 1+0 configuration.
    My memory question is this - When looking at performance counters, the "target server memory" and the "total server memory" is the same. I'm under the impression that when this happens that I should add memory to the server. I have the SQL memory set to use all but 4GB of the DRAM. We went from 16GB to 32GB last night. Should we add more?
    The database is about 40GB and there are typically between 250-300 connections.
    Thanks,
    --Patrick
  2. MohammedU New Member

    I don't see any reason to increase the memory when your db is about 40 GB.
    Do you have any performance issues?
    But your memory usage is based on your OS...what is your OS version and edition?
    SQL server will not take all the memory when you start even if you configure the max server memory, it takes the only when necessary...
    http://msdn.microsoft.com/en-us/library/aa366778.aspx
  3. pbrown New Member

    My OS is W2K3 64bit. SQL is 64 bit enterprise.
    I haven't heard of any performance issues lately. We have had problems with blocking. The app vendor is clueless.
    Should I be concerned when the total and target SQL memory is the same? I've heard that if they are then SQL is wanting to use more memory than is available and I should add memory.
    --Patrick
  4. Jorge Cunha New Member

    Hi Patrick,

    I think you are confused about the terms and I confess that can be tricky
    Target Server Memory (Reference to Target Server the server that is controlling others but it reflects the same like Local Server (refer to SQL Agent))
    and
    Total Server Memory
    p.s. I believe that is your question ?
    BR,
    Jorge Cunha
  5. moh_hassan20 New Member

    Your server Memory is more enough for the database.
    Check root problem of blocking using the profiler.
    be sure that all high size table has clustered index(not heap tables).

    what application and connection type (OLEDB /ODBC/ native / other)?
    what min /max memory configuration.?

  6. pbrown New Member

    Clients use a native TCP/IP connection to the database. There are a few ODBC connections for Crystal reporting.
    --Patrick
  7. moh_hassan20 New Member

    Some Blocking problems may due to the setting of connection parameter in development tools, which use autocommit =true , and server is implicit_transaction on (which should be off).
    in that case server send automatically "begin trans" command with any update/insert/delete, and expect commit from client application which may not be sent , and so blocking is created.
    The profiler with blocking template, can help in finding these cases using blocking report.


Share This Page