SQL SERVER performance optimization Summary (good summary, don't miss it) page 1/3

I. analysis stage
In general, in the system analysis phase, there are often too many places to pay attention to. The various functional, availability, reliability, and security requirements of the system often attract most of our attention. However, we must pay attention to the following, performance is an important non-functional requirement. You must determine the real-time requirement, response time requirement, and hardware configuration based on the characteristics of the system. It is better to have quantitative indicators of various needs.
On the other hand, in the analysis phase, the types of systems should be distinguished according to various requirements. In The Big aspect, the types should be OLTP (online transaction processing system) and OLAP (Online Analytical Processing System ).
Ii. Design Phase
The design stage can be said to be a key stage of system performance in the future. At this stage, there is a process related to almost all performance tuning in the future-database design.
After the database design is complete, preliminary index design can be carried out. A good index design can guide the coding stage to write efficient code, laying a good foundation for the performance of the entire system.
Pay attention to the following performance requirements during the design phase:
1,
Standardization of database Logic Design
The standardization of database logic design is what we generally call the paradigm. We can simply understand the paradigm as follows:
1st specification: there are no repeated groups or multi-value columns, which is the minimum requirement for database design.
2nd specification: each non-Keyword segment must depend on the primary keyword and cannot depend on some components of a combined primary keyword. Eliminate part of the dependency. In most cases, the database design should reach the second paradigm.
3rd specification: one non-Keyword segment cannot depend on another non-Keyword segment. To eliminate the transfer dependency, reaching the third paradigm should be the requirement of most tables in the system, unless there are tables with special functions.
The higher paradigm requirement is not introduced here. I personally think that if most of the requests reach the second paradigm, the system will generate fewer columns and more tables, this reduces data redundancy and improves performance.
2,
Reasonable redundancy
It is almost impossible to design a system completely according to the standardization. Unless the system is very small, it is necessary to add redundancy in a planned manner after the standardization design.
Redundancy can be a redundant database, a redundant table, or a redundant field. redundancy at different granularities can play different roles.
Redundancy can be increased for programming convenience or for performance improvement. From a performance perspective, redundant databases can distribute database pressure, while redundant tables can distribute the concurrent pressure on tables with a large amount of data and speed up special queries, redundant fields can effectively reduce database table connections and improve efficiency.
3,
Primary Key Design
The primary key is necessary. The primary key of SQL SERVER is a unique index at the same time. In practice, we often choose the smallest key combination as the primary key. Therefore, the primary key is usually suitable for table clustered indexes. Clustered indexes have a great impact on queries. This is an index description below.
In a table with multiple keys, the selection of the primary key is also important. Generally, the selection of keys with a small total length is faster, at the same time, a small key can reduce the B-tree hierarchy of the primary key.
When selecting a primary key, pay attention to the field order of the primary key combination. For the primary key combination, the performance of the primary keys in different field order may vary greatly, generally, you should select a field with a low repetition rate, a single field, or a combination of query possibilities.
4,
Foreign key design
Foreign keys are used as database objects, and many people think that they are troublesome. In fact, foreign keys are useful in most cases for the following reasons:
Foreign keys are the most efficient Consistency Maintenance Method. Database Consistency requirements can be followed by foreign keys, CHECK constraints, rule constraints, triggers, and client programs, the method closer to the data is more efficient.
Exercise caution when using cascade deletion and cascade update. Cascade deletion and cascade update are the new functions of SQL server 2000 in the current year. They are reserved in SQL SERVER 2005 and should be available. I am cautious about this because cascading deletion and cascading update break through the traditional definition of foreign keys, and the features are a little too powerful, before use, you must determine that you have mastered the function scope. Otherwise, cascading deletion and cascading update may cause your data to be inexplicably modified or lost. In terms of performance, cascading deletion and cascading update are more efficient than other methods.
5,
Field Design
Fields are the most basic unit of the database, and their design has a great impact on performance. Note the following:
A. Use the numeric type as much as possible for the data type. The numeric type is much faster than the numeric type.
B,
The data type should be as small as possible. The smaller value here refers to meeting the foreseeable future needs.
C,
Do NOT allow NULL unless necessary. Use not null + DEFAULT instead.
D. Use less TEXT and IMAGE. The reading and writing of binary fields is slow, and there are not many reading methods. It is best not to use them in most cases.
E,
Auto-increment fields must be used with caution, which is not conducive to data migration.
6,
Design of physical database storage and Environment
During the design stage, you can design the physical storage, operating system environment, and network environment of the database, in the future, our system will be able to adapt to a large number of concurrent users and a large amount of data.
Pay attention to the role of the file group. The applicable file group can effectively distribute I/O operations to different physical hard disks to improve concurrency.
7,
System Design
The design of the entire system, especially the system structure design, has a great impact on the performance. For general OLTP systems, you can choose a C/S structure and a three-layer C/S structure, the performance of different system structures varies.
In the system design phase, some business logic should be summarized in Database Programming implementation. database programming includes database stored procedures, triggers and functions. The advantage of using Database Programming to Implement business logic is to reduce network traffic and make full use of the database's pre-compilation and cache functions.
8,
Index Design
In the design phase, you can perform a preliminary index design based on the functional and performance requirements. here you need to design indexes based on the expected data volume and query, which may be different from actual use in the future.
You should change your mind about the selection of indexes:
A,
You can determine which tables need to add indexes based on the data volume. If the data volume is small, only the primary key can be used.
B,
Determines which fields need to be indexed Based on the frequency of use, and selects fields that are often used as connection conditions, filtering conditions, aggregate queries, and sorting as candidate fields of the index.
C,
Combine fields that often appear together to form a composite index. The field order of the composite index is the same as that of the primary key. You also need to put the most common fields in front, put the fields with low repetition rate in front.
D,
Do not add too many indexes to a table because the index affects the speed of insertion and update.
Iii. coding stage
The coding stage is the focus of this article, because the quality of coding almost determines the quality of the entire system.
In the coding stage, all programmers need to be aware of the performance, that is, to implement functions while considering the performance. The database is a tool that can perform aggregate operations. We should try our best to use this tool, the so-called set operation is actually a batch operation, that is, to minimize the number of large data loop operations on the client, instead of using SQL statements or stored procedures. It is hard to say clearly about ideas and consciousness. We need to understand it in the programming process.
The following are some precautions for programming:
1,
Only the required data is returned.
To return data to the client, you must at least extract data from the database, transmit data over the network, receive data from the client, and process data from the client. If no data is returned, it will increase invalid labor on servers, networks, and clients. The harm is obvious. To avoid such incidents, you must note:
A. in the horizontal view, do not write the SELECT * statement, but SELECT the fields you need.
B,
Vertically, write the WHERE clause properly. Do not write SQL statements without WHERE.
C,
Note the WHERE clause after select into. Because select into inserts data INTO the temporary table, this process locks some system tables. If the data returned by this WHERE clause is too large or too slow, the system table will be locked for a long time and other processes will be blocked.
D. For aggregate queries, you can use the HAVING clause to further limit the returned rows.
2,
Try to do less repetitive work
This is the same as the above, that is, to minimize ineffective work. However, the focus of this point is on the client program. Note the following:
A,
It is seldom noticed by many programmers to control multiple executions of the same statement, especially the multiple executions of some basic data.
B,
Data conversion may be designed to reduce the number of data conversions, but it is possible for programmers to reduce the number of data conversions.
C,
Eliminate unnecessary subqueries and connection tables. subqueries are generally interpreted as external connections in the execution plan, resulting in additional costs for redundant connection tables.
D,
Merge multiple updates for the same table with the same condition, for example
Update employee set fname = 'haiwer 'WHERE EMP_ID = 'vpa30890f'
Update employee set lname = 'yang' WHERE EMP_ID = 'vpa30890f'
These two statements should be merged into the next statement.
Update employee set fname = 'haiwer ', LNAME = 'yang'
WHERE EMP_ID = 'vpa30890f'
E,
The UPDATE operation should not be split into DELETE operations + INSERT operations. Although the functions are the same, the performance difference is great.
F,
Do not write meaningless queries, such
SELECT * from employee where 1 = 2
3,
Pay attention to transactions and locks
Transactions are important tools in database applications. They have four attributes: atomicity, consistency, isolation, and persistence. We need to use transactions to ensure data correctness for many operations. When using transactions, we need to avoid deadlocks and minimize blocking. Pay special attention to the following aspects:
A. The transaction operation process should be as small as possible, and the transactions that can be split should be split.
B,
There should be no interaction in the transaction operation process, because the transaction has not ended while waiting for interaction, and many resources may be locked.
C,
The transaction operation process needs to access objects in the same order.
D. Improve the efficiency of each statement in the transaction. Using indexes and other methods to improve the efficiency of each statement can effectively reduce the execution time of the entire transaction.
E,
Try not to specify the lock type and index. SQL SERVER allows us to specify the lock type and index used by the statement, but in general, the lock type and index selected by the SQL SERVER optimizer are optimal in terms of the current data volume and query conditions. What we specify is only available in the current situation, however, the data volume and data distribution will change in the future.
F,
You can use a lower isolation level when querying a report, especially when querying a report. You can select the lowest isolation level (uncommitted read ).
4,
Pay attention to the usage of temporary tables and table Variables
In complex systems, temporary tables and table variables are difficult to avoid. for usage of temporary tables and table variables, note the following:
A. if the statements are complex and have too many connections, you can use temporary tables and table variables for step-by-step execution.
B,
If you need to use the same part of the data of a large table multiple times, use temporary tables and table variables to store the data.
C,
If you need to combine the data of multiple tables to form a result, you can use temporary tables and table variables to summarize the data of these tables step by step.
D. In other cases, use of temporary tables and table variables should be controlled.
E,
Many statements about the selection of temporary tables and table variables are that the table variables are in the memory and are fast. Table variables should be preferred. However, in actual use, this option mainly considers the amount of data that needs to be stored in the temporary table. When there is a large amount of data, the temporary table is faster.