IX. OLTP performance adjustment and optimization-conclusion

OLTP performance adjustment and optimization-conclusion

According to the waterfall model of the software life cycle, the performance of an application has been qualitative in its design stage. If you think of optimization only after the application development is complete, you can only cure the problem. In the case of serious performance problems, you even need to re-launch the entire software.

When a software system has been launched for operation and needs to be optimized for database engines, follow the suggestions below.

I,Requirement Analysis and Design

Before performance optimization, you must have a detailed requirement analysis. Requirement analysis should include:

1. Business Requirements and Process Reengineering

Fully understand the business process and start from process optimization. Instead of copying the existing process, the requirement analysis and design personnel use the latest computer technology to transform the existing business process and achieve the goal of standardization and efficiency.

Business needs should be fully predicted and predicted for the potential operation pressure of computer technology and software systems in the future, at least to cope with the data growth and user access pressure in the future period. If performance optimization only meets the demand for a while and needs to be optimized again soon, the necessity of this optimization is worth discussing.

2. investment quotas and optimization objectives

The value created by performance adjustment and optimization projects is difficult for the existing business value evaluation system to calculate, so it is difficult to calculate ROI ).

In general, the performance optimization is the best, and it is enough to achieve the expected performance goal within the investment quota.

II. Implementation Method

1. Analyze the bottleneck

Master the working principles of SQL Server, understand the causes of bottlenecks from the underlying layer, and then make countermeasures. The general bottleneck occurs in:

1) server hardware

2) Operating System

3) SQLServer options

4) memory, CPU, disk I/O

2. segmented implementation

1)Stage 1:Establish performance baseline

Establishes performance standards for the current SQL Server System and quantifies them as much as possible. This serves as the basis for evaluating the effect. For example, you can use a performance counter to obtain the current memory, CPU peak, and average value. Another example is to run a complex query and record its time consumption.

2)Stage 2:Optimize System Resources

For example, enable AWE for a 32-bit system. This is the simplest and most meaningful job. It can bring obvious results to Performance Tuning projects. If the hardware bottleneck of the server is insufficient memory, the disk performance is low, and so on), or the operating system bottleneck is 32-bit, migrate SQLServer to the new server or reinstall the operating system, in some cases, the performance can be significantly improved.

3)Stage 3:Solve the bottleneck in sequence

Use counter, DMV, tracking and other technical means to identify the bottleneck and then remedy the problem.

4)Stage 4:Optimize T-SQL statements

If the program developer does not have the query optimization knowledge or does not know much about the SQL server bottleneck, the final delivery quality may be uneven. Some enterprises in China have even recruited several "code farmers" to undertake the "one-stop" service of requirement analysis, model design, code writing, and testing. The quality of the code can be imagined.

This article is from the "MSSQL we have Chased Together" blog. For more information, contact the author!