SQL Q&AMemory Configuration, Performance Profiling, Setting Your Fill Factor, and More
Edited by Nancy Michell
Q I'm trying to figure out the best memory configuration for my SQL Server™ boxes. The admin who preceded me set Boot.ini for each machine with 12GB of RAM on a SQL Server 2000 two-node cluster as follows: Yes /PAE NO /3GB (No AWE set for SQL Server). With 12GB of RAM available, should I remove the /3GB switch in Boot.ini, turn on AWE, and give SQL Server about 10GB of the 12GB? Nothing else is on the machines running SQL Server, so no other apps need memory.
A Yes, you should turn on address windowing extensions (AWE) and preallocate an upper limit of RAM for SQL Server—10GB sounds good on a 12GB dedicated SQL Server box. (Note that preallocating is only valid for SQL Server 2000. Starting with SQL Server 2005, using AWE is no longer static and can be changed on the fly.) There has always been a big debate as to whether to use both the /3GB and /PAE switches or just the required /PAE. Though you only truly need /PAE set and AWE enabled, I suggest you use both switches; however, there are some factors to consider.
Using the /3GB switch boils down to whether it's needed in your particular situation. Are you running out of MemToLeave areas of memory that must be in the first 2 or 3GB of virtual address space? If you do enable the switch, are you starving the operating system of memory? (See support.microsoft.com/kb/316739 for more information.) If you're working on a cluster, you could set /3GB on one node and not on the other. That way, if you're testing with /3GB and having problems, you can fail the instance over to the other node pretty quickly. Keep in mind that if you have more than 16GB RAM, the /3GB switch is not supported.
By using /3GB, you are increasing the virtual address space (VAS) by 50 percent, so applications that put memory pressure on VAS and not just the data cache benefit greatly. Fortunately, the 64-bit servers, both IA64 and x64, eliminate this misunderstood factor. Concerns regarding starving the OS are not applicable if the machine is dedicated to SQL Server. Leaving 2GB for the OS is a bit of overkill as well; if this server is dedicated to SQL Server with only the standard minimal OS services running, you'll find there will be about 1.3GB of free memory on the server, so you might as well let SQL Server use that extra 1GB. Start at 10GB, use PerfMon to monitor available memory over a long period to see how much sits idle, and then adjust accordingly. Remember that you will incur swapping if you over-commit on SQL Server 2000, as AWE is not as dynamic as it is with SQL Server 2005. The key to deciding whether to use the /3GB switch is testing it in your specific environment.
Instance Names for Replication
Q Can I use my server's IP address now in SQL Server 2005 Replication to indicate which instance to replicate? In SQL Server 2000, according to "How to Replicate between Computers Running SQL Server in Non-Trusted Domains or across the Internet" (support.microsoft.com/kb/321822), doing so will cause errors, but I don't know if this is still true.
A When specifying server instances to participate in replication, you must supply the SQL Server registered instance name. For example, you must use the SQL Server instance name when specifying Publisher or Subscriber parameters to replication stored procedures or to the replication agent connection settings on the command line. If the network name for the SQL Server instance differs from the registered instance name, replication connections by agents will fail.
If the network name of the instance and the SQL Server instance name differ, consider adding the SQL Server instance name as a valid network name. One way to set an alternative network name is to add it to the local hosts file. The local hosts file is located by default at WINDOWS\system32\drivers\etc or WINNT\system32\drivers\etc. For example, if the computer name is comp1, the computer has an IP address of 10.193.17.129, and the instance name is inst1/instname, add the following entry to the hosts file: