By Default, Microsoft SQL server 2008 R2 (or later) will allocate memory for database caching dynamically, unlike MySQL server, which requires that the innodb_buffer_pool_size parameter in the MySQL configuration file (my.cnf on Linux/OVA and my.ini on Windows) file be set to a fixed amount. However, in the event that MS SQL is utilizing all availiable memory on the server due to trying to cache Databases that are larger than the ammout of memory availiable, the cache settings can be adjusted to use a minimum and maximum amount of server memory.
To Adjust MS SQL Server Memory Utilization:
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.
Step 1) Log into MS SQL Server Studio
Step 2) In Object Explorer, right-click a server and select Properties.
Step 3) Click the Memory node
Step 4) Under Server Memory Options, enter the amount that you want for Minimum server memory and Maximum server memory.
Use the default settings to allow SQL Server to change its memory requirements dynamically based on available system resources. The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 megabytes (MB), or roughly 2048 Terabytes (TB). Change the max server memory setting to an amount appropriate to the memory available on the server (Note- the minimum amount of memory you can specify for max server memory is 16 MB).
