asked
Growing working set and private bytes for sqlserver and sql agent
We have SQL Server 2012 Standard SP1 to be used by sharePoint 2013. We are monitoring working set and private bytes for SQL server and sql agent. It is increasing everyday by few MBs. I suspect in few weeks it will be very high and will not leave any memory for other processes. We had similar situation few weeks back, but server restart released all memory. But now again it has started chewing up memory.
SQL Server is designed to not release memory it has aquired. That's because SQL Server is working with caching data. Instead of having to read data from the disk system, SQL Server reads data directly from the primary memory. If SQL Server would release memory, it would have to go back to disk every time a data page is requested - a time consuming operation. If you need to preserve a certain set of memory for other applications, you can cap SQL Server's memory consumtion. That's done either via the stored procedure sp_configure or with Management Studio. If you use Management Studio, you connect to your SQL Server instance, right click on the instance node in object explorer, select Properties and move to the Memory tab. There you set a proper value for Maximum server memory. You may also want to set Minimum server memory, which is the amount of memory that you want to reserve specifically for SQL Server and therefore not allow any other service/application to "steal" from SQL Server.
15 People are following this question.
