SQL Server database performance optimization skills (1)

Designing an application system does not seem difficult, but it is not easy to optimize the system performance. There are multiple options in terms of development tools, database design, application structure, query design, and interface selection, depending on the specific application requirements and the skills of the development team. This article takes SQL Server as an example to discuss application performance optimization techniques from the perspective of backend databases, and provides some useful suggestions.

1. Database Design

To achieve optimal performance in a good SQL Server solution, the most important thing is to have a good database design solution. In practice, many SQL Server solutions tend to suffer poor performance due to poor database design. Therefore, to achieve a good database design, you must consider these issues.

1.1 logic library Standardization

In general, the logical database design meets the first three standards of standardization:

1. 1st specification: No repeated group or multi-value columns.

2. 2nd specification: each non-Keyword segment must depend on the primary keyword and cannot depend on some components of a combined primary keyword.

3. 3rd specification: one non-Keyword segment cannot depend on the other non-Keyword segment.

The design that complies with these rules produces fewer columns and more tables, which reduces data redundancy and data storage pages. However, the table relationship may need to be processed through complicated merging, which will reduce the system performance. To some extent, non-standardization can improve the performance of the system. The non-standardization process can be performed in a variety of different ways based on different performance considerations. However, the following methods have been verified to improve the performance.

1. If the Normalization Design produces many 4-or more-channel merging relationships, you can consider adding duplicate attributes (columns) to the database entity (table ).

2. Common calculated fields (such as total and maximum values) can be stored in database entities.

For example, a project's plan management system has a schedule, which includes the project number, the plan for the beginning of the year, the second plan, the adjustment plan, and the supplementary plan ..., The total number of plans (year-end plan + secondary plan + adjustment plan + column-filling plan) is frequently used by users in queries and reports. When the number of records in a table is large, it is necessary to add the total number of plans to the table as an independent field. A trigger can be used to maintain data consistency on the client.

3. Redefine entities to reduce spending on external attribute data or row data. The corresponding nonstandard types are:

(1) divide one entity (table) into two tables (all attributes are divided into two groups ). This separates frequently accessed data from less accessed data. This method requires that the primary keyword be copied in each table. This design facilitates parallel processing and generates tables with fewer columns.

(2) divide one entity (table) into two tables (all rows are divided into two groups ). This method applies to entities (tables) that will contain a large amount of data ). History is often retained in applications, but it is rarely used. Therefore, we can separate frequently accessed data from less accessed historical data. In addition, if the data row is accessed as a subset by a logical Working Group (department, sales partition, geographic region, etc.), this method is also very beneficial.

1.2 generate a physical database

To correctly select basic physical implementation policies, you must understand the Database Access format and operating features of hardware resources, mainly memory and disk subsystem I/O. This is a broad topic, but the following guidelines may be helpful.

1. Data Types related to each table column should reflect the minimum storage space required for data, especially for indexed columns. For example, if you can use the smallint type, do not use the integer type. In this way, index fields can be read more quickly and more data rows can be placed on one data page, therefore, I/O operations are reduced.

2. Put a table on a physical device, and then put its clustered index on a different physical device through the SQL Server segment, which improves performance. Especially when the system uses multiple smart disk controllers and data separation technologies, the benefits of doing so are even more obvious.

3. Use the SQL Server segment to split a frequently used large table and place it on two separate smart disk controller database devices. This can also improve performance. Because multiple heads are searching, data separation can also improve performance.

4. using SQL Server segments to store text or image column data on a separate physical device improves performance. A dedicated intelligent controller can further improve performance.

2. SQL Server-related hardware systems

The hardware design related to SQL Server includes the system processor, memory, disk subsystem, and network. These four parts basically constitute the hardware platform, where Windows NT and SQL Server Run on them.

2.1 system processor (CPU)

The process of determining the CPU Structure Based on your specific needs is the process of estimating the CPU usage workload on the hardware platform. From past experiences, the CPU configuration should be at least one 80586/100 processor. If only 2 ~ Three users, this is enough, but if you plan to support more users and key applications, we recommend using Pentium Pro or pⅱ CPU.

2.2 memory (RAM)

Determining appropriate memory settings for the SQL Server solution is crucial for achieving good performance. SQL Server uses memory for process caching, data and index item caching, static Server spending, and set spending. SQL Server can use up to 2 GB of virtual memory, which is also the maximum setting value. Another thing to consider is that Windows NT and all its related services also occupy memory.

Windows NT provides a 4 GB virtual address space for each WIN32 application. This virtual address space is mapped from the Windows NT virtual memory manager (VMM) to the physical memory, which can reach 4 GB on some hardware platforms. SQL Server applications only know virtual addresses, so they cannot directly access physical memory. This access is controlled by VMM. Windows NT allows the generation of virtual address space that exceeds the available physical memory. In this way, when the virtual memory allocated to SQL Server exceeds the available physical memory, the performance of SQL Server is reduced.

These address spaces are specially set for the SQL Server system. Therefore, if other software (such as file and print sharing and application service) is running on the same hardware platform, consider that they also occupy part of the memory. Generally, the hardware platform requires at least 32 MB of memory, of which, Windows NT requires at least 16 MB. A simple rule is to increase the memory size by kb for each concurrent user. For example, if there are 100 concurrent users, at least 32 MB + 100 users * kb = 42 MB memory is required. The actual usage needs to be adjusted according to the actual running conditions. It can be said that increasing memory is the most economical way to improve system performance.

2.3 Disk Subsystem

Designing a good disk I/O system is an important aspect of implementing a good SQL Server solution. The disk subsystem discussed here has at least one disk control device and one or more hard disk units, as well as considerations for disk settings and file systems. Intelligent SCSI-2 disk controller or disk group controller is a good choice, its features are as follows:

(1) Controller High-speed cache.

(2) The bus motherboard has a processor, which can reduce the interruption to the system CPU.

(3) asynchronous read/write support.

(4) 32-bit RAID support.

(5) Fast SCSI-2 drive.

(6) Advanced read cache (at least 1 track ).