Starting with Service Pack 2 for SQL Server 7.0, and including SQL Server 2000, there is a SQL Server startup switch that affects how SQL Server allocates memory when it starts up. This switch, /g, is used to tell SQL Server how much address space to reserve for the non-buffer pool component of SQL. The non-buffer pool is used by SQL Server for executable code or for large memory allocations that need space in chunks larger than 8KB, such as large stored procedures and query plans.
In SQL Server 7.0, the default address space allocated to the non-buffer pool is 128MB. For SQL Server 2000, the default amount is 256MB.
Microsoft only recommends using this switch if your server has large amounts of RAM (2GB or greater) and if you regularly get this error message in the SQL Server Error Log:
Warning: Clearing procedure cache to free contiguous memory.
[7.0, 2000] Updated 8-17-2003
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, and I don’t generally recommend it.
Let’s take a closer look at each of these two settings.
The “maximum server memory” setting, when set to the default, 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 SQL Server, as the operating system needs some RAM too).
When “maximum server memory” is set to the default value, as mentioned before, memory use is adjusted dynamically. What this also means is that if you are running other applications other than SQL Server on a physical server, that SQL Server “may play nice” and give up some of its memory if other applications need the use of some. (Note: Above, I say “may play nice”. This is because SQL Server will not give up RAM if it needs it. But if it has allocated the RAM, and it doesn’t need it for a long period of time, then it will give up some of the RAM it has grabbed it if is not being used.)
So in most cases, there is no reason to change this setting from its default value. Only in rare occasions when SQL Server doesn’t appear to “play nice,” 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 attempt to set the “maximum server memory” setting manually. First, if you allocate too much memory to SQL Server and not enough for 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 benefits would most likely be negligible. [7.0, 2000, 2005] Updated 6-7-2004
*****
When SQL Server is run under Windows NT 4.0 or Windows 2000/2003, a SQL Server thread can move from one CPU to another. This feature allows SQL Server to run multiple threads at the same time, generally resulting in better load balancing among the CPUs in the server. The only downside to this process is that each time a thread moves from one CPU to another, the processor cache has to be reloaded, which can hurt performance in some cases.
In cases of heavily-loaded servers with more than 4 CPUs, performance can be boosted by specifying (to a limited degree) which processor should run a specific thread. This reduces the number of times that the processor cache has to be reloaded, helping to eek out a little more performance of the server. For example, you can specify that SQL Server will only use some of the CPUs, not all of them available to it in a server.
The default value for the affinity mask setting, which is “0,” tells SQL Server to allow the Windows Scheduling algorithm to set a thread’s affinity. In other words, the operating system, not SQL Server, determines which threads run on which CPU, and when to move a thread from one CPU to another CPU. In any server with 4 or less CPUs, the default value is the best overall setting. And for servers with more than 4 CPUs, and that are not overly busy, the default value is also the best overall setting for optimum performance.
But for servers with more than 4 CPUs, and heavily loaded because of one or more non-SQL Server applications running on the same server as SQL Server, then you might want to consider changing the default value for the affinity mask option to a more appropriate value. Please note that if SQL Server is the only application running on the server, then using the affinity mask to limit CPU use could hurt performance, not help it.
For example, let’s say you have a server that is running SQL Server, COM+ objects and IIS. Let’s also assume that the server has 8 CPUS and is very busy. By reducing the number of CPUs that can run SQL Server from 8 to 4, what will happen is that SQL Server threads will now only run on 4 CPUs, not 8 CPUs. This will reduce the number of times that a SQL Server thread can jump CPUs, reducing how often the processor cache as to be reloaded, helping to reduce CPU overhead and boosting performance somewhat. The remaining 4 CPUs will be used by the operating system to run the non-SQL Server applications, helping them also to reduce thread movement and boosting performance.
For example, if you have an 8 CPU system, the value you would use in the sp_configure command to select which CPUs that SQL Server should only run on are listed below:
|
Decimal Value |
Allow SQL Server Threads on These Processors |
|
1 |
0 |
|
3 |
0 and 1 |
|
7 |
0, 1, and 2 |
|
15 |
0, 1, 2, and 3 |
|
31 |
0, 1, 2, 3, and 4 |
|
63 |
0, 1, 2, 3, 4, and 5 |
|
127 |
0, 1, 2, 3, 4, 5, and 6 |
Specifying the appropriate affinity mask is not an easy job, and you should consult the SQL Server Books Online before doing so for additional information. Also, you should test what happens to your SQL Server’s performance before and after you many any changes to see if the value you have selected hurts or helps performance. Other than trial and error, there is no easy way to determine the optimum affinity mask value for your particular server. [7.0, 2000, 2005] Updated 6-7-2004
If you are using SQL Server 2000 Standard Edition under Windows NT 4.0 or Windows 2000/2003 (any version), or are running SQL Server 2000 Enterprise Edition under Windows NT 4.0 or Windows 2000 Server, or if your server has less than 4GB or RAM, the “awe enabled” option should always be left to the default value of 0, which means that AWE memory is not being used.
The AWE (Advanced Windowing Extensions) API allows applications (that are written to use the AWE API) to run under Windows 2000 Advanced Server or Windows 2000 Datacenter Server to access more than 4GB of RAM. SQL Server 2000 Enterprise Edition (not SQL Server 2000 Standard Edition) is AWE-enabled and can take advantage of RAM in a server over 4GB. If the operating system is Windows 2000 Advanced Server, SQL Server 2000 Enterprise Edition can us up to 8GB of RAM. If the operating system is Windows 2000 Datacenter Server, SQL Server 2000 Enterprise can use up to 64GB of RAM.
By default, if a physical server has more than 4GB of RAM, Windows 2000 (Advanced and Datacenter), along with SQL Server 2000 Enterprise Edition, cannot access any RAM greater than 4GB. In order for the operating system and SQL Server 2000 Enterprise Edition to take advantage of the additional RAM, two steps have to be completed.
Exactly how you configure AWE memory support depends on how much RAM your server has. Essentially, to configure Windows 2000 (Advanced or Datacenter), you must enter one of the following switches in the boot line of the boot.ini file, and reboot the server:
- 4GB RAM: /3GB (AWE support is not used)
- 8GB RAM: /3GB /PAE
- 16GB RAM: /3GB /PAE
- 16GB + RAM: /PAE
The /3GB switch is used to tell SQL Server to take advantage of 3GB out of the base 4GB of RAM that Windows 2000 supports natively. If you don’t specify this option, then SQL Server will only take advantage of 2GB of the first 4GB of RAM in the server, essentially wasting 1GB of RAM.
AWE memory technology is used only for the RAM that exceeds the base 4GB of RAM, that’s why the /3GB switch is needed to use as much of the RAM in your server as possible. If your server has 16GB or less of RAM, then using the /3GB switch is important. But if your server has more than 16GB of RAM, then you must not use the /3GB switch. The reason for this is because the 1GB of additional RAM provided by adding the /3GB switch is needed by the operating system in order to take advantage of all of the extra AWE memory. In other words, the operating system needs 2GB or RAM itself to mange the AWE memory if your server has more than 16GB of RAM. If 16GB or less of RAM is in a server, then the operating system only needs 1GB of RAM, allowing the other 1GB of RAM for use by SQL Server.
Once this step is done, the next step is to set the “awe enabled” option to 1, and then restart the SQL Server service. Only at this point will SQL Server be able to use the additional RAM in the server.
One caution about using the “awe enabled” setting is that after turning it on, SQL Server no longer dynamically manages memory. Instead, it takes all of the available RAM (except about 128MB which is left for the operating system). If you want to prevent SQL Server from taking all of the RAM, you must set the “max server memory” option (described in more detail later in this article) to a figure that limits SQL Server to the amount or RAM you specify. (7.0, 2000) Updated 6-7-2004