SQL Server Execution Plan operator detailed (1)--assertion (assert

Objective:

Many places for the optimization of the sentence, generally more reliable reply even-the implementation of the plan to see. Of course, those who only look at the statement to say how to change the code, I have always refused, because this is pure Mongolian. According to my experience, a lot of performance problems simply from the statement to find the bottleneck, the same statement, due to different circumstances, the gap is very large, so more appropriate or analysis of the implementation plan.

Then for the execution plan, the general use of graphical execution plan is almost the same, but the use of people have some doubts, the inside of the icon (called operator) is not very intuitive. So from the beginning of this article, we will tidy up some of the less common but more important operators and interpretation, for those table scan, index Scan, clustered index Scan, index lookup, clustered index lookup These very common operators, temporarily do not intend to introduce.

Only by understanding some important and common operators can you perform accurate and efficient performance analysis and optimization of statements.

This series of articles is expected to contain the following operators:

1. Assert: Assert (the name of the English version of the graphical interface, the Chinese version of the execution plan in XML format, and the name of the execution plan in text format. Same below
2. Concatenation: concatenation
3. Compute scalar: Compute scalar
4. Key Lookup: Key Lookup
5. Spool: Spools
6. Table spooling: Lazy Spool
7. Index Spool: Index Spool
8. Row count spooling: Row Countspool
9. Stream aggregation: Stream Aggregate
10. Sort by: Sort
11. Merge Join: Merge Join
12. Merge interval: Merge Interval
13. Splitting, Folding: split,collapse

The next step is to start with assertions. Original source: http://blog.csdn.net/dba_huangzj/article/details/50261747

Assertion:
The Assert operator is a physical operator. In the execution plan, if the graphical execution plan for the Chinese version is called "assertion", it is displayed as an assert in the English and non-graphical execution plans.

Its icon is:

Assert operator is used to validate the condition. For example, verifying referential integrity or ensuring that a scalar subquery returns a row. For each input row, theAssert operator evaluates the expression in the Argument column of the execution plan. If the value of this expression is NULL, the row is passed through the Assert operator, and query execution continues. If the value of this expression is not NULL, a corresponding error is generated.

Assertion and CHECK constraints:

Let's take a look at this code first, creating a test environment when the server executes, and using tempdb is a good choice:

Use Tempdbgoif object_id (' Tableassert ') are not nulldrop table Tableassertgocreate table Tableassert (ID integer,gender CHA R (1)) Goalter TABLE tableassert ADD CONSTRAINT ck_gender_m_f CHECK (Gender in (' M ', ' F ')) GO

Select the following code, do not execute, choose "Show estimated execution plan",

The code is as follows:

INSERT into Tableassert (ID, Gender    ) VALUES (1, ' X ') GO

There is an operator called ASSERT (assert), so what is in it? Move the mouse over this operator to see:

Note The above explanation: used to verify that the specified condition exists, this explanation is very straightforward, and look at the predicate section, explain the actual validation of the content, to determine whether the gender field of the insertion value is f/m two, if not return null.

The assertion operator is processed against the validation return value, and if the validation returns NULL, an error message is returned, that is, if you directly execute the INSERT statement:

Original source: http://blog.csdn.net/dba_huangzj/article/details/50261747

Assertions and FOREIGN KEY constraints:

Here's an example of a FOREIGN KEY constraint:

Use Tempdbgoalter table Tableassert ADD id_genders INT GO  IF object_id (' tableforeign ') are not NULL   DROP TABLE tabl Eforeign go CREATE TABLE tableforeign (id Integer PRIMARY KEY, Gender CHAR (1))  go  INSERT into tableforeign (ID, Gend  ER) VALUES (1, ' F ') insert into tableforeign (ID, Gender) VALUES (2, ' M ') of the INSERT into Tableforeign (ID, Gender) VALUES (3, ' N ') Go  ALTER TABLE tableassert ADD CONSTRAINT fk_tab2 FOREIGN KEY (id_genders) REFERENCES tableforeign (ID) GO  

Again, we use the estimated execution plan to test the INSERT statement:

The statements are as follows:

INSERT into Tableassert (ID, Id_genders, Gender) VALUES (1, 4, ' X ')

This time we use another tool:SET showplan_text on in this way:

SET showplan_text Ongoinsert into Tableassert (ID, Id_genders, Gender) VALUES (1, 4, ' X ')

will see two results, the first is the statement, do not close, we look at the second result:

|--assert (WHERE: case, not [Pass1009] and [Expr1008] are null then (0) ELSE NULL END))       |--nested Loops (left Semi Jo In, PASSTHRU: ([tempdb]. [dbo]. [Tableassert]. [Id_genders] is NULL), OUTER REFERENCES: ([tempdb]. [dbo]. [Tableassert]. [Id_genders]), DEFINE: ([Expr1008] = [PROBE VALUE])            |--assert (WHERE: (case) [tempdb].[ DBO]. [Tableassert]. [gender]<> ' F ' and [tempdb]. [dbo]. [Tableassert]. [gender]<> ' M ' then (0) ELSE NULL END))            |    | --table Insert (OBJECT: ([tempdb].[ DBO]. [Tableassert]), SET: ([tempdb]. [dbo]. [Tableassert]. [ID] = [@1],[tempdb]. [dbo]. [Tableassert]. [Id_genders] = [@2],[tempdb]. [dbo]. [Tableassert]. [Gender] = [Expr1004]), DEFINE: ([Expr1004]=convert_implicit (char (1), [@3],0)])            |--clustered Index Seek (OBJECT: ([ TEMPDB]. [dbo]. [Tableforeign]. [Pk__tablefor__3214ec27173876ea]), SEEK: ([tempdb]. [dbo]. [Tableforeign]. [Id]=[tempdb]. [dbo]. [Tableassert]. [Id_genders]) ORDERED FORWARD)

This result is more likely not intuitive, the reader can perform the test to see the results.

You can see that there are two assert, bottom-up reading, the first assert (that is, the following, for the graphical interface is the right one, because the graphical execution plan is read from right to left) is the previous check constraint, if return 0 will continue to run the statement, or return an error.

For the second assert is used to detect the results of two table associations where "[Expr1008] is NULL" (note [Expr1008] is not fixed, depending on each machine may return different values, SQL 2008/2012 on my machine is executed separately to get different [Expr ] value, we need to know what [Expr1008] is, and there is define in the content: ([Expr1008] = [PROBE VALUE]), which is the result of the table association. If the value of Id_gender in the INSERT statement already exists with Tableforeign, then the probe (probe) returns the associated value. Otherwise, NULL is returned. So this "assertion" is to check the value in the Tableforeign, and if the value passed in the insert is not found, the assertion returns an exception.

If the value of id_genders is NULL, SQL Server cannot return an exception, but instead returns "0" and continues to run the statement. If you run the INSERT statement above, SQL Server returns an exception because the value is ' X ', which violates the check constraint:

However, if you switch x to F and then run, you will still get an error because the FOREIGN KEY constraint is violated:

However, when you change 4 to null or 1 or 2 or 3, and then run the INSERT statement, no exception is generated:

Original source: http://blog.csdn.net/dba_huangzj/article/details/50261747

Assertions vs. subqueries:

The assertion operator can also be used to check subqueries, and multiple values cannot be returned for a scalar subquery, but sometimes the changes in the notation and data can cause a multivalued error. At this point the assertion plays the role of whether the checksum quantum query returns a value.

Here's a look at these two statements:

INSERT into Tableassert (ID, Gender) VALUES (("SELECT ID from Tableassert", ' F ')    INSERT into Tableassert (ID, Gender) VALU ES ((SELECT ID from Tableassert), ' F ')

Use the method above to view the execution plan:

SET showplan_text Ongoinsert to Tableassert (Id,gender) VALUES ((SELECT ID from Tableassert), ' F ') INSERT into Tableassert (Id,gender) VALUES ((SELECT ID from Tableassert), ' F ')

The observation statement probably knows what happens and the first insert succeeds (unless you have modified the data in it) because the select part of values only returns a value. The second insert, however, has an error because the select in values has two values (the first insert is added).

the results are as follows:

|--assert (WHERE: ([Expr1013])) |--compute Scalar (DEFINE: ([Expr1013]=case when[tempdb].[ DBO]. [Tableassert]. [gender]<> ' F ' and[tempdb]. [dbo]. [Tableassert]. [gender]<> ' M ' then (0) ELSE NULL END)) |--table Insert (OBJECT: ([tempdb].[ DBO]. [Tableassert]), SET: ([tempdb]. [dbo]. [Tableassert]. [ID] =[expr1009],[tempdb]. [dbo]. [Tableassert]. [Gender] =[expr1010],[tempdb]. [dbo]. [Tableassert]. [Id_genders] = NULL) |--top (Top EXPRESSION: (1)) |--computescalar (DEFINE: ([Expr1009 ]=[EXPR1012], [expr1010]= ' F ')] |--nested Loops (leftouter Join) | |                                     -constantscan |--assert (WHERE: (Case is [expr1011]> (1) then (0) ELSE NULL END)) |--streamaggregate (DEFINE: ([Expr1011]=count (*), [Expr1012]=any ([tempdb].[ DBO]. [Tableassert]. [ID])) |--table Scan (OBJECT: ([tempdb].[ DBO]. [Tableassert]))

Note the assert of the inner layer:

You can see that SQL Server creates a streamaggregate (stream aggregation, which can be explained from the estimated execution plan, and is specifically described later) to calculate how much data the subquery will return and then pass that value to the assertion for detection.

As a product that has been commercialized for over more than 20 years, its core (query optimizer) has been accumulating and improving over the years, and the high-version SQL Server (such as R2 and above, which has no absolute standard), will judge the current situation of the statement and table structure to determine whether "assert, assert" is required. Operator. Like what:

INSERT into Tableassert (ID, Gender) VALUES ((SELECT ID from tableassert WHERE ID = 1), ' F ') insert into Tableassert (ID, Gen Der) VALUES ((SELECT TOP 1 ID from Tableassert), ' F ')

Original source: http://blog.csdn.net/dba_huangzj/article/details/50261747

Do not execute first, open the estimated execution plan and then look at the graphical interface, you can see the following results:

Because the optimizer detects that the second statement contains top 1, only one row of data is returned, there is no need to introduce assertions to detect them.

Summarize:
So far, the introduction of this operator is complete, and the next article introduces the concatenation operator. For this assertion operator, we need to know that it is used to "validate" certain conditions, but the introduction of each operator will inevitably bring some overhead, but the introduction of these operators is necessary because they need to do some tasks. If you need to improve, you might want to look at what it is used to test, such as the subquery mentioned above, which can be reduced by using top 1, adding unique constraints, and so on. But all the improvements should be fully tested and demonstrated.

Original source: http://blog.csdn.net/dba_huangzj/article/details/50261747

SQL Server Execution Plan operator detailed (1)--assertion (assert