SQL Server memory usage Solution

Some netizens often ask, SQL Server occupies too much memory and continues to grow; or it has been set to use memory, but it does not use that much, what is this?

Next, let's take a look at how SQL Server 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 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 other applications require ms SQL to release the memory, thread switching and I/O wait are also time-consuming, resulting in performance degradation. In this way, we must set the maximum memory usage of ms SQL. 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 ms SQL.

Finally, 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.