SQL Performance Optimization Summary (continued)-SQL statement Optimization

SQL Performance Optimization Summary (continued)-SQL statement Optimization

 

We will continue to share the SQL Performance questions tonight. Today we will share some specific SQL optimization methods and ideas. If you have read other ideas, please join us, enter today's topic.

 

Optimize specific applications with high resource consumption

 

What is the first thing to do when a performance problem occurs? I have asked many colleagues about this question. Some people say that they should optimize problematic SQL statements based on experience. For example, we generally say that we should reasonably use indexes and try not to use the Like statement with the "*" sign in front, do not compare the operator's front side for calculation or use of functions. These paths are correct, but experience sometimes may not solve the problem. When a problem occurs, the first thing to do is to determine what the problem is. Only after the problem is correctly identified can the problem be solved. The following is a brief introduction of how to determine the problem.

 

1. First, understand the functions of the Service, understand the real intention of the user, what the user really cares about, what the data he wants, and whether there are convenient and concise methods to meet user requirements. Instead of using complex SQL queries. In fact, some modifications can achieve the same purpose, but the SQL statements used have been greatly simplified. This is a top priority for solving performance problems.

 

2. when optimizing a fixed SQL statement, you must pay attention to the data volume related to the query and the size of the data volume. In some cases, you must make a query. If the query conditions are not properly processed, the returned record set is too large, which is of little significance to users. The key is that this will inevitably lead to a large number of disk IO, and performance problems are inevitable. Unless the user really needs so much data, but it turns out that most of them are not, so the focus is on how to limit the size of the returned record set or the size of the temporary intermediate data set used in the query. In this way, your optimization will be effective.

The following describes several common SQL methods for checking the problem.

 

Of course, there are some tips, such:

1. Use set statistics io on to check the actual disk IO information, physical reads, logical reads, and other information. This is a simple and effective reference data. In my previous experience, it is also the main reference data.

 

Paste the problematic SQL statement in the query analyzer and set statistics io to on. You can also right-click in the blank space and choose <query Options>,

Select <advanced>

Select Set Statistics Io.

 

In addition to the result set, you can also obtain the IO information related to this query, for example:

We generally focus on the number of logical reads. When multiple tables are jointly queried, the IO information of each table is displayed here. When the number of logical reads of a table is large, you need to focus on and analyze this table. Is it because the number of records in this table is too large during the query? Is the Index not properly used, can other filtering conditions be added to reduce related record sets. The following is a brief description:

 

Output itemDescription

TableTable name.

Scan countNumber of indexes or tables scanned.

Logical readsThe number of pages read from the data cache.

Physical readsThe number of pages read from the disk.

Read-ahead readsThe number of pages cached for query.

Lob logical readsThe number of pages of the text, ntext, image or partition type (varchar (max), nvarchar (max), and varbinary (max) read from the data cache.

Lob physical readsThe number of pages of the text, ntext, image, or volume type read from the disk.

Lob read-ahead readsThe number of cached text, ntext, image, or plain pages for query.

Disk I/O information is introduced here first. Another reference data is the number of milliseconds required to use the set statistics time on reference to display analysis, compilation, and execution statements. The specific usage method is basically the same as set statistics io on, but it only displays the time information of the analysis compilation and execution used for this query. You must understand it at first glance. I will not go into details here.

 

1. Use the set statistics profile on reference to display information about the configuration file and execution steps executed by the current statement. Use the same method as above.

 

After the query is executed, in addition to the execution result set, the SQL statement execution configuration information is also displayed in the form of a record tree, corresponding to each step in the execution plan, for example, the index type used in a step, the number of evaluated rows, IO information, and time information. This information can be used for reference to determine where the SQL statement of this segment is located.

 

Analyze the data retrieval method selected by the SQL Server Query Optimizer when executing the current statement by referring to the estimated execution plan of the current statement or the actual execution plan.