Tip: Work with Dynamically Configured Memory in SQL Server 2008

Follow Our Daily Tips

Twitter | Blog | RSS | Facebook

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 will leave SQL Server with less than the minimum server memory threshold.

To use dynamically configured memory, complete the following steps:
1. In the Server Properties dialog box, go to the Memory page.
2. Set the memory usage values to different values in the Minimum Server Memory and Maximum Server Memory boxes, respectively. The recommended maximum value for stand-alone servers is at or near total RAM (physical memory + virtual memory).However, if multiple instances of SQL Server are running on a computer, you should consider setting the maximum server memory so that the instances are not competing for memory.
3. Click OK.

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

T-SQL

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

PowerShell

Invoke-Sqlcmd -Query "exec sp_configure 'min server memory', <num mb>"
Invoke-Sqlcmd -Query "exec sp_configure 'max server memory', <num mb>"

Note 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 might 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 might 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, 2,880 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 by 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.

From the Microsoft Press book Microsoft SQL Server 2008 Administrator’s Pocket Consultant, Second Edition by William R. Stanek.

Looking for More Tips?

For more tips on using Microsoft products and technologies, visit the TechNet Magazine Tips library.