Often see someone ask, MSSQL occupied too much memory, but also constantly grow, or have set up the use of memory, but it does not use so much, what is the matter?
First, let's take a look at how MSSQL uses memory.
The biggest overhead is typically used for data caching, and if enough memory is available, it will throw the used data and the data that you would like to use in memory, until the memory is low, and the data that is less than the hit is cleared. So generally when we look at statistics IO, we see that the physics read is 0.
The second is the cost of the query, generally, the hash join will bring a relatively large memory overhead, and the merge join and nested loop overhead is relatively small, there are sorting and intermediate tables, cursors will have a relatively large overhead.
Therefore, indexes are generally required on the columns used for correlation and sorting.
Second is the implementation of the plan, the storage of system data, these are relatively small.
Let's take a look at the performance impact of data caching, and if there are no other applications competing for memory in the system, the data cache is generally as good as possible, and sometimes we force some data pins in the cache. However, if there are other applications, while MSSQL frees up memory when needed, thread switching, io waiting for these tasks also takes time, so it can cause performance degradation. This way we must set the maximum memory usage for MSSQL. You can find a place to configure the maximum memory usage in the SQL Server properties (Memory tab), or you can use sp_configure to do so. If there are no other applications, then do not limit the use of MSSQL to the memory.
Then look at the cost of the query, which is obviously the lower the better, because we can not benefit from it, on the contrary, the more memory used means that the query speed is reduced. So we generally avoid the use of intermediate tables and cursors, and build indexes on columns that are often associated and sorted.
http://www.bkjia.com/PHPjc/631123.html www.bkjia.com true http://www.bkjia.com/PHPjc/631123.html techarticle often see someone ask, MSSQL occupied too much memory, but also constantly grow, or have set up the use of memory, but it does not use so much, what is the matter? ...
