Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) in the buffer pool used by an instance of SQL Server. By default, SQL Server can change its memory requirements dynamically based on available system resources. When configured with a min server memory setting, the buffer pool does not immediately acquire the amount of memory specified for the min server memory.
To set a fixed amount of memory, please go through the article …
- Launch SQL Server Management Studio by clicking on Start ► ssms
- Connect to the instance you need to configure the memory, if you have multiple instances installed
- In Object Explorer, right-click a server and select Properties.
- Click the Memory node.
- Under Server Memory Options, enter the amount that you want for Minimum server memory andMaximum server memory and click OK. In this article we will assign 2 GB to MS SQL (we have 4 GB in total installed on the server)
- Restart the SQL Server service from services console for the changes to take effect, Start ►services.msc
- Right-click on the SQL Server service and select Restart
The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 megabytes (MB) i.e. 2097151 gigabytes (GB) or 2048 terabyte (TB). The minimum amount of memory you can specify for max server memory is 16 MB.