SQL Server 2008 : Configuring the Instance (part 1) - Viewing Configuration Settings & Specifying Maximum and Minimum Server Memory

Before starting the configuration discussion, we would like to point out that there are generally two methods of modifying configuration options in SQL Server: You can use the SQL Server Management Studio GUI interface, or you can invoke the stored procedure sp_configure in a query window. When both methods of amending a configuration option exist, we will show you both the GUI version and code version for the change.

1. Viewing Advanced Options

By default, SQL Server does not let you view all of the available configuration options. You are initially limited to the most basic choices. Thus the first command to run in your query window is the following:

sp_configure 'show advanced options', 1;GO reconfigure

This statement allows us to view the advanced options when executing the sp_configure query. That way, we can ensure all the options discussed in subsequent subsections are viewable.

2. Viewing Configuration Settings

There are three basic methods of viewing the configuration options and their minimum, maximum, and currently configured value:

• Executing the stored procedure sp_configure

• Querying the sys.configurations system view

• Right-clicking on the instance name and selecting Properties from the pop-up menu

Figure 1 displays the results of executing the sp_configure stored procedure.

Figure 1. A snapshot of the results of sp_configure

Figure 2 shows the results of querying the sys.configurations system view.

Figure 2. The results of the sys.configurations system view

Figure 3 shows the Server Properties window.

Figure 3. A sample of the Server Properties window

Visually, you can easily identify the similarities and differences among the screenshots. We encourage you to review the results of the sys.configuration system view. The system view provides you with similar information as the other views, with the advantage of a description of the configuration option. After reviewing the descriptions, you should have a better understanding of the usages of the option and determine if you need to do more research before enabling or disabling the option.

3. Specifying Maximum and Minimum Server Memory

Determining the maximum and minimum amounts of memory each instance of SQL Server requires can be easy or complex, depending on the server and what applications are running on it. If you have a single instance of SQL Server on a server without any other applications (excluding applications like virus scan and other essential apps), then you want to dedicate as much memory as possible to SQL Server while still leaving enough memory for the operating system to run effectively. Depending on the amount of memory that server has, we generally like to leave at least 1 or 2 GB for Windows. Figure out the best setting for maximum memory where both SQL Server and the operating system are happy.

You can specify memory allocation from the Memory section of the Server Properties dialog for your instance (see Figure 4). You can see that the minimum server memory has been left at 0 MB, whereas the maximum has been set at 8192 MB (which is 8 GB).

Figure 4. The Memory section of the Server Properties dialog

You also have the option to invoke a stored procedure to specify memory allocation. The following invocation of sys.sp_configure requests the same 0 to 8192 MB allocation as was shown in Figure 4:

XEC sys.sp_configure N'max server memory (MB)', N'8192'
GO

Configuring your maximum amount of memory on servers that have multiple instances of SQL Server or other applications will increase the complexity and importance of your task. Do not forget that SQL Server is a memory hog; it allocates as much memory to the buffer as needed to process or handle the workload. If you have multiple instances of SQL Server reserving memory without restrictions or limitations, then one instance will likely be starved or not have as much memory as it needs. To help eliminate that as a potential problem, you should set minimum and maximum memory allocations.

When determining the maximum amount of memory for each instance, we usually revert to the requirements documentation for information about the applications that will be running on each instance. During the pre-installation phase, you gather information about the number of users, the anticipated transaction per second, the size of the databases, and so on. With that information, settle on the amount of maximum memory that you want to allocate to each instance. When the applications are highly similar in terms of transactions per second, database sizes, and the like, you should divide the memory equally among the instances after leaving a couple of gigabytes for the operating system. Once you have set up the maximum memory configuration, you do not have to worry about one instance starving another.

The minimum amount of memory is usually easy to configure and does not require as much consideration. The primary purpose of setting the minimum memory configuration on a server that has multiple instances is to ensure that an instance has enough memory to perform as expected. SQL Server will only use the amount of memory needed to process requests from the applications. When all the memory of one instance is not being utilized, that instance may release memory back to the operating system, and another instance could reserve the memory. If the other instance is using the memory and no available memory remains for the instance that released the memory, then that first instance of SQL Server will perform poorly. Setting the minimum memory option will ensure that an instance does not release too much of its memory.