March 23, 2012 at 6:24 am
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 ?
March 23, 2012 at 6:38 am
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
March 23, 2012 at 6:38 am
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
March 23, 2012 at 6:56 am
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 .
March 23, 2012 at 7:57 am
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
March 23, 2012 at 9:52 am
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 .
March 23, 2012 at 10:07 am
The "normal" practice is to not run applications on the same server with a SQL Server.
March 23, 2012 at 10:19 am
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
March 24, 2012 at 7:56 am
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
March 24, 2012 at 10:36 pm
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 ?
March 25, 2012 at 4:22 am
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
March 26, 2012 at 6:32 am
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
March 26, 2012 at 7:06 am
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