SQLCrazyCertified (1/5/2013)
So, if I understand you correctly, if SQL uses all the memory which is allocated to it, then SQL will not have enough memory to work on other outstanding processes
Huh?
SQL uses its memory for all its processes, for the data cache, the plan cache and the several other caches that it has and for anything else it needs memory for.
If that's the case how can you say it's the normal expected behavior?
Because it is normal, expected behaviour.
It would not be efficient for SQL to request memory from the OS for one process, release it when that process is done, request memory from the OS for another, release, etc. If it did, it would be spending huge amounts of time and resources requesting and and releasing memory rather than actually doing productive work. Hence it requests memory, does not release it (unless the OS complains) and manages it after that point.
See the book I previously referenced, chapter 4.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability