Memory 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:
SQL Server Integration Services
Q I am installing an active/active cluster for SQL Server 2005 (64-bit Enterprise Edition with two servers) and I'll have a total of four instances of SQL Server 2005. SQL Server Integration Services (SSIS) will be required for all the instances. What can you tell me about clustering SSIS and the effect on maintenance plans?
A While you can cluster the SSIS service, it is not necessary to do so and you could run into a variety of problems, including a lack of support for delegation (see msdn2.microsoft.com/aa337083) and that it's not multi-instanced—you can only have one instance running on a node at a time.
Previously, SSIS had to be installed—not running, just installed—for the Maintenance Plan Wizard to run. However, this is no longer the case in SQL Server 2005 SP1. If SSIS is not running, the maintenance plans can be executed by SQL Server Agent.
Rather than clustering SSIS, you may consider keeping it running as a standalone service and editing the MsDtsSrvr.ini.xml to point to any and all running instances. This lets you manage your packages from any nodes and provide the high availability most customers are looking for without any of the problems associated with clustering the service.
For more information on failed maintenance plan creation, be sure to see the Knowledge Base article at support.microsoft.com/kb/909036.
Strange Execution Times
Q During my load test on my SQL Server 2005 SP1 box, SQL Server Profiler recorded many negative values of stored procedure (SP) execution time and in some cases the SP execution time, did not match the result of subtracting start time from end time.
A There are a number of things that can affect the reporting of SP execution time and other performance times in SQL Server Profiler. Remember, SQL Server 2005 uses milliseconds to count execution time, and if you are using any technologies that change the unit of measurement, you will get inconsistent reporting and execution times that don't add up.
For example, if you're using other power schemes, CPU stepping, or AMD Cool 'n Quiet technology, you are changing CPU frequencies, which then do not match what SQL Server Profiler is expecting when calculating execution time.
There's a Knowledge Base article at support.microsoft.com/kb/931279 that explains the symptoms, a variety of causes, and some remedies.
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited