How to configure SQL Server Minimum and Maximum Memory? Why is it important to configure SQL Server Memory?

a) How to configure SQL Server Minimum and Maximum memory?
You can configure SQL server memory either using T-SQL configuration statement or using management studio. This article illustrates using management studio.
1- Start SQL Server Management studio client
2- Connect to SQL Server instance that you would like to configure memory of
3- Right click on instance and go to properties
4- Click on memory and window will appear showing minimum and maximum memory configuration of SQL Server
5- Configure desired min and max memory and click ok, for this particular operation you don't need to start the sql server services.
Fig 1. How to configure SQL Server Memory


Fig 2. How to configure SQL Server Memory


Fig 3. How to configure SQL Server Memory


Fig 4. How to configure SQL Server Memory


Fig 5. How to configure SQL Server Memory




b) Why is it important to Configure SQL Server memory?
SQL Server is real Hog when it comes to memory, it has tendency to grab all the memory it can, not even caring about Operating system unless operating system is configured to keep certain memory explicitly. This can be very dangerous in production environment as it can bring down the system sometimes referred as memory crash.
Until you restart sql services, sql server will not let memory go so it is important to configure how much memory sql server can from available memory. This is where we configure sql server maximum memory. Depending upon the requirement and memory available you need to figure out how much memory you would like to give SQL Server.
However on other hand, if you are running an application which is competitor of SQL Server in terms of memory hog, you need to make sure SQL Server has atleast the memory where it can perform basic operations and not crash. Hence it is important to configure SQL Server minimum memory. When we configure minimum memory, we are telling SQL Server, no matter what happen, SQL Server is assured to keep this much memory whether any session is running or not.


No comments:

Post a Comment