Capacity Planning - Sizing Memory

Physical memory is one of the important hardware components for a database server. This is because of the fact that, adequate physical memory decreases the physical IO for the server by reusing as much as the data and query plans that exist on the cache.

SQL Server Memory Requirements
SQL Server does dynamic memory allocation. This means SQL Server will allocate whatever memory left on the server that is not being used for its functioning. Whenever any application or the operating system requires additional memory, SQL Server releases memory for their use.
Different components of SQL Server require physical memory for their functioning. The following sections describe the components within SQL Server which are the main users of physical memory.
Buffer Cache Requirements
SQL Server buffer cache is the area in the physical memory where the data pages and index pages are cached. The buffer cache is the largest consumer of physical memory in SQL Server. The dynamic allocation and de-allocation of memory by SQL Server is done mostly by adjusting the size of the buffer pool. The server should be provided with adequate memory to ensure a very high cache hit ratio and the amount of physical IO be reduced to a very minimum level. A decrease in the buffer cache size increases physical IO activities.
Procedure Cache Requirements
Procedure Cache is the area in physical memory where SQL Server caches the compiled execution plans for queries and stored procedures. This is the second largest consumer of physical memory in SQL Server. A healthy size of procedure cache is required to reduce the number of query compilations and optimizations. A decrease in the size of procedure cache increases recompilations which in turn increases processor overhead.
Connection Context Requirements
Each user connection to SQL Server requires a certain amount of memory for handling the user session. This comes around an average of 500KB per connection. Hence, larger the set of concurrent users accessing the database, larger will be the amount of memory required to support the connections.
Other Requirements
There are other components in SQL Server that require memory. These include the lock manager etc.
The details on SQL Server memory allocation can be obtained by the command “DBCC MEMORYSTATUS”
See the hardware comparison details for different SQL Server editions.

Sizing Memory through Load Testing
The best method to estimate the physical memory requirements for a database application infrastructure is through load testing.
We need to do the simulation for the application load with the maximum number of concurrent users and transactions possible, and have to take the memory consumption. Physical memory has to be allocated enough to avoid any memory bottleneck.
A decrease in memory affects the whole system. Memory deficiency increases cache misses and paging, paging increases physical IO and these all increase the processor utilization.

Sizing Memory through Workload Analysis
In all cases, its will not be possible to create the infrastructure prototype and do simulations. In such cases, memory sizing can be carried out by manually doing a workload analysis and calculating the memory requirements by the different components of the server infrastructure.
We can have an example of a sales database application that experiences an annual user accounts growth of 15% and sales data growth of 20%. The following table shows a rough estimation of the memory requirements for procedure cache, user connections etc, and how that will affect the buffer cache.





This is just a rough estimation, assuming that one user connection requires around 500KB and the SQL Server, OS program components together require around 1GB. Similarly, we can do accurate calculations and project the memory requirements.

With this, I am concluding the current Capacity Planning series. I will be demonstrating on a few tools, trends etc in some future posts.