Guidance:
When optimizing the database server, you need to optimize the individual query performance. This is as important as optimizing other aspects of Server installation that affect the performance of hardware and software configurations-or even more important.
Even if the database server runs on the most powerful hardware, its performance may still be negatively affected by incorrect queries. In fact, as long as there is a bad query (sometimes called "out-of-control query"), it may cause serious performance problems in the database.
On the contrary, optimizing the most frequently executed query can greatly improve the overall performance of the database. In this article, I will discuss some technologies that you can use to identify and optimize queries with the highest cost and worst performance on servers.
Analyze execution plan
When optimizing a personal query, you usually start by viewing the query execution plan. This execution plan describes the sequence of physical and logical operations performed by SQL servertm to query and generate the desired result set. This execution plan is generated by the database engine component called the query optimizer in the optimization phase of query processing. This takes into account many different factors, such, the search predicates used in the query, the involved tables and their connection conditions, the list of returned columns, and whether there is a useful index that can be used as a valid data access path.
For complex queries, the number of all possible arrays may be very large. Therefore, the query optimizer does not evaluate all possibilities, but tries to find a "good enough" plan for a given query. This is because it is not always possible to find a perfect plan; even if possible, the overhead of evaluating all possibilities to find a perfect plan is likely to be outweighs the performance improvement. From the DBA's point of view, it is important to understand the process and limitations.
There are many ways to retrieve query execution plans:
Management Studio provides functions such as displaying the actual execution plan and displaying the estimated execution plan, which graphically presents the plan. These features provide the most suitable solution for direct checks, and are currently the most common method for displaying and analyzing execution plans (in this article, I will use the chart plan generated in this way to describe my example ).
Various set options, such as showplan_xml and showplan_all, will return the execution plan in the form of an XML document or a row set. The XML document uses a special architecture to describe the plan, the line set contains a text description of each operation in the execution plan.
The SQL Server Profiler event class, such as showplan XML, allows you to collect and track the execution plans of collected statements.
Although execution plans in XML form may not be the easiest format to understand, this option allows you to write steps and utilities that can analyze execution plans, to find signs of performance problems and unsatisfactory plans. XML-based representation can also be saved to a file with the. sqlplan extension, and opened in management studio to generate a graphical representation. You can also save these files for later analysis. Of course, you do not need to regenerate the execution plan every time you want to analyze them. This is especially useful when you want to compare a plan to view its changes over time.
Estimated execution overhead
For execution plans, you must first understand how they are generated. SQL Server uses the overhead-based query optimizer, that is, it tries to use the lowest overhead to generate an execution plan. This estimate is obtained from the data distribution statistics provided to the optimizer when the optimizer evaluates each table involved in the query. If the statistics are lost or expire, the query optimizer will lack the important information required to query the optimization process, and thus may get an incorrect estimate. In this case, the Optimizer may choose an unsatisfactory plan because it overestimated or underestimated the execution overhead of different plans.
There are some common false assumptions about the estimated execution overhead. In particular, it is often assumed that the estimated execution overhead can well indicate how long the query takes to execute, and this estimate allows you to separate good and bad plans. This is not the case. First, it is clearly recorded to indicate the units used to estimate the overhead and whether they are directly related to the execution time. Secondly, because this is only an estimate and may not be correct, sometimes, in terms of CPU, I/O, and execution time, although the estimation value with higher overhead is higher, the efficiency is higher. This usually occurs in queries involving table variables-because no statistical data is available for table variables, even if the table variables contain many rows, the query optimizer always assumes that the table variables contain only one row. Therefore, the query optimizer selects a plan based on inaccurate estimates. Therefore, when comparing the execution plans of each query, you should not only rely on the estimated query overhead, but include the statistics I/O and Statistics time options in the analysis to output the results, to understand the actual execution overhead of I/O and CPU time.
It is worth mentioning that there is a special type of execution plan called parallel plan. If you run a query on a server with multiple CPUs and your query meets the parallel execution conditions, you can choose to use a parallel plan (normally, the query optimizer only uses parallel plans for queries whose overhead exceeds a specific configurable threshold ). Because the management of multiple parallel execution threads (cross-thread distribution of tasks, execution of synchronization, and collection of results) will produce overhead, the overhead of execution of parallel plans will be greater, which will be reflected in the estimated overhead. So why are parallel plans more popular for non-parallel plans that are cheaper? This is because the processing power of multiple CPUs is exerted, and parallel plans often produce results faster than standard plans. Depending on your specific scheme, including available resources from other queries and variables such as parallel loads, this situation may be exactly what you expected for your settings. In this case, you should control which queries can generate parallel plans and how many CPUs are available for each query. You can achieve this by setting the maximum degree of parallelism option at the server level and overwrite it with option (maxdop N) at a single query level as needed.
Analyze execution plan
Now, I want to show you a simple query, the execution plan of the query, and some ways to improve its performance. Suppose that I use management studio to perform a query and enable the "include actual execution plan" option in the adventure works Sample Database on SQL Server 2005:
Select C. customerid, sum (linetotal)
From sales. salesorderdetail OD
Join sales. salesorderheader Oh
On OD. salesorderid = Oh. salesorderid
Join sales. Customer C on Oh. customerid = C. customerid
Group by C. customerid
As a result, I see the execution plan described in Figure 1. This example calculates the total number of orders issued by each adventure works customer. Let's take a look at this execution plan. You will see how the database engine processes queries and generates results. The graphic execution plan should be read from top to bottom and from left to right. Each icon represents a logical and physical operation, and an arrow indicates the data flow between operations. The arrow thickness indicates the number of rows passed between operations. The thicker the arrow, the more lines it contains. If you place the pointer on an operator icon, the detailed information of this operation is displayed in the yellow tooltip (2.
Figure 1
Execution Plan example (click the image to get a smaller view)
Figure 2
Operation Details (click the image to get a smaller view)
By viewing each operator, you can analyze the sequence of the steps:
The database engine performs a clustered index scan on the sales. Customer table, and then returns the customerid column of all rows in the table.
Then it performs an index scan (non-clustered index scan) on an index in the sales. salesorderheader table ). This is an index of the customerid column, but it also implies the salesorderid column (Table clustering key ). The scan returns the values in both columns.
The output from both scans uses the merge join physical operator to join the customerid column (one of the three available physical methods for performing the logical join operation. This method is fast, but you need to sort the two inputs in the joined columns. In this example, both scan operations have been returned to the rows sorted by customerid, so you do not need to perform other sorting operations ).
Next, the Database Engine. the clustered index on the salesorderdetail table performs a scan to retrieve the values of the four columns (salesorderid, orderqty, unitprice, and unitpricediscount) from all rows in the table (this operation is estimated to return rows 123 and 317, in fact, you can see from the "Estimated number of rows" and "actual number of rows" attributes in Figure 2. Therefore, this estimation is very accurate ).
The row generated by clustered index scan is passed to the first "Calculate scalar" operator. In this way, based on the orderqty, unitprice, and unitpricediscount columns involved in the formula, you can calculate the value of the linetotal column in each row.
Based on the needs of the calculation column formula, the second "Calculate scalar" operator applies the isnull function to the result of the previous calculation. This completes the calculation of the linetotal column and returns the calculation result together with the salesorderid column to the next operator.
Use the "hash matching" physical operator to join the output content of the "merge join" Operator in step 3 and the output content of the "Calculate scalar" Operator in Step 6.
Then, apply another "hash match" operator to the row group returned by the "merge join" operation to the sum of the value of the customerid column and the calculated linetotal column.
The last select node is neither a physical operator nor a logical operator, but a placeholder to represent the total query results and overhead.
On my laptop, the estimated overhead of this execution plan is 3, (3 ). If you execute this plan when statistics I/O is on, the query report performs a total of 1,388 logical read operations on the three involved tables. The percentage displayed under each operator indicates the overhead of each individual operator associated with the overall estimated overhead of the entire execution plan. Looking at the plan in 1, we can see that most of the total overhead of the entire execution plan is associated with the following three operators: clustered index scanning of the sales. salesorderdetail table and two hash matching operators. However, before optimization, I would like to point out a very simple change in my query, which will allow me to remove both operators at the same time.
Figure 3
Total estimated execution overhead of the query
Because I am from sales. only the customerid column is returned in the customer table, and this column is sales. the foreign key in the salesorderheader table. Therefore, I can use the following code to completely remove the generated customer table from the query without changing the logical meaning or result of the query:
Select Oh. customerid, sum (linetotal)
From sales. salesorderdetail OD join sales. salesorderheader Oh
On OD. salesorderid = Oh. salesorderid
Group by OH. customerid
In this way, another execution plan is generated, as shown in figure 4.
Figure 4
Remove the execution plan of the customer table from the query (click the image to get a small view)
The following two operations are completely eliminated: The clustered index scan of the customer table and the merge join between the customer and salesorderheader, And the hash matching join will be replaced by a more efficient merge join. However, to use "merge join" between the salesorderheader and salesorderdetail tables, all rows in the two tables sorted by the salesorderid column must be returned. To achieve this purpose, the query optimizer decides to perform clustered index scanning on the salesorderheader table, instead of non-clustered index scanning, which reduces the I/O overhead involved. This example demonstrates the actual working principle of the query optimizer: the overhead saved by modifying the Physical Mode of the join operation is much higher than the additional I/O overhead generated by clustered index scan, therefore, the query optimizer selects a combination of operators because it produces the lowest total estimated execution overhead. On my laptop, although the number of logical reads has increased (up to 1,941), the CPU time consumed is actually less, the estimated execution overhead of this query is also reduced by about 13% ).
Suppose I want to further improve the query performance. Now let's take a look at the clustered index scan of the salesorderheader table. This operator has become the most popular operator in the execution plan. Because I only need to fill the query with two columns in this table, I can create a non-clustered index that only contains these two columns. In this way, instead of scanning the entire table, you can scan a much smaller non-clustered index. The index definition may be as follows:
Create index idx_orderdetail_orderid_totalline
On sales. salesorderdetail (salesorderid) include (linetotal)
Note that the index I created contains a computed column. This is not always the case. The specific situation depends on the definition of the computed column.
After creating this index and executing the same query, I get a new execution plan, as shown in Figure 5.
Figure 5
Optimized Execution Plan (click the image to get a smaller view)
The clustered index scan for the salesorderdetail table has been replaced by a non-clustered index scan, and the I/O overhead of the latter is much lower. I also removed a "scalar calculation" operator because my index contains the value of the calculated linetotal column. It is estimated that the execution plan overhead is 1,125 and logical reads will be performed during the execution of the query.
Overwrite Index
The index I created on salesorderdetail is an example of "Covering indexes. It is a non-clustered index that contains all columns required to fill the query. You do not need to use the table scan operator or clustered index scan operator to scan the entire table. This index is actually a small copy of the table, containing a subset of all columns in the table. Only the columns that need to respond to the query are included in the index. In other words, the index only contains the columns that need to "Overwrite" the query.
Creating overwriting indexes for the most frequently executed queries is the simplest and most common technology used in query optimization. This type of index is especially applicable when a table contains many columns but only a few columns are frequently referenced in queries. By creating one or more overwriting indexes, You can greatly improve the performance of the affected queries because these indexes require much smaller data volumes, resulting in less I/O overhead. However, during data modification operations (insert, update, and delete), there is also an implicit overhead required to maintain additional indexes. Based on your environment and the ratio between SELECT query and data modification, you should carefully judge whether the maintenance overhead of this additional index is worth Improving the query performance.
You don't have to worry about creating multiple-column indexes (compared with single-column indexes ). Multi-column indexes are often much more useful than single-column indexes, and the query optimizer is more likely to use them to overwrite the query. Most of the covered indexes are multi-column indexes.
In my example query, there is still room for improvement. You can optimize this query by placing the covered index in the salesorderheader table. This will eliminate clustered index scanning, rather than clustered index scanning. I will leave this to you for exercises. Please try to define an index-What columns should be covered by this query, and whether the column sequence in the index definition has different impact on performance. For more information, see the "customer order query exercise" sidebar.
Index View
If the performance of my sample query is very important, I can perform one more step to create an index view that can actually store the query results. The index view has certain prerequisites and restrictions. However, if you can use the index view, the performance can be significantly improved. Remember that, compared with standard indexes, index views require high maintenance costs. Therefore, you should carefully consider when to use the index view. In this example, the index definition is as follows:
Create view vtotalcustomerorders
With schemabinding
As
Select Oh. customerid, sum (linetotal) as orderstotalamt, count_big (*) as totalorderlines
From sales. salesorderdetail OD
Join sales. salesorderheader Oh
On OD. salesorderid = Oh. salesorderid
Group by OH. customerid
Note the with schemabinding option. This option is a prerequisite for creating an index on this type of view. Pay attention to the count_big (*) function, if our index definition contains an aggregate function (sum in this example), this function is required. After creating this view, you can create an index on it, as shown below:
Create unique clustered index cix_vtotalcustomerorders_customerid
On vtotalcustomerorders (customerid)
When this index is created, the query results contained in the view definition have been embodied and are actually stored in the disk as an index. Note that all data modification operations performed on the basic table will then automatically update the values in the view according to the definition.
If I return a query, the result depends on the SQL Server version I run. In Enterprise Edition or Developer Edition, the query optimizer automatically matches the query with the index view definition and uses the index view instead of the base table involved in the query. Figure 6 shows the execution plan generated in this example. This plan consists of only one operation: scanning clustered indexes for the indexes I created on The View. It is estimated that the execution overhead is only and only 92 logical reads are performed.
Figure 6
Execution Plan when using the index view (click the image to get a smaller view)
In other versions of SQL Server, you can still create and use this index view. To achieve the same effect, you must use the noexpand prompt to change the query to reference this view directly, as shown below:
Select customerid, orderstotalamt
From vtotalcustomerorders with (noexpand)
You can see that, if used properly, the index view can become a very powerful function. They are most useful in optimizing queries that aggregate a large amount of data. If used in Enterprise Edition, these views are useful for many queries and do not require code changes.
Identify the query to be optimized
How can I identify queries worth optimization? I want to find the most frequently executed queries. the overhead of executing these queries separately may not be high, but the overhead of executing these queries may be much higher than that of rarely executed large queries. I am not saying that you should not optimize large queries, but think that you should first pay attention to the most frequently executed queries. So how do you identify these queries?
Unfortunately, the most reliable method is a bit complicated. It involves tracking all the queries executed on your server and grouping them based on their signatures (that is, the query text with actual parameter values will be replaced by placeholders to identify the same query type, even if the query uses different parameter values ). This is a complex process, because the query signature is difficult to generate. Itzik Ben-gan introduces a solution in its book Microsoft SQL Server 2005: T-SQL querying, which uses CLR user-defined functions and regular expressions.
There is also a relatively simple method, but the reliability is poor. You can use the dynamic management view to query the statistics retained for all queries in the execution plan cache. Figure 7 contains an example query that shows you the text and execution plan of the 20 queries with the highest cumulative logical reads in the cache. For queries that quickly identify the maximum number of logical reads, this query is very convenient, but there are also some restrictions. That is, this query only displays the queries whose plans are cached when the query is run. If no cached content exists, the content will be lost.
After identifying these poor queries, you can view their query plans and use some indexing techniques I have introduced in this article to find ways to improve their performance. If it can be completed successfully, your time is not in vain.
Happy optimization!
Maciej PileckiIs a quasi-consultant for solid quality mentors, a global organization dedicated to training, guidance, and consulting. He is a Microsoft certified trainer (MCT) and the most valuable SQL Server expert (MVP ), I often teach many courses on SQL Server and application development and give speeches at meetings discussing such content.
This article is transferred from
Http://idoall.org/blogs/ian/archive/2007/11/14/sql-server.aspx
