Solve the problem of high memory usage of Microsoft SQL Server

I often see some people ask, MSSQL occupies too much memory and continues to grow; or it has been set to use memory, but it does not use so much. What is this? First, let's see how MSSQL uses memory. The biggest overhead is generally used for data caching. If the memory is sufficient, it will use the used data and think you will use it.

I often see some people ask, MSSQL occupies too much memory and continues to grow; or it has been set to use memory, but it does not use so much. What is this? First, let's see how MSSQL uses memory. The biggest overhead is generally used for data caching. If the memory is sufficient, it will use the used data and think you will use it.

I often see some people ask, MSSQL occupies too much memory and continues to grow; or it has been set to use memory, but it does not use so much. What is this?

First, let's see how MSSQL uses memory.

The biggest overhead is generally used for data caching. If the memory is sufficient, it will throw the used data and the data you think you will use into the memory until the memory is insufficient, to clear data with a low hit rate. So when we look at statistics io, the physics read we see is 0.

The second is the query overhead. Generally speaking, hash join brings about a relatively large memory overhead, while merge join and nested loop have relatively low overhead, there are also sorting, intermediate tables, and cursors, which also have high overhead.

Therefore, the columns used for association and sorting generally require indexes.

The second is the storage of execution plans and system data, which are relatively small.

Let's first look at the impact of data caching on performance. If there are no other applications in the system competing for memory, the more data caching, the better, sometimes we may force some data pin in the cache. However, if there are other applications, although MSSQL will release the memory when needed, it also takes time to switch the thread and wait for IO, which will lead to performance degradation. In this way, you must set the maximum memory usage of MSSQL. You can find the maximum memory usage configuration in the SQL Server properties (memory tab), or use sp_configure to complete the configuration. If there are no other applications, do not limit the memory usage of MSSQL.

Then let's look at the query overhead. The lower the overhead, the better, because we cannot get the benefit. On the contrary, the more memory used, the lower the query speed. Therefore, we should avoid using intermediate tables and cursors and create indexes on columns that are often associated and sorted.