Insert Analysis of SQL Server insert execution secrets (lower) with foreign keys

PreviousArticleDescribes the execution plan details of the simplest insert statement. This article analyzes the insert example of an out-of-band key table.
 
The data table structure used in this article is shown in. blogid in the blog table is an auto-incrementing primary key, and two non-unique indexes are created on the createuserid and createtime columns respectively.

We need to insert a piece of data into the blog table and analyze its execution.
The insert statement is as follows:

 
Insert into [db_cn]. [DBO]. [blog] ([title], [tags], [content], [createuserid], [createtime], [IP]) values ('this is a test blog title ', 'test', 'This is the content of the test blog. The address of the blogger is http://www.cnblogs.com/yukaizhao/', 100, '2017-01-06 ', '2017. 0.0.1 ');
 

The execution plan is slightly more complex, as shown below:
 

From the right to left analysis, constant scan in the First Step generates a data row based on the SQL statement entered by the user. The first constant scan generates a new auto-increment ID; the second calculation scalar is the constant value in the SQL statement entered by the user. For more information, see the preceding section.
Step 4 is a two-step split operation. The above operation is clustered index insertion. The next operation is to query the clustered index of the User table, such as the details of clustered index insertion:
 
The estimated overhead of logical index insertion is 90%. In this step, the primary key of the blog table is inserted. The two indexes ix_blog and ix_blog_createtime of the blog table are inserted, the operations on these two indexes indicate that creating an index in a table will negatively affect the efficiency of the insert operation on the table. Because the createuserid field of the blog table is a foreign key, therefore, the createuserid field is output in the output list. This field must be used to determine whether the foreign key exists.

Let's look at the details of the clustered index search operation on the user table:
 
In this step, the searched object is the primary key of the pk_user and user tables, and the primary key scanning is very fast. Even so when the user table is very large, the scanning overhead is also very considerable. The scanning overhead can be divided into two parts, one is the overhead of the CPU, and the other is that the SQL server automatically adds a shared lock to the primary key during scanning, since the lock may cause a deadlock or exclusive lock wait.

From this step, if we have much higher response speed requirements than data consistency requirements, we can consider removing foreign keys and unnecessary overhead.

Step 5: perform a nested loop on the output produced by the two forks in Step 4. This nested loop is used to prepare for the next assert.
Part 6: assert determines whether the createuserid generated by the nested loop is null. If it is null, an exception that does not exist for the foreign key is thrown.
The last step is to execute the insert operation.

From the above analysis, we can draw some experiences
1. Why is the number of the auto-increment field occupied when data insertion fails when the auto-increment field is used?
Because the value of the auto-increment field is generated when the scalar value is calculated in the second part, this step has added the auto-increment ID to 1.

2. Why the insert performance will be affected when more indexes are created for the table?
Because every insert operation requires every index to be inserted.

3. Why do foreign keys not be created or deleted during the design of large concurrency?
Because foreign keys bring additional CPU overhead and lock resource overhead

Related essays: SQL Server insert execution secrets (on) a simplest insert Analysis