The SQL server memory has two basic management methods: dynamic allocation and static allocation.
The amount of memory that can be used by the control program. Dynamic allocation allows the administrator to declare the size of a piece of memory. Considering its actual usage, the SQL Server can allocate the maximum amount of memory to it, and (theoretically) release the memory when it is not used. Static allocation creates a fixed memory space for SQL Server.
By default, SQL Server is set to dynamically allocate all available physical memory to the running computer. Many administrators have noticed that when the SQL Server memory is gradually exhausted over time, it may be caused by a fault or a memory vulnerability, but this program is designed to be like this. SQL Server is to run on the computer whenever possible, and thus use all available memory to achieve its best performance. If SQL Server runs on an independent machine, let it allocate and release the required memory.
In a small commercial Server machine, SQL may run simultaneously with other programs, such as IIS. The administrator may try to set it so that SQL Server runs in a fixed memory, the purpose is to control whether it occupies the shared memory. However, this is not necessarily a result. On the one hand, setting the maximum memory is too low, and the SQL Server is not allocated enough available memory to be used as a cache for similar transaction logs or query execution, all of which are hard to do. The only way to get the memory required for the SQL Server to execute operations is to swap out other pages, which is a slow process.
There are many ways to calculate the best memory allocation. If you have predictable User loads, allocate them to them based on the maximum number of users required. Microsoft recommends that at least 4 MB be used as the maximum dynamic space, which has become a possible rule. If your user load has a large variation scope-in the following cases, when you connect to the public Internet through the IIS front-end to support your database services-real-time statistics will be more helpful than just making guesses. During peak hours, performance figures such as SQL Server's high-speed cache hit rate and page missing rate per second are collected. If the data indicates that SQL Server is performing a large number of exchanges, increase the maximum memory space until the swap is gradually reduced. One or more exchanges per second have disadvantages.
Another option is to make the "reserve physical memory for SQL Server" option available, which can prevent SQL Server from switching out the memory allocated to it, even when other applications can use it. This can be called a double-edged sword: it can not only greatly improve the performance, but also bring greater performance damage. In systems with many RAM users (1 GB or more), this is worth a try, but when other critical processes may suddenly need a large amount of memory, this method should not be used. (And if necessary, SQL Server may be forced to discard some of its own memory ). If SQL Server runs on an independent machine, it is worthwhile to use this method to optimize performance.
