To set the memory optionWhen you install SQL Server, the setup program assigns 8 MB of memory to SQL Server if the computer has less than 32 MB of RAM, and 16 MB of memory if the computer has 32 MB or more of RAM. After installation, you can and often should manually configure memory to a greater amount.
In particular, you must set the SQL Server memory option appropriately for servers that will be participating in replication. If a server will act as either a remote distribution server or a combined publication/distribution server, it must have at least 32 MB of memory installed, with at least 16 MB assigned to SQL Server. If a remote distribution server will be set up to perform distribution for more than one publication server, the remote distribution server will require additional memory (and additional available disk space). There are no special memory or other hardware requirements for subscription servers or for publication servers that are configured to use a remote distribution server. For more information on replication, see Part 6, Replication.
Note As you increase the SQL Server memory configuration, you need to ensure that there is sufficient disk space to grow the virtual memory support file (PAGEFILE.SYS) to accommodate the additional memory. For information on the virtual memory support file, see your documentation for Windows NT.
The memory option is set in the same manner as any other configuration option, by using SQL Enterprise Manager.
To set the memory optionThe Configuration window displays a list of the configuration options.
The current setting of the option appears in the Running column. The displayed number represents the size of available memory in 2K units. (For example, 8 MB is entered as 4096.) For more information, see the discussion following this procedure.
The setting is changed, but it does not take effect until the server is restarted.
If there are no connected users, skip this step.
To determine the optimal memory for your system, subtract the memory required for Windows NT (and other system uses, if the machine is not wholly dedicated to SQL Server) from the total physical memory. Ideally, you want to allocate as much memory as possible to SQL Server without causing the system to page. You can use SQL Performance Monitor to help determine what the threshold is for your system: the Page Faults/sec counter of the Memory Object indicates whether you are generating any page faults. If so, SQL Server is running with too much memory. The threshold varies depending on your system. For example, on a 32-MB system, 16 MB might be appropriate for SQL Server; on a 64-MB system, 40 MB might be appropriate.
The amount of memory specified must be sufficient for the SQL Server static memory needs (kernel overhead, user stack space, and so on), as well as for the procedure cache and the data cache (also called buffer cache). The memory left over after the SQL Server memory needs are met is divided between the procedure cache and the data cache. The percentage allocated to the procedure cache is set with the procedure cache configuration option. You can use DBCC MEMUSAGE and statistics from SQL Performance Monitor to help you adjust this value. Change this value only when you add or remove memory or when you change how you use your system.
When the memory option is reconfigured, this will automatically cause a possible change to the configured free buffers configuration option. The free buffers configuration value will be set to 5% of the new memory size.
The maximum value for the memory option is 2 GB. Note, however, that the memory option does not include memory needs for tempdb if you have placed tempdb in RAM using the tempdb in ram (MB) option.
For information about using SQL Performance Monitor, see Chapter 19, Monitoring Server Activity and Performance. For more information about allocating memory to SQL Server, see More About Memory, next in this chapter.
You can also display and change the memory option by using sp_configure. For information about using sp_configure to set the configuration options, see sp_configure System Stored Procedure in the Microsoft SQL Server Transact-SQL Reference.