One function that every database administrator must perform is determining the most appropriate SQL server memory configuration. Any database manager must ensure that he or she chooses the best memory configuration in order to guarantee optimum performance. Memory configuration tweaking is important for your environment to provide adequate support for all workloads that involve extended stored procedures, Linked Servers, .Net CLR and managed codes. Here's how to determine MemToLeave settings and configure Microsoft SQL Server memory.
How to establish an excellent foundation?
Prior to considering any additional tweaking of the SQL server, it is extremely important and prudent to have a solid foundation. You have to address all the basics. For starters, ensure that you apply the correct settings of both the software and hardware of your environment. Additionally, make sure that the configuration you choose makes the maximum use of the available resources.
Brilliant resources and references on how to configure your SQL server platform are available both online and offline.
Extra considerations to make when configuring your SQL server memory
After establishing an excellent foundation, further tweaking is important in order to match the exact requirements of your specific platform. For instance, your specific environment might dictate that your SQL server shares the available memory with other apps. In this case, you might need to consider the memory distribution resource. For the best configuration, ensure that the SQL server is running on its own committed server. However, in most cases, this can be troublesome.
Suppose your environment is using a great amount of managed codes like .Net CLR. Such technologies require memory similar to what a SQL server does. Nonetheless, a critical point to note is that the particular memory of such resources is usually allocated for the outside of SQL server buffer. This is in a part of the memory by the name MemToLeave.
MemToLeave Settings
Simply put, MemToLeave refers to a VAS (Virtual Address Space) that is normally left unused whenever the SQL server begins to run. It helps save the external components in some address space. Therefore, for technologies such as Linked Servers and .Net CLR to perform efficiently, you should ensure that they also have access to adequate memory.
To be on the safe side, ensure that you openly set the maximum memory amount that your SQL server uses instead of allowing it to take everything that is available to the server.
Determining the MemToLeave usage
You can use two indicators to determine if there is need to assign additional memory to the MemToLeave. These are:
- Warning or error messages raised by a specific managed code component or SQL server
- T-SQL script showing that memory available is insufficient for your platform requirements
For instance, if your MemToLeave region is small for a .Net managed code, one common indicator will be the appearance of recurrent "Application Domain Unload" messages. These messages normally appear in the SQL server”™s error log.
Contrary to what most people think, configuring of the SQL server memory is not difficult. With the above information, you can effortlessly do it. That is for sure.