I always prefer setting Max Memory setting for SQL Server 2008/2005 for Production servers which are generally larger systems and are always under memory pressure. If we do not specify the Max Memory Setting for SQL Server then it will use as much memory as it wants and hence will create memory problems for Operating System and other applications running.
If we set Max Server Memory then it prevents the SQL Server Buffer pool from using more than specified amount of memory and leaving the rest of the memory to the operating system and other applications running. Below I am showing the default Memory setting of SQL Server which allows the SQL Server to use all the available memory if OS allows..
I always prefer to set the Max Server Memory to around 80% of the total memory on x64 dedicated database server, only running the database engine. It becomes even more important to configure this parameter if we are enabling “Lock Pages in Memory” for the SQL Service account.
If on the Server we are running other components like SSIS, SSAS or SSRS then we would like to allocate lesser memory for the SQL Server Buffer Pool.
We should also monitor the available memory through Task Manager. Ideally if we have <32 GB RAM then we would like to have around 1GB-2GB available memory in the Task Manager, and if we have more than 32GB RAM then we would like to have around 2GB-4GB memory free.
Below I am showing how we can set the Max Memory setting through TSQL: Iwill be showing to set the Max Memory to 2000MB which is around 2GB.
— Showing Advanced Options
EXEC sp_configure ‘Show Advanced Options’,1;
— Set Max Memory=2000MB (2GB)
EXEC sp_configure ‘max server memory (MB)’,2000;
— Check the current settings
You can also change the Memory settings from GUI. In Object Explorer–> Right Click the Server –> Go to Properties –>If you select Memory tab then you will see the below page where you can adjust the parameters