SQL Performance Optimization Summary (cont.)-SQL Statement Optimization Chapter

Tonight continue to the SQL performance problem sharing, today is mainly a number of specific SQL optimization methods and ideas to share, if you have other ideas later, welcome to explore together, well, into today's theme.

Optimized for specific applications where resource consumption is critical

What is the first thing to do when there is a performance problem? This question I asked a lot of colleagues, some people say that based on experience on the problem of SQL optimization, as we generally say to use the index reasonably, try not to use the front with the * number of like statements, do not compare operators before the calculation or use of functions and so on, these roads are right, but experience sometimes not necessarily solve the problem. When the problem arises, the first thing to do is to determine what the problem is, only the right to find the problem can be targeted to solve the problem. The following is a brief introduction to the general point of view, to determine where the problem lies.

1. First from the business understanding of the function, understand the user's true intentions, what the user is really concerned about, what data to want, whether there is a flexible approach to meet user requirements. Rather than using complex SQL queries. In fact, there are times when modifications can be made to achieve the same goal, but the SQL statements used have been greatly simplified. This is a priority to solve the effectiveness problem.

2. To optimize the fixed SQL, it is important to pay attention to the data volume of the query, the size of the data, some time the user to make a query, if the query conditions are not processed, the returned record set is too large, which for users, in fact, it is not very meaningful, the key is that this will inevitably lead to more disk IO, The problem of effectiveness is inevitable. Unless the user really needs this much data, it turns out that most of them are not, so the focus is on how to limit the size of the returned recordset or the size of the temporary intermediate data collection used in the query. This will make your optimization achieve effect, play a role.

The following is a brief introduction to several common methods of checking the problem SQL.

Of course there are some tricks, such as:

1. Using SET STATISTICS IO on to check the actual disk IO information, physical reading, logical reading and other information, this is a simple and effective reference data, in the author's previous experience, is also the main reference data.

Post the problem SQL in Query Analyzer, use SET STATISTICS IO as on, or right-click in the blanks, select < query options;

Select < Advanced >

Tick set Statistics Io.

To run the query, you can get the IO information related to this query in addition to the result set, such as:

We generally focus on the number of logical reads, when more than one table joint query, here will be the current table of the IO information, when a table logical read a large number of times, you have to focus on and analysis of the table, is not the query involved in the table in the number of records too much, is not reasonable use to index, Is it possible to add other filters to reduce the collection of related records and so on. Here's a simple explanation:

Output Items meaning

The name of the table sheet.

Number of indexes or table scans performed by Scan Count .

logical Reads the number of pages read from the data cache.

Physical reads the number of pages read from disk.

Read-ahead reads the number of pages to put in the cache for querying.

LOB Logical Reads the number of text, ntext, image, or large value types (varchar (max), nvarchar (max), varbinary (max)) pages that are read from the data cache.

LOB Physical reads the number of text, ntext, image, or large-value type pages read from disk.

LOB Read-ahead reads the number of text, ntext, image, or large-value type pages placed in the cache for querying.

Disk IO Related information is presented here, and another reference data is the number of milliseconds required to parse, compile, and execute statements using the SET STATISTICS time on reference. The specific use method is the same as SET statistics IO on, except that it shows the time information of the analysis compile, execution, etc. used in this query. Smart you must see it. Don't dwell on it here.

1. Use the SET STATISTICS profile on reference to display the configuration file information executed by the current statement, the execution of steps, and other information, using the same method.

After executing the query, in addition to displaying the result collection executed, it also shows the relevant configuration information of the SQL statement execution, which is displayed in the form of a record tree, corresponding to the steps in the execution plan, such as the type of index used in a step, the evaluation of the number of rows, IO information, time information, etc. This information can be used as a reference to determine where the problem with the SQL statement is.

Reference the estimated execution plan or actual execution plan of the current statement, and analyze the data retrieval method selected by the SQL Server query optimizer when the current statement executes.

The actual execution plan shows the execution plan used for this execution. The graph should look right-to-left, from the bottom up, if it is more than one table connection query, here will also show a number of execution steps, you can check each step related information about the operation, such as IO cost, CPU overhead, estimated number of rows, there is no use to index, and the use of what index and other information. There are too many rows to watch out for. The type of INDEXL used is also one of the information that needs attention.

Here is a brief description of some of the concepts in the execution plan:

Tool tip Items Description

Physical The physical operator used by operation, such as a Hash Join or Nested Loops. The physical operator shown in red indicates that the query optimizer has issued a warning, such as missing column statistics or missing join predicates. This may cause the query optimizer to select a query plan that is less efficient than expected. For more information about column statistics, see Using statistics to improve query performance.

Use the shortcut menu in SQL Server Management Studio Object Explorer to immediately create or update missing column statistics and indexes when the graphical execution plan suggests creating statistics, updating statistics, or creating an index. For more information, see the Index Operations Guide topic.

Logical operation The logical operators that match the physical operators, such as the Inner Join operator. The logical operators are listed after the physical operators, both at the top of the ToolTip.

Estimated the estimated size (in bytes) of rows generated by the row Size operator.

estimated The estimated cost of all I/O activities that are used to perform the operation. This value should be as low as possible.

Estimated CPU cost the estimated overhead for all CPU activity that performs the operation.

estimated Operator cost for the query optimizer to perform this operation. The cost of this operation is shown in parentheses as a percentage of the total cost of the query. This value should be as low as possible because the query engine chooses the most efficient operation to execute a query or execute a statement.

estimated subtree cost the query optimizer does this and the total overhead of all operations that precede this operation in the same subtree.

The number of rows generated by the estimated count of rows operator.

SQL Performance Optimization Summary (cont.)-SQL Statement Optimization Chapter