SQL Server Query optimizer running mode summary tutorial

Preface

This article describes how the query optimizer works in SQL Server, that is, how to evaluate the formation of a good execution plan, as an advanced article in this series.

Start the topic of this article.

Technical preparation

The database version is SQL Server2008R2 and is analyzed using Microsoft's simpler case Library (Northwind.

Body content

When we threw a good T-SQL statement to SQL Server to prepare for execution, the preferred process is the compilation process, of course, if this statement was previously executed in SQL Server, then it checks whether there are cached compiled execution plans for reuse.

However, the compilation process requires a series of optimization processes. The optimization process is roughly divided into two phases:

1. First, SQL Server first executes some simplification on the T-SQL statement we write, usually by the query itself to find the interactive and reschedule the operation order.

In this process, SQL Server focuses on statement writing adjustment, but the most important goal is to generate an effective query because of the cost or index availability analysis.

Then, SQL Server Loads metadata, including index statistics, and enters the second stage.

2. At this stage, SQL Server is a complex optimization process. At this stage, SQL Server evaluates and tries based on the execution plan operators formed in the previous stage, and even reassembles the execution plan, therefore, this optimization process is a time-consuming process.

Use the following flowchart to understand the process:

 

This figure looks a little complicated. We will analyze it in detail, but we will divide this optimization stage into three sub-stages.

<1> In this phase, only the serial plan is considered, that is, the single processor runs. If a good serial plan is found at this stage, the optimizer will not enter the next stage. Therefore, when the data volume is small or the execution statement is simple, the serial plan is basically used.

Of course, if the overhead of this stage is relatively large, it will enter 2nd stages and then be optimized.

<2> at this stage, the serial plan of stage 1 is optimized. If the environment supports parallel operations, the series plan is compared, then, if the cost after optimization is relatively low, the execution plan will be output. If the cost is still relatively high, it will enter the 2nd stage and continue the optimization.

<3> In fact, reaching this stage is the final stage of optimization. In this stage, we will perform the final optimization on the comparison results of serial and parallel methods in the 2nd stages, if the serial execution is good, it will be further optimized. Of course, if the parallel execution is good, the parallel optimization will continue.

In fact, the 3rd stage is the helpless action of the query optimizer. When the 3rd stage is reached, it is a remediation stage and only the final optimization can be done, if the optimization is not good, it can only be executed according to the execution plan.

In the above process, what are the optimization principles of each stage:

The most important principle about these optimizers is to minimize the scanning range. Whether it is a table or an index, the index is better than the table, and the smaller the index volume, the better, the ideal situation is that there are only one or several items.

Therefore, SQL Server also respects the above principles and has been optimized around this principle.

I. Screening condition analysis

The so-called filtering condition is actually the condition behind the WHERE statement in the T-SQL statement we write, we will try to narrow the data scanning range through the statements here, SQL Server through these statements to optimize.

The general format is as follows:

Column operator

Or

Operator column

In the above format, operator includes: =,>, <, =>, <=, BETWEEN, and LIKE

For example: name = 'liudehua', price> 4000, 4000

The preceding statements are the most commonly used methods in the statements we write, and this method will also be used by SQL Server to reduce scanning, and these columns will be indexed and overwritten, we will try to use indexes to obtain values, but SQL Server is not omnipotent. Some statements cannot be identified, which is also avoided when writing statements:

A. where name like '% Liu' cannot be recognized by the SQL Server Optimizer, so it can only be executed through full table or index scanning.

B. name = 'liudehua' OR price> 1000. This is also invalid because it cannot use two filtering conditions to gradually reduce scanning.

C. price + 4> 100 is not recognized.

D. name not in ('liudehu', 'zhourunfa '). Of course, there are also similar: NOT, NOT LIKE

Give a column:

SELECT CustomerID FROM Orders
WHERE CustomerID = 'vinet'

SELECT CustomerID FROM Orders
Where upper (CustomerID) = 'vinet'

 

 

Therefore, when writing a statement in the preceding method, try to avoid it or use a flexible method.

II. Index optimization

After the above screening range is determined, SQL Server then starts the selection of indexes. The first thing to determine is whether the filtering field has an index, that is, whether it is overwritten by the index.

Of course, if the query item is the best to cover the index, if it is not covered by the index, the bookmark section is introduced to make full use of the index feature.

Therefore, when we create an index, the attribute value is the column of the filter condition.

Selection of optimization using indexes:

 

Create index EmployeesName ON Employees (FirstName, LastName)
INCLUDE (HIREDATE) WITH (ONLINE = ON)
GO

SELECT FirstName, LastName, HireDate, EmployeeID
FROM Employees
WHERE FirstName = 'Anne'

 

 

 

Of course, not all, as long as the query column has an index coverage, the index search will be executed, which depends on the number of scanned content. Therefore, the utilization of the index depends on the amount of Retrieved content.

For example:

 

Create index NameIndex ON person. contact (FirstName, LastName)
GO

SELECT * FROM Person. Contact
WHERE FirstName LIKE 'K %'

SELECT * FROM Person. Contact
WHERE FirstName LIKE 'Y %'
GO

 

 

For the exact same query statement, see the execution plan:

 

The query plans generated by identical query statements are completely different. One is index scanning and the other is efficient index search.

Here I will only tell you that there are 1255 rows of FirstName like 'K % ', while FirstName like 'Y %' only has 37 rows, of which

In fact, the reason for this is that the statistical information is at fault.

Therefore, a specific T-SQL statement does not necessarily generate a specific query plan, the same specific query plan is not necessarily the best way, it has many factors: there are many factors affecting indexes, hardware, table content, and statistics.

We will introduce the statistical information in the future. If you are interested, please pay attention to it in advance.

If you have any questions, you can leave a message or send a private message. We are always waiting for you to join SQL SERVER for further research. Learn together and make progress together.