Shulou(Shulou.com)05/31 Report--

This article is about how the SQLServer database server expands its memory. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

First, let the database application support the memory space of 3GB

Although the operating system supports 4GB memory. However, this is not all for use by applications such as databases. By default, in a 32-bit operating system, there will be 2GB memory space reserved for the operating system. Even if it's not used up, other applications can't get their hands on it. All applications, including SQLServer databases, can only use the remaining 2GB memory space.

However, in practical applications, the operating system often does not need much 2GB of memory. According to the author's experience, it is generally enough to reserve 1G of memory for the operating system. As long as there are no viruses and other adverse factors, this memory will not be fully applicable. In this way, the application can use as much as 3G memory space, a full gigabyte more than before.

In fact, it is very simple to achieve this change. In the Windows operating system, there is a BOOT startup configuration file. In order for the database server to support 3GB's user-mode process space, you must add a parameter of / 3gb to this configuration file, and then restart the operating system. After this setting, the application can address the process address space of the 3GB and reserve the memory space of the 1GB for the operating system.

Sometimes, this small configuration can greatly improve the performance of the database. I remember that once, the author optimized database performance for an enterprise. After looking at the user's database environment, the author recommends that the user increase the memory of the database server from 2G to 4G. However, the effect has not been greatly improved. When the author was at a loss, I thought of changing the memory allocation of operating systems and applications. To this end, the author changed the BOOT startup configuration file to reserve only 1G of memory for the operating system. After restarting, the performance of the database has been greatly improved.

Enable higher memory support for SQLServer

If the database administrator is not satisfied after the memory addressing space of the database application reaches 3GB, it is necessary to increase the physical memory to improve the performance of the application. It is not impossible for the server operating system to break through the limit of its default 4GB memory space and support memory space above 4GB. It just requires additional configuration, and the workload of maintenance is relatively large.

If you want a SQLServer database to support memory addressing space above 4GB, you often need to configure it as follows.

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 have any impact on virtual memory. Therefore, you need to lock the memory page. The main purpose of locking memory pages is to determine which accounts can use processes to keep data in physical memory, thereby preventing the system from paging data into virtual memory on disk. By default, this option is only OFF. In other words, when necessary, the system will page the data into the virtual space of the hard disk. In order to maximize the effectiveness of memory, you need to turn this option on. However, this database administrator often needs to seek the help of the system administrator, because only users with system administrator privileges can change this option.

Step 2: enable the AweEnable option.

By default, even if the server operating system supports memory space above 4GB, database applications do not necessarily support it. In order for the SQLServer application to support this, you must change the configuration parameters of the database. That is, 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'aweenabled',1. However, before you do this configuration, you need to pay attention to two details. One is that the database user needs permission for this operation. Second, there is a BUG, that is, there will be an error message in the SQLServer database. The database administrator can ignore this information.

Step 3: limit file system caching.

If the additional memory is used by the operating system or other applications, the database administrator will be busy in vain. For this reason, the database administrator also needs to optimize the use of database system memory. If you need to limit the amount of memory the system uses for file caching. If you want to deal with it this way, you only need three simple steps.

First, in the operating system, the database administrator finds the control panel, double-clicks the network connection, and then selects the local connection. Second, double-click the local connection, in the pop-up dialog box, locate the General tab, and click Properties. Select the network file to share with the printer and click Properties. Finally, in the pop-up dialog box, remove the maximize network application data throughput check box. Just press confirm all the way. With this simple step, you can optimize database memory usage.

3. Several key points of maintenance and management of large memory

In general, memory above 4GB is often not required. However, other application services are enabled on the server at the same time. If there are database applications, mail applications, file servers and other application services on one server at the same time, the original 4GB memory may not be satisfied. The system administrator has to upgrade the memory. However, after the memory upgrade, the database administrator needs to manually intervene in the memory allocation so that the SQLServer application will not occupy more memory space and affect the performance of other applications.

1. Configure maxservermemory options. Although this option does not have to be modified, the author strongly recommends that database administrators change this option. Especially when database applications share the same server with other applications. Because after starting SQLServer's support for large memory (set AweEnabled 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 consume almost all available memory (whether it is needed or not, the database server program will lock that memory first. This is called not taking a shit in the manger. This maxservermemory option is used to configure the maximum amount of memory it can consume. 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 to have a negative impact on the sex of other applications. In extreme cases, you can close the database application before upgrading 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 memory space previously required for other applications. Otherwise, it will have a negative impact on other applications. Sacrificing the performance of other applications to improve the performance of the database is a way of tearing down the west wall to make up for the east wall, which is not worth taking.

2. How to allocate memory for multiple SQLServer instances. Often in a single SQLServer database, multiple database instances are configured. One database instance is used to run the ERP system, and the other is used by the CRM system. There are multiple database instances in the same database system, so how to allocate the available memory among the instances is different depending on the operating system. If the database operating system is 2000 series, you need to configure the maxservermemory option for each database instance. Otherwise, it is possible for one of the database instances to lock all available memory. This is mainly because the 2000 series of server systems do not support dynamic allocation of large memory. So you need to configure this option for each database instance. However, if the database server uses the 2003 series server operating system, there is no need to manually configure these parameters. Because the operating system allocates memory dynamically. That is, the operating system balances the use of memory between instances of SQLServer in accordance with the overall system requirements. However, in order to improve the overall performance of the database, it is best to configure maxservermemory; for each instance without being balanced by the operating system.

Thank you for reading! This is the end of the article on "how to expand the memory of the SQLServer database server". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.