Talking about the strategy of optimizing the memory configuration of SQL Server database servers

server|sqlserver| Strategy | server | data | database | optimization

Since 1998, the Bank of Agriculture has officially promoted a new version of the Network integrated Business Statistics Information system, the system is based on the WindowsNT4.0 platform, using the client/server model, based on Microsoft SQL Server-built large database applications, system interface friendly, The operation is simple, calculation, analysis, retrieval function is very strong, in order to ensure that the agricultural banking system in time for vertical and horizontal business data collection, according to different requirements to generate statistical reports, a comprehensive business activity analysis provides a strong guarantee. But in the promotion of the program, maintenance of the author found that the system is sometimes slow to run, especially in the Win95 client operation is particularly serious, after the elimination of cable connections and other hardware may have the impact of the above problems still exist. The author after careful exploration, found that the system of hard, software requirements, in order to give full play to the design efficiency, to achieve the best operating results, the computer hardware and software systems need to carry out a more complete performance testing and optimal configuration, especially the good or bad memory configuration of the system running speed has a decisive role Next, I put forward some understanding and views on how to optimize the memory configuration of SQL Server database servers.

The basic concept of memory

1 Physical and virtual memory
WindowsNT uses two types of memory: physical memory and virtual memory.
Physical Memory: A memory installed as a RAM chip inside a computer.
Virtual Memory: The disk (hard disk) space used to simulate the functionality of the RAM chip, essentially by temporarily storing portions of content that are not currently in memory to disk, allowing the system to use more memory than machine physical memory.
2 pagination and Paging files
The WINDOWSNT system uses disk space to partially mitigate the need for memory, and the technology used to use more memory than physical memory is referred to as "swapping" or paging, which is often called virtual memory technology. Typically, a 16MB exchange (paging) file (Pagefile.sys) is set on the boot drive on a Windows NT 4.0 system installation.

Second, optimize the Windows NT 4.0 system memory configuration

In most cases, in order to give full play to Windows NT 4.0 system performance, memory is more influential than processor processing power, especially in the case of client/server mode, because the processor's ability is often not emphasized in this environment, On the contrary, it is very important to use enough memory to meet the needs of each customer's application. In addition, in order to obtain fault tolerance and protect the application, it is necessary to have enough memory for the application to run at high speed and to give full play to the design function, especially the industrial drawing design and the various engineering applications need to occupy a large amount of memory for complex calculation.
Physical memory (RAM) The advantages of convenient and fast is obvious, but because of its expensive, it is impossible to do the more and more, so through reasonable optimization of memory configuration, expansion of virtual memory to improve computer operation Speed has become a very important application of technical means.
1 guarantees basic memory requirements for Windows NT systems
Windows NT 4.0 system to a small should be configured 12MB memory, 16MB memory is basically enough, normally ensure that the NT system has 32MB memory can be, because not all of the 16MB basically exist at any time are used simultaneously. If you add some services and applications, the need for memory increases dramatically. Such as:
(1) Adding network services requires 4MB of memory space;
(2) fault-tolerant function and system protection function need 8MB memory (such as disk mirroring and strip function);
(3) The need to increase 16MB memory space for graphic image processing;
(4) Install VC, VB "TARGET=_BLANK>VB development system needs to increase 16MB memory space;"
In addition, if you build large databases such as Sybase, Microsoft SQL Server on Windows NT, you need more memory.
2 Optimized Memory performance
In order to keep windowsnt from consuming too much memory or wasting the processor's time for page changes, you can optimize memory performance in the following ways.
(1) Reduce the number of display colors;
(2) reduce the display resolution;
(3) Do not use or use the small bit width of the wallpaper as far as possible;
(4) Close unwanted service programs or drivers and try not to use other applications on the server.
The steps for deactivating a service or driver are as follows:
① determine the name of the service or driver that needs to be deactivated;
② from the Control Panel, double-click the Service or device icon;
③ Select the name of the service or device driver you want to deactivate in the list, click the Stop button, and the Confirmation Action dialog box appears.
④ Select Yes to confirm the operation, and then close the dialog box to complete the setup.
3 Optimize virtual memory
When you set up Windows NT virtual memory, you need to reasonably determine the start size and maximum two parameters for each drive paging file, which specifies the starting space and maximum space for the paging file. Here are some explanations for these two parameters:
Start size: Refers to the file size when the paging file was initially created, in megabytes, which, by default, is set to the size of the physical memory in the system.
Maximum value: Indicates the maximum size, in megabytes, of the paging file.
(1) The setting principle of the paging file:
① The starting size of the paging file should remain the default setting, and do not change it in general;
The ideal maximum size for ② paging files is 2.5 times to 4 times times the size of the system's physical memory. Note that if the system does not require a large amount of memory to work, select a value near the lower bound, that is, 2.5 times times the physical memory of the system as the starting value of the dimension; If the system needs a lot of memory to work, select a value near the upper bound.
(2) Windows NT Virtual Memory setup steps:
① from the Control Panel, double-click the System icon;
② in the System Features dialog box, click the Performance tab;
③ Click the Change button in the Virtual Memory dialog box, the Virtual Memory dialog box appears, and the Upper drive box lists the size of all the Windows NT paging files.
④ in the drive list, select the drive letter where you want to set the paging file, and in the drive paging File Size dialog box, list the starting size and maximum two parameter columns, and fill in the values determined by the above principles;
⑤ Click Settings to confirm the above actions, and then click OK to exit each dialog box to complete the setup.
(3) WIN95/98 virtual memory settings.
WIN95/98 virtual memory settings methods, procedures, and principles are approximately the same as those for Windows NT 4.0, please refer to the Windows NT settings above.
4 Matters needing attention
(1) Reasonable determination of the maximum paging file, according to the system needs to adjust at any time, the use of too much virtual memory will lead to a decline in the overall system processing performance. The goal of setting the maximum value for virtual memory is to make it unnecessary for users to consume too much disk space on a windowsnt swap file, and the disk space to generate the swap file is wasted if the optimal value of the system is exceeded.
(2) As far as possible set up a dedicated hard disk configuration memory swap area, or place the swap space on another partition on the primary hard drive, and reduce the Exchange file size of the primary hard disk to 16MB so that the primary hard drive (partition) is used only to place the operating system and the application, reducing the number of exchanges and preventing frequent swapping and time-consuming CPU time.
(3) Virtual memory technology does improve the performance of Windows NT system, but also by the machine hard disk space size, hard disk speed, processor (CPU) speed, from the ideal point of view, to improve the performance of the computer must reduce the number of exchange operations, However, none of the WindowsNT computers do not exchange, and this requires that the computer has sufficient physical memory to maintain a minimal exchange operation.

III. optimizing the Microsoft SQL Server database memory configuration

Memory is an important factor that affects the performance of Microsoft SQL Server systems, and SQL Server database installation will default to 16MB available memory for machines with 32MB physical memory, and 16MB physical memory machine defaults to 4MB available memory. You should set the memory option (Memory) setting after the Microsoft SQL Server database is installed with a maximum configuration value of 2GB.
To determine the most appropriate memory requirements for a SQL Server system, you can subtract from total physical memory the memory required by Windows NT4.0 and some other memory requirements, ideally to allocate as much memory as possible to SQL Server without generating page scheduling.
1 logical programming of SQL Server available memory based on physical memory
In most production environments, the server is equipped with physical memory that is 64MB~128MB and occasionally 256MB, as long as it is properly configured to fully meet SQL Server memory requirements. The following table is the author's proposed plan for SQL Server memory allocation for reference.

Physical memory allocated to SQL Server set value (in 2KB)
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 is the SQL Server memory options (Memory) Setting method
(1) Start SQL Enterprise Manager from a Microsoft SQL Server assembly;
(2) Select the "Server" menu option from the Server Manager window;
(3) Select the "Configurations" option in the "Server" menu;
(4) in the "Server Configuration" dialog box, select the "Configuration" tab, Configuration window displays a list of configuration options;
(5) Select the "Memory" item and fill in the new value in the "Current" column;
(6) Stop and restart the SQL Server service for the settings to take effect.
2 reasonable expansion of virtual memory, increase SQL Server available memory
When the SQL Server system does need to expand available memory, expand it for virtual memory when there is sufficient disk space, and increase the available memory for SQL Server accordingly. In particular, the system administrator to expand the virtual memory of the server, and then refer to the table to increase SQL Server available memory, the key is to be based on the load of the system to decide whether to expand the memory, optimize the configuration.
3 Use Tempinram
SQL Server uses the tempdb staging database as a working space for sorting or creating temporary tables when some query connection operations are made. Creating tempdb in RAM can improve system operating performance, and because tempdb rebuilds every time the server is restarted, it is safer to have an abnormal shutdown, such as a power failure. To create tempdb in RAM, you can set it up using sp_configure, please refer to the information in the specific usage.
Because the memory used by Tempdbinram is allocated separately by the system from the memory body, the free memory pool that is set up with the memory option of SQL Server is separate, and using Tempdbin RAM reduces the available memory for the entire system and should be configured based on SQL Server and server health , otherwise it may backfire and affect system performance. In addition, adding the tempdb database space appropriately can improve the speed of the database even if you do not use Tempdbin RAM.
4 Matters needing attention
(1) It is recommended that SQL Server not set less than 32MB of memory in the production environment, and do not run other applications on the database server as far as possible;
(2) Expansion of virtual memory, increase SQL Server available memory, should consider physical memory usage and disk space licensing;
(3) Where possible, to leave the system with some extra memory, so that opening a service on the server or adding a process without changing the SQL Server memory configuration does not affect the speed of the NT Server (becoming very slow), it is generally assumed that the minimum is 2MB max 20MB.
Four, use the memory configuration skillfully, solves the statistic server problem

A unit of statistics server put into use, running slower, after troubleshooting reasons, found that the memory option in SQL Server (Memory) only for the installation of default--16MB (and the server has 128MB of physical memory), the memory value is adjusted to 100MB but mistakenly changed it to the 1000MB, the SQL Server service could not be started, the statistics database could not be opened, and the SQL Enterprise manager could not be allowed to modify the memory settings. Since the business data has not been backed up in the near future, and the SQL Server database cannot be reinstalled without the last resort, an attempt was made to use command-line parameter commands to restart it, but none worked and got into trouble. We have carefully analyzed that since SQL Server's available memory settings are much larger than physical memory, causing the SQL Server service to fail to start, why not expand virtual memory? After attempting to extend machine virtual memory to 1000MB and reboot, the SQL Server database started successfully. Problem solved.

V. Concluding remarks

Currently, most units put into use of Microsoft SQL Server database server physical memory is generally above 64MB, such as Agricultural Bank province, the city branch of the statistical server with 128MB physical memory, as long as the method proposed above to reasonably plan, optimize NT and SQL Server's memory configuration, so that the settings as far as possible, the application system will be able to give full play to the design function, meet business needs. -

Di Guang (Agricultural Bank of China, Jiangsu Province, Taizhou Branch Information Computer Center, Taizhou, Jiangsu 225300)

Reference documents

1,jim Boye Windows NT 4.0 installation& CONFIGURATION Beijing: Machinery Industry Press, 1997 7
2,microsoft Company Network Database System Management Microsoft SQL Server 6.0 Beijing: Science Press, 1997 1