Online Documentation for SQL Manager for SQL server

Memory options


The Memory section of the Server Properties dialog allows you to configure the instance of Microsoft® SQL Server by setting options pertaining to memory usage in groups: Server memory options, Other memory options.

 

Server Properties - Memory

 

Server memory options

 

RadioButton Dynamic memory configuration

Use the two server memory options, Minimum server memory and Maximum server memory, to reconfigure the amount of memory (in megabytes) in the buffer pool used by the instance of SQL Server.

By default, SQL Server can change its memory requirements dynamically on the basis of available system resources. The default setting for Minimum server memory is 0, and the default setting for Maximum server memory is 2147483647. The minimum amount of memory that can be specified for Maximum server memory is 16 MB.

 

RadioButton Locking working set

Allowing SQL Server to use memory dynamically is recommended; however, you can set the memory options manually and restrict the amount of memory that SQL Server can access.

 

CheckBox Use AWE memory

Under Windows Server 2003, SQL Server 2005 (and higher) can use Address Windowing Extensions (AWE) memory to further assist in balancing its own memory requirements with those of the operating system. This balancing between SQL Server and the operating system is subject to the constraints of the Minimum server memory and Maximum server memory options.

 

 

Other memory options

 

In SQL Server 2000/2005/2008 the Index creation memory option controls the maximum initial amount of memory allocated for index creation. If additional amount of memory is required for index creation operation, and it is available, the server will use additional memory thus exceeding the setting of this option. If additional memory is not available, the memory that has been already allocated will be used for index creation.

 

Use the Minimum memory per query option to specify the minimum amount of memory (in kilobytes) that will be allocated for execution of a query. For example, if Minimum memory per query is set to 2,048 KB, the query is guaranteed to get at least that much total memory. You can set Minimum memory per query to any value from 512 up to 2,147,483,647 KB (appr. 2 GB). The default value is 1,024 KB.