Memory is one among the resources forming the performance triangle—CPU and storage being the other two. If one is hit, the other two take the load to try to bring the performance to acceptable levels, but there’s always the trade-off. Whatever transactions cannot be committed to the memory they would be forwarded to the disk subsystem by SQL Server. This causes a performance bottleneck. Therefore, the wait statistics can help identify performance issues on a SQL Server.
In this article, the following topics are discussed:
- Understanding internals of SQL Server memory setting and configuration
- The SQL Server memory and its impact on the database and application performance
- Discuss various SQL Server components that contribute to the memory usage
- Best practices and recommendation for memory sizing
- Multi-server memory report
- And more…
Memory management internals
SQL Server has a Memory Management Unit that performs automated dynamic memory management based on the workload of the system. This memory is the volatile space that’s critical to today’s Business – Tech needs, the right-sizing of which is vital to the optimal performance of the applications.
However, we all know that when setting up the server, the sizing contains some default values. in some cases, we soon find out that SQL Server uses almost all of the memory on the server, even though there’s no visible activity on the databases, bringing in the questions: Are the default values incorrect? If so, what should be the right size?
Memory Management on SQL Server works on the Fill-and-Flush algorithm. The default values do not restrict the memory consumption from growing unless there’s a request from the Operating System.
The sizing depends on various components of the system—in many cases, setting it between 70% and 80% is a good starting point. Then, you should also monitor it to see what else you may be missing and if you should tweak the setting. If you have other services on the SQL Server (you really shouldn’t), you may need to leave behind more, especially if these services are memory hogs. Consider revisiting the memory setting of the SQL instance in any of the following scenarios:
- Unresponsiveness of the Operating System
- Application exhaustion
- Backup operations that require large memory buffers
- In-Memory Optimized objects
- Column store indexes, since they require large volumes of memory to perform index maintenances.
The memory setting on SQL Server is pretty straightforward. You can change the value using sp_configure or SSMS GUI. This is an online option but remember that setting or resetting these values may cause some of the internal cache objects to reshuffle, which will leave the system running slightly slower.
further reading
Importance of memory setting in SQL Server
Happy Learning!
