Reasonable configuration of SQL Server memory

Reasonable configuration of SQL Server memory

Original address: https://www.cnblogs.com/lyhabc/archive/2012/09/28/2707857.html

SQL Server is a program that likes memory resources, and his ideal state is to put all the data and structures that may be used in the physical memory to achieve optimal performance.

Although this is the case, SQL Server will constrain its memory usage, but it's best to make a reasonable allocation of SQL Server memory usage.

Where is SQL Server's memory used?

SQL caches a large number of data pages, and he caches a lot of other information, including the execution plan of the stored procedure, the security context of the particular user, etc.

If this information is not cached in the database, SQL has to recalculate it again and spend extra time, so SQL Server has a very strong need for memory.

Configuration:

(1) If the server supports 64-bit operating systems, please install the 64-bit system for no special reason. This allows SQL to effectively use more than 2GB of memory. If you must use a 32-bit system, be sure to open "Use AWE allocated memory" inside the SQL Server properties. However, do not use the/3GB switch in the boot. ini file, that is, do not add/3GB to the boot. ini file.

(2) Try to make the server dedicated to the database, and do not install other services (such as IIS, middle tier application services, etc.) on the same machine. Multiple production application services running on the same machine can significantly increase maintenance costs

(3) It is recommended that you set SQL Server Max memory (the maximum) to ensure that Windows has enough memory for the system to use.

The situation is summarized as follows:

4GB machine, 1GB recommended, set SQL Server max server memory to 2.8GB

8GB machine, 2GB recommended, set SQL Server max server memory to 6GB

A 64-bit machine larger than 8GB, it is recommended to reserve 3GB~4GB

If there are other apps on one server that use memory, also deduct their memory usage

General SQL Server min server memory (minimum servers RAM) is not very meaningful

(4) If it is an Enterprise Edition SQL Server, it is recommended to assign SQL Server startup account lock page in memory permission

The Setup method is as follows:

Open Group Policy (run, enter gpedit.msc)-"Find Computer Configuration-" Windows Settings-"Security settings-" Local Policies-"User Rights Assignment-" Lock memory page
Right-click on the Lock memory page, open the Lock memory page properties, add SQL Server startup account in

(5) "Set working set Size" This SQL Server parameter does not play the role of fixed SQL physical memory on Windows now, so please never use

After modifying the configuration, it is recommended to restart the SQL Server service!!!!!

Reasonable configuration of SQL Server memory