MS SQLServer occupies too much memory problem
The company is also SQL Server, DB Server has nearly 2G Memory, and SQLServer's memory usage can always reach around 1.5G, and it is still growing, and the harmful we will recover a few times. Some people have proposed some solutions for this issue:
I often see someone asking, MSSQL occupies too much memory, and constantly grows; or has set up memory, but it is not used so much, how is this? First, let's take a look at how MSSQL uses memory. The biggest overhead is generally used for data cache. If memory is sufficient, it will throw the used data and the data you will use, until the memory is insufficient, the lowest rate is low. Drop. So, when we look at Statistics Io, the Physics Read is 0.
Secondly, the overhead of queries, generally, haveh Join will bring relatively large memory overhead, and Merge Join and Nested Loop have a small overhead, and there are sorting and intermediate tables, and the cursor will have a relatively large overhead. . So the column used to associate and sort us generally requires an index.
Further, it is a relatively small amount of storage of the execution plan, system data. Let's first see the influence of data cache for performance. If there are no other applications in the system to compete for memory, the data cache is usually better, even in some time we will force some data PIN in the cache. But if there is other applications, although MSSQL will release memory when needed, thread switches, IO waits for these work, which will cause performance reduction. This way we must set the maximum memory usage of MSSQL. You can find the maximum use of memory in the SQL Server property (Memory tab), or you can use sp_configure to complete. If there is no other application, then do not limit MSSQL to memory. Then look at the overhead of the inquiry. This overhead is obviously the lower, because we can't get the benefits from it, the more memory, the more memory means a decrease in query speed. So we generally avoid the use of intermediate tables and cursors to establish indexes on columns often associated and sorted.
PS :) I have done it according to the article, harvest is some, but it is not big.
I played Microsoft's 800 technical consultation phone, Microsoft told me that this is basically the problem of SQL Server itself. They don't advocate the release of memory through the app. If he doesn't tell me how to release it.
It seems that this problem basically can't solve it, maybe I can only set up a maximum of memory :) Who has a good way to talk about it. . .
