SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Careful with your SQL Server Max Memory settings

Quite often I see database administrators set SQL Server max server memory thinking everything related to SQL Server uses this shared memory pool. This is a mistake. There are many things that rely on memory that are not part of SQL Server. Best practices state that you should leave memory allotted for the operating system. However, did you know that if you are running services like SSIS, SSAS or SSRS on the same server as the database engine that it does not use the same memory you have allocated for SQL Server? If the Max Memory setting is not configured correctly, these other serves could incur memory pressure.  While the memory consumed by SSAS and SSRS can be configured, SSIS can be a little bit more challenging. Beyond this, there are even scenarios where SQL Server max memory consumed can exceed the setting, like with CLR in versions earlier than 2012 and some other bugs in SQL Server.

As a consultant, I have seen memory pressure and memory exhausted too many times to count because the DBA was unaware of this. I applaud those that take the time to properly configure this setting according to what the database engine requires. Let’s take it a step further and take the time to look at what additional services you are using and allot memory accordingly.

Beyond just thinking of what additional services are running also be aware of additional instances on that server. Again, I have seen time and time again over allocation of memory when other instances are not considered. I have seen where each instance has the same maximum memory value which over extends the available physical memory instead of spreading that amount across the instances according to each of their workloads. SQL Server makes zero attempts to balance memory usage across instances that reside on the same server.

I am not going to go into how to set your max memory as there are many great resources out there to help you do that. I am writing this just to put a little bug in your ear of things to consider when choosing a value above and beyond what your database engine may require. Be sure to leave enough memory for those additional things running on your server besides the operating system.

Note: Jonathan Kehayias (B|T) wrote a great post on configuring max memory. I recommend reading, How much memory does my SQL Server actually need?

SQLEspresso

Monica lives in Virginia and is a Microsoft MVP for Data Platform. She has over 15 years of experience working with a wide variety of database platforms with a focus on SQL Server. She is a frequent speaker at IT industry conferences on topics including performance tuning and configuration management. She is the Leader of the Hampton Roads SQL Server User Group and a Mid‐Atlantic PASS Regional Mentor. She is passionate about SQL Server and the SQL Server community, doing anything she can to give back. Monica can always be found on Twitter (@sqlespresso) handing out helpful tips.

Comments

Leave a comment on the original post [sqlespresso.com, opens in a new window]

Loading comments...