SQL Server Performance

Target Server Memory vs. Maximum Server Memory

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by DBADave, Sep 17, 2007.

  1. DBADave New Member

    I've read and noticed SQL 2005 handles memory differently then 2000. In 2000 if I told a server it had 6GB to use, it allocated the memory. In 2005 I have one 32-bit server with 6GB of memory and one 64-bit server with 32 GB. If Target Server Memory is the amount of memory SQL Server would like to have, how does that correspond to Maximum Server Memory? Also, how is Target Server Memory determined?
    32-bit
    Physical Memory = 8GB
    Target Server Memory = 6GB (Willing to consume)
    Total Server Memory = 690MB (Currently consuming)
    Minimum Server Memory = 2GB
    Maximum Server Memory = 6GB
    For the 32-bit server the Target Server Memory matches Maximum Server Memory
    64-bit
    Physical Memory = 32GB
    Target Server Memory = 28GB (Willing to consume)
    Total Server Memory = 397MB (Currently consuming)
    Minimum Server Memory = 4GB
    Maximum Server Memory = 30GB
    For the 64-bit server the Target Server Memory is less then the Maximum Server Memory
    Lock Pages in Memory is set for the service account. Neither server above has yet to be released to production and only the 32-bit server has any users. In 2000 when SQL Server started I could count on it using about 1.72GB of memory immediately. Seeing the servers above consume only only 690MB and 397MB has me concerned. Is this just a case of SQL Server 2005 handling memory better then 2000?
    Thanks, Dave
  2. thomas New Member

    Maybe a stupid question, but are you stressing the 32 bit server? It'll only take the memory when it needs it (even if you've set a minimum). Try running checkdb on an 8+ GB database and see what happens.
  3. DBADave New Member

    I ran SQLIOSim, but saw little change in memory consumption. I'll try running some CHECKDB to see what happens. It sounds like with 2005 SQL Server will only grab the memory if it needs it. That will take some getting used to for me since I am used to seeing SQL Server grab the memory immediately.
    Any idea why am I seeing some pagefile access if I have so much free physical memory. I thought the pagefile is only to be accessed if the OS determine not enough physical memory exists to service a request. I have three new servers yet to be released to production and each show some degree of pagefile access.
    Dave
  4. TRACEYSQL New Member

    hi there i was wondering same thing when i looked at total server memory i have 6 gig and the target is 52 Gig. Using 64 bit SQL 2005
    Also getting
    The messages are appearing when the working set of SQL Server 2005 process reaches 50 percent of the memory that is committed to the process.
    Did you get any of these messages
  5. satya Moderator

Share This Page