Optimizing the memory configuration policy of the SQLServer database server (2) _ MySQL

Optimizing the memory configuration policy of the SQLServer database server (2) bitsCN.com

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 available SQL Server memory based on 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 is 16 MB (the server has 1000 MB of physical memory), but it is mistakenly changed to MB when the memory value is adjusted to MB, the SQL Server service cannot be started, and the statistical database cannot be opened. Therefore, 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.

BitsCN.com