Keeping default max memory 2147483647 MB value in SQL Server, a bad practice?
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
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.