6

Based on that msdn page : server memory settings it's a good practice to keep the default values for SQL-Servers min and max memory to keep it dynamic...

From what I learned through tutorials you should always define a max value, about min I haven't heard much. I am monitoring my pagefile using simple old perfmon and I realised that seldomly diskswapping is happenning and once it nearly caused a server crash.

Can that be related to have the default max memory setting in SQL Server? The databases are around 150 GB and Memory is 48GB, there is no other application running on that machine.

Also, why should you have a min value? I know that a too low value can prevent the SQL-Server from starting and I know that SQL-Server keeps stuff in memory so doesn't try to release it's cache.

I am using SQL-Server 2012, thank you in advance!

5

Some details to clear things up

Based on that msdn page : server memory settings it's a good practice to keep the default values for SQL-Servers min and max memory to keep it dynamic.

No, it's not written it is a good practice it says it's recommended and I am sure MS books online cannot write it as good, this is because of fact that environment varies and what is good for one environment might not suit other. Its mostly good to have restriction on something which is heavily consumed although managed efficiently. I consider a good practice to define max server memory on system because it will restrict buffer pool and will tell SQL Server how much max a buffer pool can grow although SQL Server can consume memory outside buffer pool/max server memory setting if it heavily uses Third party DLL's extended stored procs and Linked servers.

Page file is used by Windows to hold temporary data which is swapped in and out of physical memory in order to provide a larger virtual memory set. Page file largely depends on how much memory OS is committing and changes accordingly as per min and max value set. if you want to monitor page file you must rely on perfmon counters. Please read this MSDN Blog

Memory: Committed Bytes --Number of bytes of virtual memory that has been committed. This does not necessarily represent page file usage - it represents the amount of page file space that would be used if the process was completely made nonresident

Memory: Commit Limit-- Number of bytes of virtual memory that can be committed without having to extend the paging files.

Paging File: % Usage-- Percentage of the paging file committed

Paging File: % Usage Peak-- Highest percentage of the paging file committed

Also, why should you have a min value? I know that a too low value can prevent the SQL-Server from starting and I know that SQL-Server keeps stuff in memory so doesn't try to release it's cache.

Min Server memory is minimum amount of memory available to the SQL Server Memory Manager for an instance of SQL Server. This comes into active picture when windows is facing memory pressure in such case it will raise Low Memry Notification Flag. SQLOS will respond to it, it would ask SQL Server to trim its memory consumption and free its cache then SQL Server will non preemptively trim down till its min server memory value. If pressure is too high SQL Server might not be able to react fast and then SQL Server process would be paged out. Remember min server memory does not say that when SQL Server starts it would minimum take this much memory if not required, SQL Server will start up consuming much less than min server memory.

Other use of Min server memory is if you do not want SQL Server to be paged out to disk because of some misbehaving OS/Third party driver. You set max server and min server memory to almost same value and give SQL Server service account Locked pages in memory privilege and in this case even if OS faces memory crunch SQL Server would max trim down to min server and if OS memory crunch is still there OS process would be paged out and would become extremely slow . But this is bad thing to do and I would not recommend

  • I have to disagree with you -- "Page file is an virtual area which act as dump pad. Windows would use this to write information when system crashes." -- a bit confusing statement. A page file is an extension to RAM. When a process is not being used, it gets pushed out to the page file (in simple words!) The stop errors are handled by the OS memory dumps including what was in the page file since it's a part of RAM. – DenisT Nov 3 '14 at 22:00
  • I will amend it to give more accurate meaning. yes its kind of swapping area for storing temp information. It also acts as place where information will be stored in case of system crash. – Shanky Nov 3 '14 at 23:05
2

The MIN value is useful when you have a server with multiple instances. You can use it to prioritise memory usage per instance. But mainly it is to guarantee that SQL with have at least that much memory to work with. This stops people running something on the server and stealing all the memory.

The default setting basically leaves memory allocation to SQL Server but it's best practice to set a static value so that there is never an issue of SQL taking memory away from other processes or the OS. This is a must if you are running SSIS, SSAS, SSRS or any other serverice / application on the same server.vSo your setting needs to leave enough memory for the OS + services + applications running on the server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.