I often see someone asking, MSSQL takes 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.
