Reducing SQL Server memory is managed by a process called LazyWriter. This is a periodic process that checks the status of the SQL Server buffer in a cycle and evaluates if it needs to increase or decrease according to the memory required by SQL Server and memory available on the OS. This Process wakes up every 1 second by default and can also be called other process to run more frequently.
When Lazywriter activates, it is designed to shrink the buffer only when the OS does not have sufficient memory available to service the memory requests from other applications. If the OS has sufficient memory, SQL Server does not de-commit any buffers, thus leaving the committed buffers in memory for fast access and better performance. The buffer stores execution plans, creates temporary indexes and allocates cursors.
A counter called SQL Server Buffer Mgr: Lazy Writes/Sec tracks how many times a second the LazyWriter process moves dirty pages from the buffer to disk in order to free up buffer space. This counter should not be a high value, ideally, it should be close to zero. If it is exactly zero, this indicates that SQL Server’s buffer cache is sufficiently large and SQL Server does not have to free up dirty pages, and will instead check the status at regular intervals. If the counter value is high more memory is required
]]>