8.2 Configuring Memory Properties

8.2 Configuring Memory Properties

8.2.1 Viewing the Memory tab

Switch to the Memory tab to view the current memory configuration.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/6B/7E/wKiom1UvJV6wDsZJAAHPovaWiZw001.jpg "title=" memory. png "alt=" Wkiom1uvjv6wdszjaahpovawizw001.jpg "/>

Some configurations need to be restarted for this instance to take effect. Select run values below the right to see the configuration parameters that are currently in use, which are read-only, and select configuration values to view the configuration parameters that will take effect when the instance is restarted.

8.2.2 Memory Options

The Memory tab allows you to configure the following parameters.

Minimum server memory

Specifies the minimum amount of memory (in megabytes) that the instance should be allocated at least. After the instance restarts, the memory initially allocated may be lower than this parameter value and may exceed this parameter value if it encounters an increased load. When the load is reduced, the instance frees up some memory, but the minimum is no lower than this value.

This parameter is set to zero by default and is indicated by the operating system scheduler. Set this parameter value to a reasonable value based on the size and load of the instance to ensure that the operating system does not request too much memory for the instance to avoid slowing down Windows performance.

max server memory

Specifies the maximum amount of memory (in megabytes) that can be allocated when the instance starts and runs.

Applications typically use all available memory at startup, and if they are running on the same computer as the SQL Server instance at the same time, set this parameter to ensure that the instance can get enough memory.

This parameter is set by default to 2147483647 MB, which is indicated by the operating system scheduler. For 32-bit systems and 64-bit systems, the minimum amount of memory that can be specified for maximum server memory is two megabytes and MB, respectively.

CREATE index-occupied memory

Specifies the amount of memory (in kilobytes) to be used during index creation sorting.

This parameter value is set to zero by default, which means that dynamic allocation is enabled and, in most cases, it works without further adjustment. You can enter additional values from 704 to 2147483647. If the value entered is less than 704, the parameter entry will overwrite the value entered with 704.

The minimum memory consumed per query

Specifies the amount of memory (in kilobytes) allocated for execution of the query.

This parameter is set to 1024 by default. You can enter additional values from 512 to 2147483647.

8.2.3 options for Old products

Previous products for SQL Server 2012 may also have the following parameters.

Allocating memory using AWE

Specifies that SQL Server takes advantage of the AWE feature in 32-bit operating systems to support up to five GB of physical memory.

This setting applies only to 32-bit operating systems. To use AWE, in addition to this setting, you must also configure the PAE settings for Windows and configure the lock memory page policy.

SQL Server 2012 uses the new memory allocation mechanism and no longer supports AWE options.

Lock Working Set

Specifies the amount of fixed memory (in megabytes) used by SQL Server to turn off dynamic allocation. If you select this option, enter the fixed amount of memory to use in the associated text box. This option is displayed only for SQL Server 2000 servers.

This article from "SQLServer2014 series" blog, declined reprint!

8.2 Configuring Memory Properties