MS SQL Server Max-Min Memory – PLE


Setting the Memory Options Manually. Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) that is managed by the SQL Server Memory Manager for a SQL Server process used by an instance of SQL Server. Error: PLE: Page Life Expectancy is number of seconds a page will stay in the buffer pool without references.

The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 MB. By default, SQL Server can change its memory requirements dynamically based on available system resources.

Set min server memory and max server memory to span a range of memory values. This method is useful for system or database administrators to configure an instance of SQL Server in conjunction with the memory requirements of other applications that run on the same computer.

Use min server memory to guarantee a minimum amount of memory available to the SQL Server Memory Manager for an instance of SQL Server. SQL Server will not immediately allocate the amount of memory specified in min server memory on startup. However, after memory usage has reached this value due to client load, SQL Server cannot free memory unless the value of min server memory is reduced.

OS Type – Minimum Memory Amounts Allowable for max server memory
32-bit – 64 MB
64-bit – 128 MB

How to configure memory options using SQL Server Management Studio
Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) managed by the SQL Server Memory Manager for an instance of SQL Server. By default, SQL Server can change its memory requirements dynamically based on available system resources.

To set a fixed amount of memory
In Object Explorer, right-click a server and select Properties.
Click the Memory node.
Under Server Memory Options, enter the amount that you want for Minimum server memory and Maximum server memory.
Use the default settings to allow SQL Server to change its memory requirements dynamically based on available system resources. The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 megabytes (MB).
Maximize Data Throughput for Network Applications
To optimize system memory use for SQL Server, you should limit the amount of memory that is used by the system for file caching. To limit the file system cache, make sure that Maximize data throughput for file sharing is not selected. You can specify the smallest file system cache by selecting Minimize memory used or Balance.
To check the current setting on your operating system
Click Start, click Control Panel, double-click Network Connections, and then double-click Local Area Connection.
On the General tab, click Properties, select File and Printer Sharing Microsoft Networks, and then click Properties.
If Maximize data throughput for network applications is selected, choose any other option, click OK, and then close the rest of the dialog boxes.
Lock Pages in Memory:

To disable the lock pages in memory option
On the Start menu, click Run. In the Open box, type gpedit.msc.
The Group Policy dialog box opens.
On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
Expand Security Settings, and then expand Local Policies.
Select the User Rights Assignment folder.
The policies will be displayed in the details pane.
In the pane, double-click Lock pages in memory.
In the Local Security Policy Setting dialog box, select the account with privileges to run sqlservr.exe and click Remove.

Virtual Memory Manager
The 32-bit operating systems provide access to 4 GB of virtual address space. 2 GB of virtual memory is private per process and available for application use. 2 GB is reserved for operating system use. All operating system editions include a switch that can provide applications with access up to 3 GB of virtual address space, limiting the operating system to 1 GB. How to use the switch memory configuration, see the Windows documentation about 4-gigabyte tuning (4GT). When the 32-bit SQL Server is running on 64-bit operating system its user available virtual address space is the full 4 GB.

The committed regions of address space are mapped to the available physical memory by the Windows Virtual Memory Manager (VMM).

The amount of physical memory supported by different operating systems, see the Windows documentation “Memory Limits for Windows Releases”.
Virtual memory systems allow the over-commitment of physical memory, so that the ratio of virtual-to-physical memory can exceed 1:1. As a result, larger programs can run on computers with a variety of physical memory configurations. However, using significantly more virtual memory than the combined average working sets of all the processes can cause poor performance.
The min server memory and max server memory options are advanced options. If you are using the sp_configure system stored procedure to change these settings, you can change them only when show advanced options is set to 1.
These settings take effect immediately without a server restart.

Amount of Memory to SQL Server : 

32-bit 64-bit
Conventional memory Up to process virtual address space limit in all SQL Server editions:

  • 2 GB
  • 3 GB with /3gb boot parameter1
  • 4 GB on WOW642
Up to process virtual address space limit in all SQL Server editions:

  • 8 TB on x6

More info please visit link site

1 /3gb is an operating-system boot parameter. For more information, visit the MSDN Library.

2 WOW64 (Windows on Windows 64) is a mode in which 32-bit SQL Server runs on a 64-bit operating system. For more information, visit the MSDN Library.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s