SQL Server memory reservation

Question

Hi all,

I first want to say that I'm not really familiar with SQL or managing a SQL servers, but we have a SQL server that keeps crashing like 3 or 4 times a week. The error say's that the connection from our other server to our SQL is lost.
And I really want to solve this problem but because of the lack of knowledge from the SQL I was hoping with a topic that I can resolve this issue or get in the right direction.

Our SQL server has the current hardware configuration:
- Its running in a VMware environment
- Microsoft Windows Server 2008 R2 (64bit)
- 4 CPU's (Xeon X560 @ 2.67GHz)
- 10GB memory

The SQL server has 10GB memory which is assigned to the SQL management automatically, but this means that other applications that are running in the server can not request memory since its already been taken by the SQL management.
So I was wondering is it smart to give the server for example 10GB but let the SQL management only take 80% from the 10GB, so this means 8GB.
This gives the server room for other request that are coming true so that the server can handle them without crashing.

I hope I have made my self clear and that with the help from you volunteers to make me realize of this is a good idea or not.

Thank you all for already ready this.

Kind regards,
Martijn

Answer 1

Hi,

If your SQL server has a total of 10GB of memory and you are running other processes how much memory do the non-SQL processes ideally need?

You may want to try limiting the SQL buffer cache to 6GB thereby allowing 4GB for Windows, the other applications processes and non buffer SQL requirements. You can then monitor your system to see if you need to lower this value or you have free memory that you can use by increasing it.

http://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/

http://sqlserverperformance.wordpress.com/2009/10/29/suggested-max-memory-settings-for-sql-server-20052008/

EXEC  sp_configure ‘Show Advanced Options’,1;
GO
RECONFIGURE;
GO
– Set max server memory to 6GB 
EXEC  sp_configure ‘max server memory (MB)’,6144;
GO
RECONFIGURE;
GO
-- Check values
EXEC sp_configure;
EXEC  sp_configure ‘Show Advanced Options’,0;
GO
RECONFIGURE;
GO

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog, Twitter or Hire Me.
Please click the Mark as Answer button if a post solves your problem!

Answer 2

Dear Sean,

I was forget to say thank you for your explantion and good help on this.
I have set the option like shown above but this didn't resolve the issue so I'm now trying to find the solution with technical support from VMware to find the desired solution.

Aldo I need to say this was also a part of the solution, so there for thank you.

Kind regards,
Martijn