Accelerator for large memory SQL Server database

Configuring larger memory for the database can improve database performance effectively. Because the database is running, a region is marked in memory as the data cache. Typically, when a user accesses a database, the data is first read into the data cache. The next time the user needs to access this data, it is read from the data cache. Because reading data in the data cache is hundreds of times times faster than reading the data on the hard disk. So expanding the database server memory can effectively improve the performance of the database, especially the operation of large database is more obvious effect.

However, the database servers commonly used in the enterprise now are 32-bit operating systems. This 32-bit operating system has the maximum memory usage limit. Typically, a standard 32-bit address can take up to 4GB of memory. Additional configuration is required if the database administrator wants the database system to use more memory to improve the performance of the database. The following is the introduction of two common configuration methods, so that SQL Server database servers to support large memory, so that it becomes a database accelerator.

One, let the database application support 3GB memory space

Although the operating system supports 4GB of memory. However, this does not all apply to applications such as databases. By default, in a 32-bit operating system, 2GB of memory space is reserved for the operating system. Even if you don't run out of work, other applications are not allowed to get in. All applications that contain SQL Server databases can only use the remaining 2GB of memory space.

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

To achieve this change, it is very simple indeed. In the Windows operating system, there is a boot boot configuration file. In order for the database server to support 3GB of user-mode process space, you must include a/3GB parameter in the configuration file, and then restart the operating system. Once this is set, the application can address the 3GB process addressing space and reserve 1GB of memory space for the operating system.

Sometimes, this small configuration can improve the performance of the database to a large extent. I remember once, the author for an enterprise optimization database performance. After reviewing the user's database environment, the author recommends that users increase the database server's memory, from 2G to 4G. However, the effect has not improved much. Just when I was in the wrong, I thought of changing the way the operating system and the application memory are allocated. To this end, the author changed the boot boot configuration file, only to the operating system to retain 1G of memory space. After the reboot, the database performance has been greatly improved.

Second, enable higher memory support for SQL Server

If the database application memory address space reaches 3GB and the database administrator is not satisfied, then you need to increase the physical memory to improve the performance of the application. It is not impossible for the server operating system to break the limit of its default 4GB memory space to support 4GB or above memory space. Only additional configuration is required, and the amount of maintenance is greater.

If you want the SQL Server database to support more than 4GB of memory addressing space, you often need to configure the following.

First step: Lock the memory page.

By default, there is a positive proportional relationship between the memory size and the operating system's virtual memory. Here, the database administrator simply wants to increase the physical memory of the server, rather than having any effect on the virtual interior. Therefore, you need to lock the memory page. The primary role 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. That is, when necessary, the system will paging data to the hard disk's virtual space. To maximize the usefulness of memory, you need to turn this option on. However, the database administrator often needs to seek the help of the system administrators, because only users with system administrator privileges will be able to change this option.

Step two: Enable the AWE enable option.

By default, database applications are not necessarily supported, even if the server operating system supports more than 4GB of memory space. For SQL Server applications 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, just use the command sp_configure ' awe enabled ', 1 can be. However, before making this configuration, you need to pay attention to two details of the content. The first is the permissions that the database user needs for this operation. Second, there is a bug where there is an error message in the SQL Server database. The database administrator can ignore this information.

Step three: Restrict file system caching.

If the increase in memory for the operating system or other applications, then the database administrator is not busy? For this reason, database administrators also need to optimize the use of database system memory. If you need to limit the amount of memory the system uses for the file cache. If you want to do so, just a simple three steps.

First, the database administrator in the operating system, locate the Control Panel, double-click the network connection, and then select the Local area connection. Second, double-click the local connection, and in the dialog box that pops up, locate the General tab and click Properties. Select the network file to share with the printer and click Properties. Finally, in the dialog box that pops up, remove the Maximize network Application data throughput check box. All the way to confirm. This simple step allows you to optimize the utilization of database memory.

Three, large memory maintenance management several key points

In general, it is often not necessary to enable more than 4GB of memory. However, other application services are enabled on the server as well. If you have multiple application services such as database applications, mail applications, and file servers on a single server, you may not have the original 4GB memory. The system administrator has to upgrade the memory. However, after the memory upgrade, the database administrator needs to manually intervene the allocation of memory to prevent SQL Server applications from consuming more 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, I strongly recommend that the database administrator modify this option. In particular, when a database application shares the same server as another application. Since SQL Server is enabled for large memory (set awe enabled to 1), the available physical memory is greater than user-mode process space. Then, when you start the database server, the running instance of SQL Server consumes almost all of the available memory (the database server program locks the memory first, regardless of whether you need to use it or not). It's called holding the manger and not taking a crap. The max server memory option is used to configure the maximum amount of memory it can occupy. The database administrator needs to estimate a reasonable number beforehand and then configure it. Allow database applications to work together with other application services, at least not adversely affecting the nature of other applications. In extreme cases, you can close the database application before you upgrade the memory, and then enable other application services. Watch for a while to see how much memory they need to use. It then upgrades the memory and retains at least the previously needed memory space 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, this is the demolition of the west wall to fill the east wall practice, not worth taking.

2. How many SQL Server instance memory is allocated. Often in a SQL Server database, multiple database instances are configured. One database instance is used to run the ERP system, and the other is to use it for the CRM system. In the same database system, where there are multiple database instances, how do you allocate the available memory between the instances? This is based on the operating system and differs. If the database operating system uses the 2000 series, you need to configure the max server memory option for each database instance. Otherwise, one of the database instances might lock all available memory. This is mainly because the 2000-series server system does not support the dynamic allocation of large memory. So you need to configure this option for each database instance. However, if the database server is using a 2003-series server operating system, you do not 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 SQL Server according to the overall system requirements. However, in order to improve the overall performance of the database, it is best to configure the max server memory for each instance instead of balancing the operating system.