Overview of SQL Server performance optimization

Recently due to work needs, I hope that a more comprehensive summary of SQL Server database performance optimization related considerations, search on the internet, found a lot of articles, and some are listed hundreds, but look at the discovery, there are many specious or outdated (may be on the SQL SERVER6.5 the previous version or Oracle is suitable for the information, had to be based on the previous experience and test results summarized.

I always believe that the performance of a system, not only the test run or maintenance phase of the performance tuning task, and not only the development phase of things, but in the entire software lifecycle need attention, effective work can be achieved. So I want to summarize the considerations related to the database performance optimization according to the different stages of the software life cycle.

I. Analysis phase

In general, there are often too many areas of concern in the system analysis phase, system functionality, availability, reliability, security requirements tend to attract most of our attention, but we must note that performance is a very important non-functional requirements, must be based on the characteristics of the system to determine its real-time requirements, response time requirements , hardware configuration, and so on. It is best to have quantifiable indicators of various needs.

On the other hand, the type of the system should be differentiated according to various requirements in the analysis phase, which distinguishes between OLTP (online transaction processing systems) and OLAP (online analytical processing systems).

Second, the design phase

The design phase can be said to be the key phase of system performance later, at this stage, there is a relationship with almost all the performance tuning process-database design.

After the completion of the database design, you can conduct a preliminary index design, good index Design can guide the coding phase to write efficient code, for the performance of the whole system lay a good foundation.

Here's what the performance requirements design phase needs to be noted:

1, the standardization of Database logic design

The normalization of database logic design is what we generally call paradigm, so we can simply understand the paradigm:

1th Spec: There are no duplicate groups or multivalued columns, which is the minimum requirement for database design.

2nd Spec: Each non-critical field must rely on the primary key and cannot rely on some part of a modular primary keyword. Eliminate partial dependencies, and in most cases the database design should be in the second paradigm.

3rd specification: A non-critical field cannot be dependent on another non-critical field. Eliminating transitive dependencies, reaching the third normal form should be the requirement of most tables in the system, unless some special tables are in effect.

Higher paradigm requirements are no longer introduced here, personally, if all reached the second normal form, most of the third normal form, the system will produce fewer columns and more tables, thus reducing data redundancy, but also conducive to performance improvement.

2. Reasonable redundancy

It is almost impossible to design a system completely in accordance with the norm, unless the system is particularly small, it is necessary to systematically add redundancy after the normalization design.

Redundancy can be redundant databases, redundant tables, or redundant fields, and different granularity of redundancy can play a different role.

Redundancy can be increased for ease of programming, or for improved performance. From the performance point of view, the redundant database can disperse the database pressure, the redundant table can disperse the data large table's concurrent pressure, but also can speed up the special query speed, the redundant field can reduce the database table connection effectively, enhances the efficiency.