MS SQL 2008 Memory Usage

SQL Server is designed to use as much as memory possible for its SQL performance.

Which is good for its performance but admin may consider to limit its memory usage for other installed programs.

Follow below instruction to limit memory usage of MS SQL server.



sp_configure

- To see the SQL Server configuration



sp_configure 'max server memory (MB)'

- To see the Max Server Memory configuration only



sp_configure 'max server memory (MB)', xxxxx;
reconfigure with override;

- To set the new max memory value and update
- For example sp_configure 'max server memory (MB)', 2000 will set max memory usage to 2GB







85% of Physical Memory is used all time.




I used max memory 2000 (2GB), you can see memory usage is drastically dropped and freed up Free memory (blue).





Here I used max memory to 20,000 (20GB), memory usage is now 60GB freed up 10-12GB of memory which is where I wanted it to be.






Comments

Popular Posts