SQL Server Performance

SQL server service memory

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by GopalMynam, Apr 14, 2009.

  1. GopalMynam New Member

    I have the following environment:
    Win 2003 server x64 (Ent) with SQL server 2006 x64 (Ent), two nodes (db1 & db2) are clustered with one instance in each node.
    Node1: SQL instance
    RAM = 16 GB, Max server memory = 7 GB, Page file size = 24 GB
    The sql server service memory is continuously increases and reaches max 7 GB in about a day and remains there and the system becomes slow.
    The page file usage reaches about 8 GB and remains there.
    my question is is it normal to have sql server service to take 7 GB memory. The application runs processes on every 6 mins schedules. This process puts locks on different resources (page, index, table). after the process is run, the locks are removed.
    Do I need to change any configuration settings?
  2. SmartDBA New Member

    Yes, SQL Server 2005 use the memory which is available depending upon its requirement unlike in previous versions. In your case, you have allocated 7 GB of memory for SQL Server which means one you restart SQL Server it takes about a day to reach 7 GB which is physically available. However, since your SQL Server requires more memoy, which is not available physically it starts using Page File. Thats the reason you see pagefile size increasing to as huge as 24 GB. In order to resolve this issue you need to increase the RAM on your server.
    You can use the counter mentioned in the following thread http://www.mytechmantra.com/forums/index.php?topic=26.0 for you memory requirement analysis.

Share This Page