Optimize the memory configuration of the sqlserver Database Server

Some ideas are raised 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, to achieve fault tolerance and protect applications Program To ensure the high-speed operation of applications and make full use of the design functions, enough 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) only for operating systems and applications, you 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)
8 Mb 4 MB 2048
16 MB 8 Mb 4096
32 MB 16 ~ 18 MB 8192 ~ 9216
48 MB 28 ~ 34 MB 14336 ~ 17408
64 MB 40 ~ 46 MB 20480 ~ 23552
128 MB 100 ~ 108 MB 51200 ~ 55296
256 MB 216 ~ 226 MB 110592 ~ 115712
512 MB 464 ~ 472 MB 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. To create tempdb in Ram, you can use sp_configure to set it. For more information about the usage, see.
Because the memory used by tempdbinram is separately allocated by the system from the memory body, it is separate from the available memory pool set by the memory option of SQL Server, the use of tempdbin Ram will reduce the available memory of the entire system and should be configured according to the SQL Server and server running conditions. Otherwise, it may be counterproductive and affect the system performance. In addition, appropriately increasing the tempdb database space can speed up database operation even if you do not use tempdbin Ram.
4. Notes
(1) we recommend that you do not set the SQL Server to be smaller than 32 MB in the production environment, and do not run other applications on the database server;
(2) physical memory usage and disk space license should be taken into account to expand the virtual memory for users and increase the available memory of SQL Server;
(3) If possible, leave some additional memory for the system, so that you can open a service on the server or add a process without changing the memory configuration of the SQL Server, it is generally considered that the minimum running speed of the NT Server is 2 MB and the maximum running speed is 20 mb.
4. Use memory configuration to solve the problem of counting servers

After a unit of statistical servers is put into use, the running speed is slow. After troubleshooting, the memory option (memory) in sqlserver is found) only the default value for installation-16 MB (the server has 1000 MB of physical memory) is changed to MB by mistake when the memory value is adjusted to MB, so that the SQL Server service cannot be started, if the statistical database cannot be opened, you cannot enter SQL Enterprise Manager again to modify the memory settings. Since business data has not been backed up recently, you cannot reinstall the SQL Server database as a last resort. Instead, you try to use the command line parameter command to restart the SQL Server service, but none of them work and are in trouble. After careful analysis, we put forward that since the available memory value of SQL Server is much greater than the physical memory, the SQL Server service cannot be started. Why not expand the virtual memory? After trying to expand the virtual memory of the machine to MB and restart it, the SQL Server database is successfully started and the problem is solved.

V. Conclusion

Currently, the physical memory of the Microsoft SQL Server database server that is put into use by most organizations is usually more than 64 MB. For example, the statistical server of the provincial and municipal branches of the Agricultural Bank is equipped with MB physical memory, as long as the memory configurations of NT and SQL Server are reasonably planned and optimized according to the method proposed above, and the settings are as optimal as possible, the application system will be able to give full play to the design functions and meet business needs.