Optimizes the memory Configuration Policy of the SQL Server.

It provides a strong guarantee for the Agricultural Bank system to collect vertical and horizontal business data in a timely manner and generate statistical reports according to different requirements for comprehensive business activity analysis. However, during the promotion and maintenance of this program, I found that the system sometimes runs slowly, especially when operating on the Win95 client, after removing the possible impact of network connection and other hardware, the above problems still exist. After careful exploration, I found that the system has high requirements on hardware and software. In order to give full play to the design efficiency and achieve the best operation effect, A complete performance test and optimal configuration of computer hardware and software systems are required, especially the memory configuration plays a decisive role in the system running speed. Next, I will give some ideas on how to optimize the memory configuration of the SQLServer database server.

I. Basic Concepts about memory

1 physical memory and virtual memory

WindowsNT uses two types of memory: physical memory and virtual memory.

Physical memory: Used as the memory of the RAM chip installed in the computer.

Virtual Memory: the disk (hard disk) space used to simulate the RAM chip function. Its essence is to temporarily store some unused content in the memory to the disk, this allows the system to use more memory than the physical memory of the machine.

2. Paging and paging files

The WindowsNT system partially mitigates memory requirements by using disk space. Therefore, the technology that uses more memory than physical memory is called "SWAp" or paging, that is, the virtual memory technology. During Windows NT 4.0 installation, a 16 MB swap (paging) file (pagefile. sys) is usually set on the boot drive ).

Ii. Optimize Windows NT 4.0 system memory configuration

In most cases, in order to make full use of the Windows NT 4.0 system performance, the role of memory is more influential than the processing capability of the processor, especially in the customer/Server mode environment, because in this environment, the processor capability is not very emphasized, but rather the memory is used to meet the application requirements of various customers. In addition, in order to gain fault tolerance and protect applications, ensure that applications run at high speed and make full use of the designed functions, sufficient memory is required, in particular, industrial drawing design and various engineering applications require a large amount of memory for complex computing.

The advantages of physical memory (RAM) For ease and speed are obvious, but because of its expensive price, it is impossible to do more and better, therefore, optimizing the memory configuration and expanding the virtual memory to increase the computing speed of the computer has become an important technical means of application.
1. Ensure basic memory requirements of the Windows NT System

From Windows NT 4.0 to small, 12 MB memory should be configured, and 16 MB memory is basically enough. Under normal circumstances, ensure that the NT System has 32 MB memory, because not all 16 MB memory is used at any time. If you add some services and applications, the demand for memory will increase dramatically. For example:

(1) adding a network service requires 4 MB of memory;

(2) The fault tolerance function and system protection function require 8 MB memory (such as disk image and shard function );

(3) adding 16 MB memory space for image processing;

(4) The installation of VC and VB development systems requires 16 MB of memory space;

In addition, for example, building large databases such as SYBASE and Microsoft SQL Server on Windows NT requires more memory.

2. Optimized memory performance

To prevent WindowsNT from occupying too much memory or wasting processing time for page feed, you can use the following methods to optimize memory performance.

(1) Reduce the number of display colors;

(2) reduce the display resolution;

(3) Try not to use or use wallpaper with a smaller bit width;

(4) Close unnecessary service programs or drivers and try not to use other applications on the server.

To stop a service or driver, follow these steps:

① Determine the name of the service or driver to be deactivated;

② Double-click the "service" or "device" icon from "Control Panel;

③ Select the name of the service or device driver to be deactivated in the list, and click "stop". The confirm operation dialog box is displayed;

④ Select "yes" to confirm the operation, and close the dialog box to complete the settings.

3. Optimize virtual memory

When setting Windows NT virtual memory, you need to properly determine the "Start size" and "maximum value" parameters of each drive paging file, which are used to specify the start space and maximum space of the paging file. The following two parameters are explained:

Start size: the size of the file when the paging file is initially created. The unit is MB. According to the default setting, this value is set to the physical memory size in the system.

Maximum Value: indicates the maximum size of the paging file, in MB.

(1) paging file setting principles:

① The default size of the start part of the paging file should be retained. Do not change the size normally;

② The ideal maximum size of paging files is 2.5 to 4 times the physical memory size of the system. Note: if the system does not require a large amount of memory, select a value close to the lower limit, that is, 2.5 times the physical memory of the system as the starting value of this size; if the system requires a large amount of memory, select a value close to the upper limit.

(2) Procedure for setting Windows NT virtual memory:

① Double-click the System icon from the control panel;

② Click the performance tab in the "system features" dialog box;

③ In the virtual memory dialog box, click the "change" button. The "Virtual Memory" dialog box appears. The upper drive box lists the size of all Windows NT page files one by one;

④ In the drive list, select the drive letter of the paging file to be set. In the "drive page file size" dialog box, the "Start size" and "maximum value" parameter columns are listed, enter the values determined based on the preceding principles;

⑤ Click "Settings" to confirm the preceding operations, and then click "OK" to exit the dialog box and complete the settings.

(3) Win95/98 virtual memory settings.

The methods, steps, and principles for setting Win95/98 virtual memory are roughly the same as those for Windows NT 4.0. Refer to the settings for Windows NT above.

4. Notes

(1) reasonably determine the maximum size of the paging file and adjust it at any time as required by the system. Using too much virtual memory will lead to a reduction in the processing performance of the entire system. The purpose of setting the maximum virtual memory value is to prevent the user from consuming too much disk space on the WindowsNT swap file. Generally, if the maximum value is exceeded, the disk space for generating swap files is wasted.

(2) try to set up a dedicated hard disk to configure a memory swap zone, or place the swap space in another partition of the primary hard disk. At the same time, the swap file size of the primary hard disk should be reduced to 16 MB, so that the primary hard disk (partition) for placement only
Operating systems and applications can reduce the number of exchanges and prevent frequent exchanges from consuming a large amount of CPU time.

(3) the virtual memory technology does improve the performance of the Windows NT system, but it is also affected by the hard disk space, hard disk speed, and processor (CPU) Speed of the machine. From an ideal point of view, to improve the performance of a computer, you must reduce the number of switching operations, but no WindowsNT computer does not exchange. This requires that the computer have enough physical memory to maintain a minimum of switching operations.

3. Optimize the memory configuration of the Microsoft SQL Server database

Memory is an important factor affecting Microsoft SQL Server system performance. When installing the SQL Server database, 16 MB of available memory is configured by default for machines with 32 MB physical memory, by default, the machine with 16 MB physical memory is configured with 4 MB available memory. You should set the Memory option (Memory) after installing the Microsoft SQL Server database. The maximum configuration value is 2 GB.

In order to determine the most suitable memory requirements for the SQL Server system, you can subtract the memory required for Windows NT4.0 and some other memory requirements from the total physical memory, the ideal situation is to allocate as much memory as possible to the SQL Server without generating page scheduling.

1. properly plan the available memory of SQL Server Based on the physical memory

In most production environments, the physical memory of the server is 64 MB ~ 128 MB, and occasionally MB, as long as the configuration is appropriate, it can fully meet the memory requirements of SQL Server. The following table lists the suggestions for SQL Server Memory allocation.

Physical memory allocated to SQL Server setting value (unit: 2 KB)

8MB 4MB 2048 16MB 8MB 4096 32MB 16～18MB 8192～9216 48MB 28～34MB 14336～17408 64MB 40～46MB 20480～23552 128MB 100～108MB 51200～55296 256MB 216～226MB 110592～115712 512MB 464～472MB 237568～241664

The following describes how to set the Memory option (Memory) of SQL Server:

(1) start SQL Enterprise Manager from the Microsoft SQL Server program;

(2) Select the "Server" menu option from the Server Manager window;

(3) Select the ations option from the "Server" menu;

(4) Select the Configuration tab in the "Server Configuration" dialog box. The Configuration window displays the Configuration option list;

(5) Select the "Memory" project and enter a new value in the "Current" column;

(6) stop and restart the SQLServer service to make the settings take effect.

2. Reasonably expand the virtual memory and increase the available memory of SQL Server

When the SQL Server system needs to expand the available memory, the virtual memory should be expanded when the disk space is sufficient, and the available memory of SQL Server should be increased accordingly. Specifically, the system administrator first expands the virtual memory of the Server, and then increases the available memory of the SQL Server by referring to the table above. The key is to determine whether to expand the memory and optimize the configuration based on the overall load of the system.

3. Use tempinRAM

SQL Server uses the temporary tempdb database as the workspace for sorting or creating temporary tables when querying connection operations. Creating tempdb in RAM can greatly improve system operability, And because tempdb is rebuilt every time the server is restarted, it is safer even if an abnormal shutdown occurs, for example, power failure.