About MSSQL occupy too much memory issues

The problem often see people ask, MSSQL takes up too much of the memory, and constant growth; or have already been set memory usage, but it didn't use so much, that's what this is all about?
First of all, we're going to look at MSSQL is how to use the memory.
The largest expenses is generally used for the data cache, if enough memory, it will use the data and think you're going to use a data all throw in memory, until the memory, until the hit rate of low data to get rid. So in general we're looking at the statistics Io, the physics read is 0.
The second is the query overhead, the general, hash join will bring large memory overhead, and merge join and the nested loop overhead is relatively small, and the sort and middle table, the cursor is also there will be relatively large overhead.
So used to correlate and sort the column of the general need index.
Again, the second is to carry out the plan, the system data storage, these are rather small.


We first came to see the data cache performance impact, if the system is not in the other applications to compete for memory, a data cache is generally the more, the better, even at some point, we can force some data pin in a cache. But if there was any other applications, although at the time of need, MSSQL will release the memory, but a thread switch, IO waiting for these work is also need time, so it will cause performance degradation. So we must be set to MSSQL maximum memory usage. Can the SQL Server properties (memory tab) found in the configuration maximum use of memory, or you can also use the sp_configure to complete. If there is no other applications, then don't limit MSSQL to memory usage.

And then to query the overhead of this overhead is obviously the lower the better, because we can't benefit from it, on the contrary, use the more memory probably means that the query speed reduced. So we want to avoid middle table and of the cursor, the often as a linked and sort the column index.