Large Memory SQL Server database accelerator

Configuring a large memory for the database can effectively improve the database performance. Because the database is running, a region is allocated in the memory as the data cache. Generally, when a user accesses a database, the data is first read to the data cache. The next time the user needs to access the data, the data will be read from the data cache. Because reading data in the data cache is several hundred times faster than reading data on the hard disk. Therefore, expanding the memory of the database server can effectively improve the database performance, especially when operating large databases.

However, the database servers currently widely used by enterprises are 32-bit operating systems. The 32-bit operating system has the maximum memory usage limit. Generally, the Standard 32-bit address can use a maximum of 4 GB memory. If the database administrator wants the database system to use more memory to improve the database performance, additional configuration is required. The following describes two common configuration methods to enable the SQLServer database server to support large memory and make it an accelerator for the database.

1. enable database applications to support 3 GB of memory space

Although the operating system supports 4 GB memory. However, this is not all used by databases and other applications. By default, 2 GB of memory is reserved for the 32-bit operating system. Other applications cannot be infected even if they are not used up. All applications, including SQL Server databases, can only use the remaining 2 GB of memory space.

However, in practical applications, the operating system usually does not need much 2 GB of memory. Based on my experience, it is generally enough to reserve 1 GB of memory for the operating system. As long as there are no viruses and other adverse factors, this memory will not be fully applied. In this case, the application can use up to 3 GB of memory space, an extra GB of memory.

It is actually very easy to achieve this transformation. In Windows, there is a boot startup configuration file. To enable the database server to support 3 GB user-mode process space, you must add a/3 GB parameter to the configuration file and restart the operating system. After this setting, the application can address 3 GB of process address space, while retaining 1 GB of memory space for the operating system.

Sometimes, this small configuration can greatly improve the database performance. I remember one time I optimized the database performance for an enterprise. After checking the user's database environment, I suggest you increase the memory of the database server from 2 GB to 4 GB. However, the effect has not been significantly improved. Just as I was confused, I thought of changing the memory allocation mode of the operating system and applications. To this end, I changed the BOOT configuration file and only reserved 1 GB of memory space for the operating system. After the restart, the database performance has been greatly improved.

2. enable higher memory support for SQLServer

If the Database Administrator does not meet the requirements after the memory addressing space of the database application reaches 3 GB, you need to increase the physical memory to improve the performance of the application. If the server operating system needs to break the default 4 GB memory space limit, it is not impossible to support more than 4 GB memory space. It only requires additional configuration, and the maintenance workload is also large.

If you want the SQLServer database to support memory addressing space of more than 4 GB, the following configuration is often required.

Step 1: Lock the Memory Page.

By default, there is a proportional relationship between the memory size and the virtual memory of the operating system. Here, the database administrator only wants to increase the physical memory of the server and does not want to affect the virtual memory. Therefore, You need to lock the Memory Page. The main function of locking the Memory Page is to determine which accounts can use the process to keep the data in the physical memory, thus preventing the system from paging the data to the virtual memory of the disk. By default, this option is only OFF. That is to say, when necessary, the system will paging the data to the virtual space of the hard disk. To maximize the memory usage, You need to enable this option. However, this database administrator often needs help from the system administrator, because only users with system administrator permissions can change this option.

Step 2: Enable the Awe Enable option.

By default, even if the server operating system supports more than 4 GB of memory space, database applications do not necessarily support it. To enable the SQL Server application to support this, you must change the database configuration parameters. That is to say, you need to set the value of this option to 1 and then restart the database system. This configuration is relatively simple. You only need to use the command sp_configure 'awe enabled', 1. However, you need to pay attention to two details before configuring this configuration. First, the database user needs the permission for this operation. Second, there is a BUG, that is, there is an error message in the SQL Server database. The database administrator can ignore this information.

Step 3: restrict the file system cache.

If the added memory is used by the operating system or other applications, isn't the database administrator busy? Therefore, the database administrator needs to optimize the memory usage of the database system. To limit the amount of memory used by the system for file caching. To do this, you only need three simple steps.

First, the database administrator finds the control panel in the operating system, double-click the network connection, and select the local connection. Next, double-click the local connection. In the displayed dialog box, find the General tab and click Properties. Select the network file to share with the printer, and click Properties. Finally, in the displayed dialog box, remove the "maximize network application data throughput" check box. All the way to confirm. This simple step can optimize the memory usage of the database.

Iii. Key Points of large memory maintenance and management

Generally, you do not need to enable memory larger than 4 GB. However, if other application services are enabled on the server. If a server has multiple application services, such as database applications, email applications, and file servers, the original 4 GB memory may not be enough. The system administrator has to upgrade the memory. However, after the memory upgrade, the database administrator needs to manually intervene in the memory allocation to avoid the SQL Server application occupying a large amount of memory space and affecting the performance of other applications.

1. Configure the max server memory option. Although this option does not have to be modified, the author strongly recommends that the database administrator modify this option. Especially when database applications share the same server with other applications. Because after SQLServer is Enabled for large memory (set Awe Enabled to 1), and the available physical memory is larger than the user mode process space. When the database server is started, the running SQLServer instance will occupy almost all the available memory (no matter whether you need it or not, the database server will first lock the memory. This is called taking care of the pitfalls ). The max server memory option is used to configure the maximum amount of memory that can be occupied. The database administrator needs to estimate a reasonable value in advance and then configure it. So that database applications and other application services can be improved together, at least not adversely affect the performance of other applications. In extreme cases, you can shut down database applications before upgrading the memory, and then enable other application services. Observe for a while to see how much memory they need. Then, upgrade the memory and reserve at least the previously needed memory space for other applications. Otherwise, it will have adverse effects on other applications. It is not worthwhile to sacrifice the performance of other applications to improve the database performance.

2. How to allocate memory for multiple SQLServer instances. Multiple database instances are usually configured in an SQLServer database. One database instance is used to run the ERP system, and the other is used by the CRM system. In the same database system, there are multiple database instances. How do I allocate available memory between instances? This is based on different operating systems. If the database operating system uses the 2000 series, you need to configure the max server memory option for each database instance. Otherwise, a database instance may lock all available memory. This is mainly because the 2000 Series server system does not support dynamic allocation of large memory. Therefore, you need to configure this option for each database instance. However, if the database server uses the 2003 series server operating system, you do not need to manually configure these parameters. Because the operating system dynamically allocates memory. That is to say, the operating system balances the memory usage between instances of SQLServer according to the overall system requirements. However, to improve the overall performance of the database, it is best to configure max server memory for each instance, rather than balance the operating system.