SQL Server database performance optimization technology page 1/2

Designing an application system does not seem difficult, but it is not easy to optimize the system performance. In the development of tools, database design, should
There are multiple options for application structure, query design, and interface selection, depending on the specific application requirements and the skills of the development team. This document uses SQL
Server is used as an example to discuss application performance optimization techniques from the perspective of backend databases and give 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 statements
The Server solution is often caused by poor database design, resulting in poor performance. 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. But table relationship
It may need to be processed through complicated merging, which will reduce the system performance. To some extent, non-standardization can improve the system performance.
You can use different methods based on different performance considerations. However, the following methods are proven 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 (the plan at the beginning of the year + the Second Plan + the adjustment plan + the replenishment plan) is frequently used by users in queries and reports. When the number of records in the table is large
Add the total number of plans as one independent field to the table. 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
You must copy the primary keyword 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 ). In common applications
Historical records must be retained, but they are rarely used. Therefore, we can separate frequently accessed data from less accessed historical data. And if the data row
This method is also good if it is accessed as a subset by a logical Working Group (department, sales partition, geographical region, etc.
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 wide range of topics, 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
The smallint type does not use the integer type, so that the index field can be read more quickly and more data rows can be placed on one data page.
This reduces I/O operations.
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 can improve the performance.
Yes. 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.
High 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 smart Controller
Step by step to 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 constitute the hardware platform,
Windows NT and SQL Server run on it.
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 is the most
Less than one 80586/100 processor. If only 2 ~ This is enough for three users. However, if you plan to support more users and key applications, we recommend that you use
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 cache, data, and cable
Cited cache, static server expenses, and set expenses. SQL Server can use up to 2 GB of virtual memory, which is also the maximum setting value. Another thing that must be considered
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 reflected by the Windows NT virtual memory manager (VMM ).
It can reach 4 GB on some hardware platforms. SQL Server applications only know virtual addresses, so they cannot directly access the physical memory.
Access is controlled by VMM. Windows NT allows the generation of virtual address space that exceeds the available physical memory, so that when the virtual memory allocated to SQL Server is large
When the physical memory is available, the performance of SQL Server is reduced.
These address spaces are specially set for the SQL Server system. Therefore, if there are other software (such as file and print sharing) on the same hardware platform
And so on. Generally, the hardware platform requires at least 32 MB of memory, of which, Windows NT
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, you must at least
32 MB + 100 users * kb = 42 MB memory, the actual usage needs to be adjusted according to the actual running situation. It can be said that increasing the memory improves the system performance.
The most economical way.
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 controller.
Device and one or more hard disk units, as well as disk settings and file system considerations. Intelligent SCSI-2 disk controller or disk group controller is a good choice
The 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 ).
3. Search Policy
After carefully selecting the hardware platform and implementing a good database solution, and having the knowledge of user needs and applications, we should design the query
Query and index. It is very important to achieve good query and index performance on SQL Server. 1st is based on the SQL Server optimizer.
Knowledge generation query and indexing; 2nd is to enhance data access operations by taking advantage of the performance characteristics of SQL Server.
3.1 SQL Server Optimizer
The Microsoft SQL Server database kernel uses a cost-based query optimizer to automatically optimize the data query operations submitted to SQL. Data operation query is
Queries that support SQL keywords WHERE or HAVING, such as SELECT, DELETE, and UPDATE. Fee-based Query Optimizer generates clause fees based on statistical information
Use Estimation.
An easy way to understand the optimizer's data processing process is to check the output of the SHOWPLAN command. If you use a character-based tool (such as isql), you can use
Type show showplan on to get the output of the SHOWPLAN command. If graphical query is used, such as the query tool in SQL Enterprise Manager or
Isql/w. You can set configuration options to provide this information.
SQL Server is optimized in three phases: query analysis, index selection, and merge selection.
1. query and Analysis
In the query and analysis phase, the SQL Server optimizer checks every clause represented by a regular query tree and determines whether it can be optimized. SQL Server
Will try to optimize the clauses that restrict scanning. For example, search and/or merge clauses. However, not all valid SQL syntaxes can be divided into optimized clauses, as shown in figure
The clause containing the SQL unequal relational character "<>. Because "<>" is an exclusive operator, rather than an included operator, it is not included before scanning the entire table.
To determine the scope of clause selection. When a relational query contains an unoptimized clause, the execution plan uses table scan to access this part of the query,
For SQL Server clauses that can be optimized in the query tree, the optimizer selects indexes.