How much memory should be allocated to SQL Server 2008 R2 64 Bit Enterprise

  • Good Day ,

    We are running the database system on a virtual server and I am not sure what the optimum memory allocation to SQL Server 2008R2 64 bit enterpsie should be ? E.g. if a server has 10 gig of memory, should SQL server be allocated only 4 gig ? This sounds a bit thin . Any ideas ?

  • It depends... of course. If you don't have anything else running on the server, I'd start with 8GB for SQL Server and 2GB for the operating system.

    John

  • For 10GB of memory on a dedicated SQL Server, I'd probably set max memory to 7 or 8 GB.

    See chapter 4 http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thsnk you for your comments. I mus also add the application is running in its own memory space on the server and it is using 1.5 gig of memory .

  • If there is other stuff running on the server, then you need to account for that before setting SQL's memory. So if the app is using 1.5 GB memory (round up to 2 to allow growth), then you probably want to set SQL Server to 5-6 GB max memory.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thank you for the feedback. We eventually configured SQL Server to use only 4 gig as they wanted 2.5 gig of memory to be free . Well I don't know if this setting could cause more harm than good as it is a high volume online transaction system . I am of the old school and I am not in favour of this setting. But we'll have to wait and see .The question now arises : What now SQL Server for the future in general ? Is this practice of giving SQL Server only the scraps just to keep applications running going to be the future ? I thought the practice of applications running in their own resource space on a db server is a thing of the past . Thanks for your feedback. It is much appreciated .

  • The "normal" practice is to not run applications on the same server with a SQL Server.

  • It is strongly recommended to always set max server memory, especially when the server is not dedicated to SQL Server. 2.5 GB free is a little large, the usual recommendation is 500 - 1000 MB free.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I would recommend moving the application off that server onto it's own VM. This way you can dedicate the resources on each VM appropriately and they won't conflict.

    Since this is a VM environment I don't see any reason why that couldn't be done. If you only had a physical server it would make more sense, but not in a VM environment.

    I will setup single servers with both SQL and the application on a single guest - but only for applications that are department level applications. For Enterprise level, or highly transactional systems I split the systems out and use dedicated hardware for large database systems.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Thank you all for the feedback. I have one more question though: If I were to set the max server memory for SQL Server to 6 gigs it shows on Task Manager SQL Server is using this amount of memory . Now if you were to set it to use 9 gig out of 10 gigs the server administrators are seriously concerned and raise the alarms and eventually you are forced by higher powers to reduce your settings . Any ideas ?

  • Lian (3/24/2012)


    Now if you were to set it to use 9 gig out of 10 gigs the server administrators are seriously concerned and raise the alarms and eventually you are forced by higher powers to reduce your settings . Any ideas ?

    Tell the higher powers (politely) that they don't know what they're talking about.

    There is no benefit to memory sitting idle. If a SQL Server has 4GB of memory free, that's 4GB of memory (and the associated capital investment) going completely to waste, money mis-spent.

    That said, if the server has 10 GB of memory you wouldn't want to set SQL's memory limit to 9GB. Too high. 7 or 8 is a safe figure, you can always increase later if there's still free memory on the server.

    The OS needs memory and there are memory allocations outside of the SQL buffer pool that don't count against max server memory. 9GB out of 10 total is risking OS starvation problems, and that's assuming the server is dedicated.

    Have a read through chapter 4 of this http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/, it talks about the signs of memory pressure and gives you something you can use to push back against the 'leave the memory free' types

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The memory counter SQL Server Target Memory shows SQL's assessment of what would be the ideal setting for Max Memory for the given workload.

    If the target memory is higher than your current Max Memory, then it is very likely that SQL Server performance would improve if the exta memory was made available.

    However, as Gail has said, you must not starve the OS of memory. You need to work out all the demands on memory (OS, SQL, Applications, etc), and work out what requirements are mandatory for the function to work and what merely affect performance. You often have to limit performance-improving memory use to satisfy mandatory use. Most of SQL Memory falls into the Performance category.

    If you work out that for best performance you need more memory than is on the server, then you have the start of a Busines Case to get the memory. If you can show that SLA measures are being breached by poor performance caused by lack of memory than you have a very good case to get the exta memory.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • GilaMonster (3/25/2012)


    Lian (3/24/2012)


    Now if you were to set it to use 9 gig out of 10 gigs the server administrators are seriously concerned and raise the alarms and eventually you are forced by higher powers to reduce your settings . Any ideas ?

    Tell the higher powers (politely) that they don't know what they're talking about.

    There is no benefit to memory sitting idle. If a SQL Server has 4GB of memory free, that's 4GB of memory (and the associated capital investment) going completely to waste, money mis-spent.

    That said, if the server has 10 GB of memory you wouldn't want to set SQL's memory limit to 9GB. Too high. 7 or 8 is a safe figure, you can always increase later if there's still free memory on the server.

    The OS needs memory and there are memory allocations outside of the SQL buffer pool that don't count against max server memory. 9GB out of 10 total is risking OS starvation problems, and that's assuming the server is dedicated.

    Have a read through chapter 4 of this http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/, it talks about the signs of memory pressure and gives you something you can use to push back against the 'leave the memory free' types

    Or you can hire me and I will UNpolitely tell the higher powers they have their heads up their backsides! :w00t:

    Seriously, with the app running on the same machine I would also go with 5 or maybe 6GB for sql max mem and still monitor for memory pressure. Note that the max memory setting only limits the BUFFER POOL. There are other memory buckets used by sql server that are not constrained by that limit.

    Given that you are on a VM you also need those higher powers to be monitoring the host for memory overutilization, ballooning and other VERY common issues that come with running SQL Server in a virtual environment. I actually expect suboptimal configurations given some of the questions and statements you have made on this thread.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply