November 2, 2012

SQL SERVER: Limit memory usage of SQL SERVER

Recently, we have found that SQL SERVER is using most of the memory on the server, due to heavy queries are running on my different teams and it freezes the whole server. Due to time constraint team doesn’t have time to optimize those queries, so we have found a way to configure SQL SERVER to not to use all available memory. We have an option to configure how much memory can be used by SQL SERVER as below:

1. Connect to the SQL SERVER instance for which we need to limit memory usage and click on properties:

SQL SERVER Limit SQL SERVER Memory usage2. Go to “Memory”, where you can see the memory options that can be used by SQL SERVER as below:

SQL SERVER Limit SQL SERVER Memory usage SQL SERVER Properties
By default, SQL SERVER is using maximum server memory, so to resolve the freezing issue, we need to change it as per requirement. So SQL SERVER will use only that much memory and other processes can run in the server.

Let me know your comments.

Reference: Tejas Shah (http://www.SQLYoga.com)

No comments:

Post a Comment