Max Server Memory Setting Explained
If you have more memory than your database and applications on the SQL Server will ever use than this is not a problem, but when you run into memory constraints this setting is much more important.
SQL Server attempts to use as much memory as possible, and when there is no more memory available, SQL Server will use much more I/O due to data and index pages having to be read from disk more often. This works great for SQL Server, but what happens is that SQL Server will take almost all the memory leaving very little for the operating system processes or other applications that are running.
The default for this setting is 2147483647 which is probably more memory that your server has, which effectively tells SQL Server to take as much memory as it wants to.
You can the change the Max Server Memory option in SQL Server Management Studio by right clicking on the instance and choosing properties, then clicking on the Memory tab.
Lets say your database has 8gb of memory, and you have some other applications running that may need 1gb of memory, you might want to set this max server memory setting to 6gb or 6000mb to allow 2gb for your application, the operating system, and everything else that may be running on that server.
You can check this setting by viewing the properties dialog shown above, or by running the following query:
SELECT * FROM sys.configurations WHERE name like 'max server memory%';
There are many recommendations that are usually 80% to 90% of the available memory, or to leave 1gb to 3gb free, however it really depends on what else you have running on your SQL Server. Consider things like SSRS, SSIS, and other applications that may need some memory.
Setting this too low may prevent SQL Server from starting. It is recommended to never set it less than 1gb, however the setting will allow you to set it as low as 128mb, which may have made sense in 1995, but not in todays world. Setting this too low may also significantly impact SQL Server performance. It is safer to start high, and ratchet it down as needed bases on the individual performance of your SQL Server.
Related Links
Need Help
Stedman Solutions, the provider of the Database Health Monitor Application offers consulting solutions, and can help with your SQL Server Administration needs.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!