About the problem of MSSQL occupying too much memory_PHP tutorial

I often see people ask, MSSQL takes up too much memory, and it keeps growing; or it has been set up to use memory, but it does not use that much. What is going on?
First, let’s take a look at how MSSQL uses memory.
The biggest overhead is generally used for data caching. If the memory is enough, it will throw all the used data and the data it thinks you will use into the memory. It will not cache the data with a low hit rate until the memory is insufficient. Clear the data. So generally when we look at statistics io, the physics reads we see are all 0.
The second is the cost of query. Generally speaking, hash join will bring relatively large memory overhead, while the cost of merge join and nested loop is relatively small, and sorting, intermediate tables, and cursors will also be relatively large. of expenses.
So the columns used for association and sorting generally need to have indexes.
The next step 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 to compete for memory, the more data caches, the better. Sometimes we will even forcibly pin some data in the cache. middle. But if there are other applications, although MSSQL will release memory when needed, thread switching and IO waiting will also take time, so performance will be reduced. This way we have to set the maximum memory usage of MSSQL. Configuring the maximum memory usage can be found in the SQL Server properties (Memory tab), or it can be done using sp_configure. If there are no other applications, then do not limit MSSQL's memory usage.
Then let’s look at the query overhead. The lower the overhead, the better, because we can’t benefit from it. On the contrary, using more memory probably means a decrease in query speed. Therefore, we generally want to avoid the use of intermediate tables and cursors, and create indexes on columns that are frequently associated and sorted.

http://www.bkjia.com/PHPjc/631123.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/631123.htmlTechArticleI often see people asking that MSSQL takes up too much memory and keeps growing; or that it has been set up Use memory, but it doesn't use that much, what's going on? ...