What are the correct settings for min-memory and max-memory in this use case?
The server has 8GB ram, dual Intel Xeon processors, running Windows Server 2008 R2 / Sql Server 2008 Standard Edition. It's running several databases ranging from 30GB – 5GB in size.
Originally the memory usage was set to the default settings (min=0 max=2,147,483,647). On these settings most of the memory usage was taken up by sqlservr.exe and the server would eventually need to be restarted every day or two. It would run normally at first but within a day start to timeout on simple operations like looking up a record using the primary key.
I have changed min=4,096 and max=6,144. This results in only 1.4GB memory usage. However now all four cpus are running at 50-60% cpu usage constantly. Tasks are taking roughly 1/3rd longer to execute, although the server is much more stable.
Best Answer
Looking at your RAM availablity for this particular server and you are running several databases ranging from 30GB to 5 GB, you definitely need more RAM on this server.
You have not mentioned that this is a stand alone instance or this server is having more than one instance of sql server running.
Your MAX Memory settings seems OK for a server having 8GB RAM. See these suggested best practice settings from Glenn Berry.
I would highly recommend you to do a baseline of your environment using below PERFMON counters to get a good value of your memory configuration :
Total Server Memory: Amount of memory currently allocated to Buffer Pool and not the total amount of memory to SQL Server
Target Server memory: Ideal Size of the buffer pool corresponding to max memory for the instance.
Note: If Total Server Memory > Target Server memory, then it suggests memory pressure.
Below script will help you find LOW or HIGH Memory notifications from
sys.dm_os_ring_buffers
- system health session :Some good references :