I want to know why SQL Server memory use increases whenever I run stored procedures.
After I stop and restart SQL Server, the memory used is just 1 GB.
Each time I run stored procedures, the memory use increases, and has now reached 8GB.
I checked temporary tables, there are just a few.
I don't know why and how to reduce SQL Server memory use.
I'm using SQL Server 2012 under Windows 8.
I just want to know why SQL Memory increase whenever runs stored procedures.
Because SQL Server is a messy. It tries to keep everything it ever reads. The idea behind this is that using memory is faster than using disc. Not sure you agree here - the rest of the world does.
After I stopped and restarted SQL. The memory just has 1 GB.
Because right after a restart obviously SQL Server has nothing read and thus nothing cached.
Each time I run stored, the memory will raise and now it reach 8GB.
That is still a VERY small server, you know. I have seen machines using hundreds of gb. So what? It is faster than reading the data from disc.
I don't know why and how to reduce SQL memory.
Why would you ever do that? Set good limits for memory use and then let SQL Server do it's work. After all, accessing cached data is many times faster than even reading it from SSD, you know.
If you are SERIOUSLY memory starved then set a good limit and if that leads to performance problems (particularly when dealing with small amounts like 8gb of memory) just plug in more RAM. RAM is not that expensive. At least as long as you talk about such small amounts.
So, in short: SQL Server loves caching data under the correct assumption it makes it faster. If you have not set a limit, it will gladly do so up to the memory installed in the machine. It DOES release that, but having multiple servers fighting for memory is not good. If you are not running a dedicated SQL Server and/or are memory starved, set the max memory parameter in the server options to a sensible value.
