How to Limit SQL's Memory usage

If you are working with a Dynamics Ax 2012, you would need SQL 2008/2012 to have the database in it. And if you have a Development or a Test environment, you tend to put both SQL and Dynamics Ax in the same Machine. And that is when this post can help you.

I have Dev machine in my local box along side Dynamics Ax 2012 and many other applications. And even though I am not using Major applications I see that the Memory usage of my box goes real high. And I understood that this is because of Microsoft SQL.
Microsoft SQL tends to eat up as much as memory it can get whenever memory is free, and thereby, causing less memory for other applications when needed.

Solution for that would be to limit the Memory usage of SQL to a certain amount based on your usage of SQL. I use SQL only to store Dynamics Ax 2012's data and the memory required for SQL to manage the transactions to and forth Dynamics Ax isn't much. So I limit the memory usage of SQL server to 1024 MB and thereby increase the performance of my Dynamics Ax 2012.

You can limit SQL's memory usage by following below steps

1. Open your SQL Server with Administrator permissions (Right click > Run as Administration)
2. Connect to your SQL instance
3. On the SQL connection node > Right click > Select properties
4. In the opened "Server properties" window Select Memory from the left pane
5. Enter desired amount of memory (in MB) as the limitation for SQL Server to use.
   
6. Click Ok to apply and you are good to go.

Result of this immediately I clicked Ok button is shown as below. I didn't open any other application, but for SQL Server and the memory usage will dropped to pretty reasonable amount.

Hope this helps !!