With dynamically configured memory, SQL Server configures memory usage automatically based on workload and available resources. Total memory usage varies between the minimum and maximum values that you set. The minimum server memory sets the baseline usage for SQL Server, but this memory is not allocated at startup. Memory is allocated as needed based on the database workload. When the minimum server memory threshold is reached, this threshold becomes the baseline, and memory is not released if it would leave SQL Server with less than the minimum server memory threshold.

To use dynamically configured memory, complete the following steps:

You can use the stored procedure sp_configure to change the minimum and maximum settings. Use the following Transact-SQL commands:

exec sp_configure "min server memory", <number of megabytes>
exec sp_configure "max server memory", <number of megabytes>

Best Practices

With dynamically configured memory, you usually do not need to set minimum and maximum memory usage values. On a dedicated system running only SQL Server, however, you may achieve smoother operation by setting minimum memory to 8 MB + (24 KB * NumUsers), where NumUsers is the average number of users simultaneously connected to the server. You may also want to reserve physical memory for SQL Server. SQL Server uses about 8 MB for its code and internal structures. Additional memory is used as follows: 96 bytes for locks, 2880 bytes for open databases, and 276 bytes for open objects, which include all tables, views, stored procedures, extended stored procedures, triggers, rules, constraints, and defaults. You can check the baseline memory usage using the SQLServer:Memory Manager performance object. Select all counters for monitoring and use the Report view to examine the memory usage. Pay particular attention to the Total Server Memory counter. See Chapter 13 for more details on monitoring SQL Server performance.

If you want to override the dynamic memory management features, you can do this by reserving memory specifically for SQL Server. When you reserve physical memory for SQL Server, the operating system does not swap out SQL Server memory pages even if that memory could be allocated to other processes when SQL Server is idle. This means SQL Server has a fixed memory set. On a dedicated system, reserving memory can improve SQL Server performance by cutting down on paging and cache hits.

To reserve physical memory for SQL Server, complete the following steps:

You can also use the stored procedure sp_configure to reserve physical memory. The Transact-SQL command to do this is:

exec sp_configure "set working set size", 1
go
exec sp_configure "min server memory", <number of megabytes>
go
exec sp_configure "max server memory", <number of megabytes>
go
reconfigure with override
go

Caution

Setting fixed working set memory incorrectly can cause serious performance problems on SQL Server. Use fixed working set memory only in circumstances in which you need to ensure that an exact amount of memory is available for SQL Server.

SQL Server 2005 Enterprise and Developer Editions support Address Windowing Extensions (AWE) memory. When AWE memory support is enabled, SQL Server 2005 dynamically allocates AWE memory at startup and allocates or deallocates AWE-mapped memory as required within the constraints of the minimum server memory and maximum server memory options. The goal is to balance SQL Server memory use with the overall system requirements. SQL Server always attempts to use AWE-mapped memory, even on computers configured to provide applications with less than 3 GB of user mode address space.

Note

When SQL Server 2005 runs on Windows Server 2003, Microsoft recommends enabling AWE memory support. The Hot-Add Memory feature requires AWE to be enabled during SQL Server startup. Additionally, it is important to note that SQL Server can dynamically release AWE-mapped memory, but the current amount of allocated AWE-mapped memory cannot be swapped out to the page file.

Tip

If you enable AWE support, the user or system account under which the instance runs must have the Lock Pages In Memory user privilege. This privilege can be assigned to the account using Group Policy. See Chapter 8 and Chapter 9 of the Windows Server 2003 Administrator’s Pocket Consultant for details.

To enable AWE support, complete the following steps:

You can also use the stored procedure sp_configure to enable AWE support. The Transact-SQL command you would use is:

exec sp_configure "awe enabled", 1

reconfigure

go

By default, SQL Server 2005 dynamically manages the amount of memory allocated for index creation operations. If additional memory is needed for creating indexes, and the memory is available based on the server memory configuration settings, the server will allocate additional memory for index creation operations. If additional memory is needed but not available, the index creation will use the memory already allocated to perform index creation.

Normally, the self-tuning works very well with this feature. The main exception is in cases in which you use partitioned tables and indexes, and have nonaligned partitioned indexes. In these cases, if there is a high degree of parallelism (lots of simultaneous index creation operations), you might encounter problems creating indexes. If this happens, you can allocate a specific amount of index creation memory.

To use a specific index creation memory allocation, complete the following steps:

You can also use the stored procedure sp_configure to set the index creation memory size. The related command is:

exec sp_configure "index create memory", <number of kilobytes>

Note

The amount of memory allocated to index creation operations should be at least as large as the minimum memory per query. If it is not, SQL Server will use the amount of memory specified as the minimum memory per query and display a warning about this.

By default, SQL Server allocates a minimum of 1024 KB of memory for query execution. This memory allocation is guaranteed per user, and you can set it anywhere from 512 KB to 2 GB. If you increase the minimum query size, you can improve the performance of queries that perform processor-intensive operations, such as sorting or hashing. If you set the value too high, however, you can degrade the overall system performance. Because of this, adjust the minimum query size only when you are having trouble executing queries quickly.

Best Practices

The default setting of 1024 KB of RAM works in most cases. However, you may want to consider changing this value if the server operates in an extremely busy environment, with lots of simultaneous queries running in separate user connections, or in a relatively slow environment, with few (but large or complex) queries. In this case, four factors should determine your decision to adjust the minimum query size:

Best Practices

Often a compromise is necessary with these values. You cannot always get an instant response, but you can optimize performance based on available resources.

Use the following equation to get a starting point for the optimization:

FreeMemory / (AvgQuerySize * AvgNumSimulQueries)

For example, if the system has 200 MB of free memory, the average query size is 2 MB, and the average number of simultaneous queries is five, then the optimal value for query size is 200 MB / (2 * 5) or 20 MB. Generally, this value represents the maximum you should assign given the current environment, and you will want to lower this value if possible.

To allocate memory for queries, complete the following steps:

You can also use the stored procedure sp_configure to set the minimum query size. The related command is:

exec sp_configure "min memory per query", <number of kilobytes>