In my “career” as a muskie fisherman, I’ve caught or seen fish in some very unlikely places. I’ve caught fish over very deep water with seemingly no nearby structure as well as in water as shallow as just a few feet. I’ve had muskies strike or chase northern pike and walleye as I brought them into the boat. I’ve had bites when I was expecting nothing. The key to catching more fish is being will to test new locations, techniques, and lures.
I recently wondered what would happen if I was forced to drastically reduce memory available to SQL Server. Most DBAs know that if SQL Server is low on available memory and there is RAM to spare on the server, you can increase the memory SQL Server can use in the server properties. But what happens if you reduce the memory SQL Server can use below what it is currently using? I decided to try it out in our Test environment.
The initial maximum memory usage was 7 GB:
In looking at the SQL Server process in Windows Task Manager, I see it is using all plus a bit:
I turned the max down to 4.5 GB:
Looking at the Windows Task Manager shortly after the change, I can see the amount be using by SQL Server has been reduced as expected.
If I look at my Memory Usage tracking at that time, I can see buffer pool was the area that was directly affected. The buffer pool is where any data pages in SQL Server are cached for quick reading/writing.
So next time you need to adjust the max memory downward, be aware this will immediately reduce the number of pages in the buffer pool.