locked
SQL Server Memory Calculation RRS feed

  • Question

  • Hi All,

    first, let me introduce my self. my name is Leon, i'm a DBA production. i want ask about SQL Server memory sizing best practice.how to calculate the size of memory for a few user in a system? how to obtain the rasio between size of memory and count of user in a system ? is this ratio can be used as a baseline for memory sizing ??

    thank you for help.

    best regards,
    Leon

    Wednesday, March 31, 2010 2:21 AM

Answers

  • Ah, I think you're on the wrong track. SQL Server allocates a fairly small block of memory (about 18K) for each connection (not each user as a "user" can open any number of connections). Each connection then runs queries (which are cached in memory) and fetches rows (which are cached as well). As more users are added, they often use the same cached procedures and some of the same data pages (in a typical OLTP system) so the system actually gets faster as more users are added (on a per/user basis).

    hth


    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Thursday, April 1, 2010 6:46 PM
  • It depends. Let's assume you have a dedicated SQL Server. This means the system is not sharing memory with user programs (Microsoft Office), a print server or even Reporting Services or other similar services. In this case, SQL Server will consume the amount of memory assigned to it. As you probably know, this is set in SSMS in the Server Properties Memory dialog. Here you can set the maximum memory (which defaults to 2,147,483,647 MB) as well as the minimum memory allocated to the server. If you're trying to get good performance out of SQL Server, leave these settings alone as it maximizes the amount of RAM cache for procedures and data (which means less disk IO and query re-compiles).

    If, however, you're working with an "imbedded" server that has to share space with other programs, then you'll have to adjust the min and max settings to help other applications co-exist with SQL Server.

    hth 


    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Monday, April 5, 2010 4:36 PM

All replies

  • Which version of SQL Server are you using?

    If it is 2008

    Refer the section "To get an idea of which individual processes are taking up memory, use the following query:" to get the users and the correspoding memory consumption.

    http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1265214,00.html

    But remember this DMV shows only the current active process.

    I may be wrong, but I feel that sizing memory based on number of users is not a good approach as there is no guarantee that users will be consuming same size of memory everytime.

    Wednesday, March 31, 2010 1:35 PM
  • Hi Suresh,

    thank  you for your reply and your kb. i use SQL Server 2008 Enterprise Edition..

    hmm,, if we can get the memory consumption for users,, would not this can be used for baseline of memory sizing ??

    and if you feel that "feel that sizing memory based on number of users is not a good approach as there is no guarantee", so do you have the best practice for SQL Memory Sizing ??

    thank you for your help.

    best regards,
    Leo

    • Proposed as answer by William Vaughn Thursday, April 1, 2010 6:46 PM
    • Unproposed as answer by William Vaughn Thursday, April 1, 2010 6:46 PM
    Thursday, April 1, 2010 1:53 PM
  • Ah, I think you're on the wrong track. SQL Server allocates a fairly small block of memory (about 18K) for each connection (not each user as a "user" can open any number of connections). Each connection then runs queries (which are cached in memory) and fetches rows (which are cached as well). As more users are added, they often use the same cached procedures and some of the same data pages (in a typical OLTP system) so the system actually gets faster as more users are added (on a per/user basis).

    hth


    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Thursday, April 1, 2010 6:46 PM
  • Hi William,

    Thank you for your correction. so, user memory consumed can't be baseline for database server memory sizing ??

    if yes, what is the best practice for database server memory sizing ??

    thank you for your help

    best regards,
    Leon

    Monday, April 5, 2010 1:56 AM
  • It depends. Let's assume you have a dedicated SQL Server. This means the system is not sharing memory with user programs (Microsoft Office), a print server or even Reporting Services or other similar services. In this case, SQL Server will consume the amount of memory assigned to it. As you probably know, this is set in SSMS in the Server Properties Memory dialog. Here you can set the maximum memory (which defaults to 2,147,483,647 MB) as well as the minimum memory allocated to the server. If you're trying to get good performance out of SQL Server, leave these settings alone as it maximizes the amount of RAM cache for procedures and data (which means less disk IO and query re-compiles).

    If, however, you're working with an "imbedded" server that has to share space with other programs, then you'll have to adjust the min and max settings to help other applications co-exist with SQL Server.

    hth 


    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Monday, April 5, 2010 4:36 PM
  • Hi William,

    Thank you for your explanation... yes, in my case, my database server only dedicated for SQL Server. and now that server has become a production server. i've set the memory usage for this database server, and now it's running well and normally .. :)

    now, i will use that server as a baseline for memory sizing for a new databse server. but i don't know how is the best practice for this memory sizing. the man who doing this before , already resigned and he didn't leave any documents. can you help me ??

    thank you for your help.

    best regards,
    Leon

    Tuesday, April 6, 2010 2:04 AM
  • The way you configure the memory varies from one environment to another. We have to take lot of factors into consideration.

     for ex:  Dedicated or Shared.

                32 bit or 64 bit

                Amount of Ram the machine has, etc.

     

    For a machine with 8 GB RAM, You could set the Max Memory setting to 6 GB,leaving around 2 GB for the OS.

     

    If you do not set an upper limit ,then on Systems with heavy load, the OS may be starved of memory.

     

     

     

     

     

     

     

    Tuesday, April 6, 2010 3:17 AM
  • Hi Karthik,

    Thank you for your info.. but my topic is how to determine memory size for our new database server based on the old one or based on the application that will run on that server..

    whether to do the stress test or maybe you have another way to determine the memory..

    thank you for your help.

    best regards,
    Leon

    Tuesday, April 6, 2010 6:59 AM