The voice on the other end of the line was concerned. It would appear that their server was having memory issues. But they already knew the answer, the memory issues were ‘expected’ due to the fact that they were running Microsoft SQL Server.
“SQL Server is a memory hog, it uses all that the server has!”
Yes. It does. But only because you told it to do that.
Which means you can also tell it to do something else, say, to not use all the server memory.
As John Lennon once said “…it’s easy if you try.”
Know Your Defaults
It doesn’t take much to install software these days other than an ability to click on the ‘Next’ button repeatedly. At the end of the SQL Server install process you are left with an instance up and running but with all the default settings. Not understanding the implications of those default settings is what results in people having crazy notions about SQL Server performance, such as it being a “memory hog”.
I don’t care what kind of server you are running; Linux, Unix, or Windows. It doesn’t matter. Each has the same resource bottlenecks: CPU, memory, disk I/O, and network. After you have completed an install of SQL Server if you don’t choose the right settings then you are likely to see issues with one of those four resource bottlenecks sooner rather than later.
The one we are interested in here is named ‘max server memory’ and can be found in one of two ways. One way is to use the sp_configure stored procedure. Chances are you will need to view the advanced options so go ahead and run this:
EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure 'max server memory' GO
You will likely see this as the result:
What that means is that you are telling SQL Server that it is OK to go ahead and try to use up to a maximum of 2147483647 MB of memory. Do a little math and you see that you are telling SQL Server that it is OK to go ahead and use up to roughly 2 petabytes of memory. I don’t know about you, but that is just a tad bit more memory than what I find available in most servers. In other words, you are telling SQL to go ahead and use everything!
The other way to see this info is to use SSMS and to view the server properties. Inside of the object explorer, right-click on the server name and select ‘Properties’, then select ‘Memory’ from the options on the left. On that screen you can set the values you want for both the minimum and maximum memory values, in MB:
If you want to know what value you should choose for your max setting I would point you to this blog post by Jonathan Kehayias (blog | @SQLPoolboy), I like the formula he has been using, it is better than any others I have been using previously.
After setting this value you should notice something remarkable: SQL Server won’t consume all of the memory on the server by default! Even if the server is dedicated to running just this instance of SQL Server you still wouldn’t want to allow it to consume all available memory (you don’t want the O/S to compete with SQL Server for memory), so why would you ever leave this setting at the default? Unless you have more than 2PB of RAM, of course.
I would encourage you to examine all of the default configuration settings that can be set using the sp_configure system stored procedure. It is good to understand all of the options that are available to you as you try to troubleshoot issues with any one of those aforementioned resource bottlenecks.
Thanks for the post.
You can use the following query to pull back the information as well:
USE masterGO
SELECT name, value_in_use FROM sys.configurationsWHERE name = ‘max server memory (MB)’
The query can be run through Central Management Server, so I can get information about my entire environment at once. Additionally, multiple configuration values can be pulled back with the single query.
Frank,
Nice idea, using the CMS to gather such details. Thanks for the comment!
Thanks for the info.
On one of my servers, sp_config is showing min: 16 max: 2,147,483,647 while server properties is showing min: 4096 max: 24,576.
Do you have any idea why these might be different? Thanks!
Sorry, I see it now. The config_value and run_value are set at 24,576. I was fixated on the max value of 2,147,483,647.
Machines always do just what you tell them to do
As long as you do what they say. T-Bone Burnett