Server Properties (Memory Page)
Use this page to view or modify your server memory options. When Minimum server memory is set to 0 and Maximum server memory is set to 2147483647, SQL Server can take advantage of the optimum amount of memory at any given time, subject to how much memory the operating system and other applications are currently using. As the load on the computer and SQL Server changes, so does the memory allocated. You can further limit this dynamic memory allocation to the minimum and maximum values specified below.
Options
- Use AWE to allocate memory
Specifies that SQL Server will take advantage of Address Windowing Extensions (AWE) in Microsoft Windows 2000 and Windows Server 2003 to support up to 64 gigabytes (GB) of physical memory. AWE only applies to 32-bit operating systems. To use AWE you must configure Windows settings in addition to this SQL Server setting. To set this option, you must configure the lock pages in memory policy. For instructions about setting the policy, see How to: Enable the Lock Pages in Memory Option (Windows).
- Minimum server memory (in MB)
Specifies that SQL Server should start with at least the minimum amount of allocated memory and not release memory below this value. Set this value based on the size and activity of your instance of SQL Server. Always set the option to a reasonable value to ensure that the operating system does not request too much memory from SQL Server and inhibit Windows performance.
- Maximum server memory (in MB)
Specifies the maximum amount of memory SQL Server can allocate when it starts and while it runs. This configuration option can be set to a specific value if you know there are multiple applications running at the same time as SQL Server and you want to guarantee that these applications have sufficient memory to run. If these other applications, such as Web or e-mail servers, request memory only as needed, then do not set the option, because SQL Server will release memory to them as needed. However, applications often use whatever memory is available when they start and do not request more if needed. If an application that behaves in this manner runs on the same computer at the same time as SQL Server, set the option to a value that guarantees that the memory required by the application is not allocated by SQL Server.
- Lock working set (in MB)
Specifies a fixed amount of memory for SQL Server to use, turning off dynamic memory allocation. If this option is selected, enter the fixed amount of memory to use in the associated text box. This option only appears for SQL Server 2000 servers.
Index creation memory (in KB, 0 = dynamic memory)
Specifies the amount of memory (in kilobytes) to use during index creation sorts. The default value of zero enables dynamic allocation and should work in most cases without additional adjustment; however, the user can enter a different value from 704 to 2147483647.Note
Values from 1 to 703 are not allowed. If a value in this range is entered, the field overrides the entered value with 704.
- Minimum memory per query (in KB)
Specifies the amount of memory (in kilobytes) to allocate for the execution of a query. The user can set the value from 512 to 2147483647. The default value is 1024.
- Configured Values
Displays the configured values for the options on this pane. If you change these values, click Running Values to see whether the changes have taken effect. If they have not, the instance of SQL Server must be restarted first.
- Running Values
View the currently running values for the options on this pane. These values are read-only.
See Also
Other Resources
Enabling AWE Memory for SQL Server
Enabling Memory Support for Over 4 GB of Physical Memory
awe enabled Option
Using AWE
Setting Server Configuration Options
Server Memory Options
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
14 April 2006 |
|