Max Server Memory (MB) & Min Server Memory (MB)
For best SQL Server performance, you want to dedicate your SQL Servers to only running SQL Server, not other applications. And in most cases, the settings for the “maximum server memory” and the “minimum server memory” should be left to their default values. This is because the default values allow SQL Server to dynamically allocate memory in the server for the best overall optimum performance. If you “hard code” a minimum or maximum memory setting, you risk hurting SQL Server’s performance.
On the other hand, if SQL Server cannot be dedicated to its own physical server (other applications run on the same physical server along with SQL Server) you might want to consider changing either the minimum or maximum memory values, although this is generally not required.
Let’s take a closer look at each of these two settings.
The “maximum server memory” setting, when set to the default value of 2147483647 (in MB), tells SQL Server to manage the use of memory dynamically, and if it needs it, to use as much RAM as is available (while leaving some memory for the operating system).
If you want SQL Server to not use all of the available RAM in the server, you can manually set the maximum amount of memory SQL Server can use by specifying a specific number that is between 4 (the lowest number you can enter) to the maximum amount of RAM in your server (but don’t allocate all the RAM in your server, as the operating system needs some RAM too).
Only in cases when SQL Server has to share memory with other applications on the same server, or when you want to artificially keep SQL Server from using all of the RAM available to it, would you want to change the default value. For example, if your “other” application(s) are more important than SQL Server’s performance, then you can restrain SQL Server’s performance if you want.
There are also two potentially performance issues you can create if you do attempt to set the “maximum server memory” setting manually. First, if you allocate too much memory to SQL Server, and not enough for other applications or the operating system, then the operating system may have no choice but to begin excessive paging, which will slow performance of your server. Also, if you are using the Full-Text Search service, you must also leave plenty of memory for its use. Its memory is not dynamically allocated like the rest of SQL Server’s memory, and there must be enough available memory for it to run properly.
The “min server memory” setting, when set to the default value of 0 (in MB), tells SQL Server to manage the use of memory dynamically. This means that SQL Server will start allocating memory as is needed, and the minimum amount of RAM used can vary as SQL Server’s needs vary.
If you change the “min server memory” setting to a value other than the default value of 0, what this means is not that SQL Server will automatically begin using this amount of minimum memory automatically, as many people assume, but that once the minimum amount is reached (because it is needed) that the minimum amount specified will never go down below the specified minimum.
For example, if you specify a minimum value of 100 MB, then restart SQL Server, SQL Server will not immediately reserve 100 MB of RAM for its minimal use. Instead, SQL Server will only take as much as it needs. If it never needs 100MB, then it will never be reserved. But if SQL Server does exceed the 100 MB amount specified, then later it doesn’t need it, then this 100 MB will then become the bottom limit of how much memory SQL Server allocates. Because of this behavior, there is little reason to change the “min server memory” setting to any value other than its default value.
If your SQL Server is dedicated, there is no reason to use the “min server memory” setting at all. If you are running other applications on the same server as SQL Server, there might be a very small benefit of changing this setting to a minimum figure, but it would be hard to determine what this value should be, and the overall performance benefit would be negligible.
If you find in your audit that these settings are some other value other than the default, find out why. If you can’t find out why, or if the reason is poor, change them back to their default values.
Max Text Repl Size
The “max text repl size” setting is used to specify the maximum size of text or image data that can be inserted into a replicated column in a single physical INSERT, UPDATE, WRITETEXT, or UPDATETEXT transaction. If you don’t use replication, or if you don’t replicate text or image data, then this setting should not be changed.
The default value is 65536, the minimum value is 0, and the maximum value is 2147483647 (in bytes). If you do heavy replication of text or image data, you might want to consider increasing this value only if the size of this data exceeds 64K. But as with most of these settings, you will have to experiment with various values to see what works best for your particular circumstances.
As part of your audit, if you don’t use replication, the the only correct value here is the default value. If the default value has been changed, you need to investigate if text or image data is being replicated. If not, or if the data is less than 64K, then change it back to the default value.
Max Worker Threads
The “max worker threads” SQL Server configuration setting is used to determine how many worker threads are made available to the sqlservr.exe process from the operating system. The default value is 255 worker threads for this setting. SQL Server itself uses some threads, but they will be ignored for this discussion. The focus here is on threads created for the benefit of users.
If there are more than 255 user connections, then SQL Server will use thread pooling, where more than one user connection shares a single worker thread. Although thread pooling reduces the amount of system resources used by SQL Server, it can also increase contention among the user connections for access to SQL Server, hurting performance.
To find out how many worker threads your SQL Server is using, check the number of connections that are currently made to your server using Enterprise Manager. For each SQL Server connection, there is one worker thread created, up to the total number of worker threads that are specified in the “max worker threads” settings. For example, if there are 100 connections, then 100 worker threads would be employed. But if there are 500 connections, but only 255 worker threads are available, then only 255 worker threads are being used, with all the open connections sharing the limited worker threads.
Assuming there is enough RAM in your server, for best performance, you will want to set the “max worker threads” setting to a value equal to the maximum number of user connections your server ever experiences, plus 5. But there are some limitations to this general recommendation, as we will soon see.
As has already been mentioned, the default value for the “max worker threads” is 255. If your server will never experience over 255 connections, then this setting should not be changed from its default value. This is because worker threads are only created when needed. If there are only 50 connections to the server, there will only be that many worker threads, not 255 (the default value).
If you generally have over 255 connections to your server, and if “max worker threads” is set to the default value of 255, what will happen is that SQL will begin thread pooling. Now comes the dilemma. If you increase the “max worker threads” so that there is one thread for each connection, SQL Server will take up additional resources (mostly memory). If you have plenty of RAM in your server that is not being used by SQL Server or any other application, then boosting the “max worker threads” can help boost the performance of SQL Server.
But if you don’t have any extra RAM available, then adding more worker threads can hurt SQL Server’s performance. In this case, allowing SQL Server to use thread pooling offers better performance. This is because thread pooling uses less resources than not using it. But, on the downside, thread pooling can introduce problems of resource contention between connections. For example, two connections sharing a thread can conflict when both connections want to perform some task as the exact same time (which can’t be done because a single thread can only service a single connection at the same time).
So what do you do? In brief, if your server normally has less than 255 connections, leave this setting at its default value. If your server has more than 255 connections, and if you have extra RAM, then consider bumping up the “max worker threads” setting to the number of connections plus 5. But if you don’t have any extra RAM, then leave the setting to its default value. For SQL Server with thousands of connections, you will have to experiment to find that fine line between extra resources used by additional worker threads and contention between connections all fighting for the same worker thread.
As you might expect, before using this setting in production, you will want to test your server’s performance before and after the change to see if SQL Server benefited, or was hurt, from the change.
As part of your audit, follow the advice just given above for how to set this setting.
Min Memory Per Query
When a query runs, SQL Server does its best to allocate the optimum amount of memory for it to run efficiently and quickly. By default, the “minimum memory per query” setting allocates 1024 KB, as a minimum, for each query to run. The “minimum memory per query” setting can be set from 0 to 2147483647 KB.
If a query needs more memory to run efficiently, and if it is available, then SQL Server automatically assign more memory to the query. Because of this, changing the value of the “minimum memory per query” default setting is generally not advised.
In some cases, if your SQL Server has more RAM than it needs to run efficiently, the performance of some queries can be boosted if you increase the “minimum memory per query” setting to a higher value, such as 2048 KB, or perhaps a little higher. As long as there is “excess” memory available in the server (essentially, RAM that is not being used by SQL Server), then boosting this setting can help overall SQL Server performance. But if there is no excess memory available, increasing the amount of memory for this setting is more likely to hurt overall performance, not help it.