Configuring memory usage of MS SQL Server
Posted by Ariful Mahmud (M-Files) on 17 December 2019 11:13 AM
Microsoft SQL Server depends on Buffer Pool memory (BPool) for high performance. When data is needed for a query, SQL Server first checks the BPool to determine if the data is already available. If it is, the data in the BPool is used to run the query, which is why performance improves after the first time a query executes.
SQL Server attempts to keep data loaded in the BPool for as long as possible once it has been physically read from disk. Consequently, the server commits additional memory for the new queries. Once the BPool gets full, stale data is flushed from the cache.
By Default, the maximum size of the BPool is the size of physical memory or the max server memory configuration, whichever is smaller. So, in practice SQL Server reserves all the memory physical and virtual memory for the BPool if you do not configure the max server memory setting in the SQL Server.
Hence if other applications are running on the SQL Server machine, it is good to leave at least 2GB of RAM memory to the operating system and these applications. For an example: if the server has 8GB RAM, the max memory allocated for MS SQL Server should be maximum of 6GB.
Use Microsoft SQL Server Management Studio to alter the maximum server memory value:
1. Log in to MS SQL server using MS SQL Server Management Studio
2. Open SQL Server properties:
3. Set the maximum server memory (in MB) on Memory page
4. Restart SQL Server service.