Experimenting with SQL Server Memory

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.

shallow-muskies
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:

20161130-lower-memory-limit-below-current-usage-initial-setting

In looking at the SQL Server process in Windows Task Manager, I see it is using all plus a bit:

20161130-lower-memory-limit-below-current-usage-current-usage

I turned the max down to 4.5 GB:

20161130-lower-memory-limit-below-current-usage-change-setting

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.

20161130-lower-memory-limit-below-current-usage-new-usage

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.

20161130-lower-memory-limit-below-current-where-memory-changed

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s