Polish your eyes and look at SQL Server's simple insert

I was planning to write SQL Server history first, but I feel that it is difficult to write that part of the content. I still need to check more information. So I adjusted the next order and wrote simple insert statements. The database structure still adopts the structure of the previous article. For details, refer to the previous article.ArticlePolishing your eyes to see SQL Server simple select.The statements discussed today are also relatively simple. insert statements.

I. insert script

Insert into test ([name]) values ('xiaojun ')

There is nothing to say, because it is too easy to write such a statement.

II, Statement Analysis

What happened to this statement?Assume that the reader knows the overall architecture of sqlserver or has read the first article in this series. When this statement is reliably transmitted to the relational engine, an execution plan has been generated and scheduled for execution.Next, the following occurs:

 

 

Write transaction logs: The only operation in a data modification transaction that always needs to be written to the disk. It is not about modifying the list of query statements, but about the specific changes on the data page after the modification operation. Is completed by the log manager. When we see data written to a disk, we should immediately think of performance problems because this operation is always written to the disk. If a statement has a large amount of data, this time is terrible. For example, if you want to know the gap, You can execute the following two statements in a million or ten million tables:Truncate table test and delete from test. Of course, the rigorous students say that truncate is for zone operations, and delete is for page operations. The truncate locks consume less than the delete locks. These are the reasons why truncate is faster than Delete. But these are not the main reasons. The main reason is that the transaction log is written here. Delete is to delete a row each time and record one row in the transaction log, however, truncate deletes data by releasing the data pages used to store table data, and only records the release of pages in transaction logs. Since transaction logs affect performance, why do we keep records? It mainly solves the problem of data protection and data consistency.

receive write requests : once the access method receives the confirmation that the transaction log is successfully written, it receives the write request and sends the write request to the cache manager. Note that the request is sent to the cache manager. The cache manager only operates on the cache and has nothing to do with physical files. The purpose of this article is not to understand the principle. You may have done a lot of insert operations for yourself, and the size of the data file is not changed. On the surface, the access method plays a role in request transmission. In fact, it is intelligent and has some complicated algorithms to predict the execution.

Insert Buffer Pool: The buffer manager inserts data in the memory. After successful insertion, the confirmation result is sent to the access method, and the final confirmation result is sent to the client.

Write Data Files : This step can be completed by either of the two components. The inertia writer thread regularly checks the size of the SQL Server idle buffer list. When this value is too low, the inertia writer scans the entire data cache and ages all unused pages for a period of time. If you find a dirty page that has not been used for a period of time, the inertia writer writes it to the disk and deletes it, And then marks the memory space of this page as idle space. The inert writer also monitors the idle physical memory on the server. If the memory is small, it releases the idle buffer list of sqlserver to Windows. When sqlserver is under heavy load, it also adds the idle buffer list of sqlserver to adapt to the load when the server has idle physical memory and the memory allocated to sqlserver has not reached the configured Max server memory. A checkpoint is a time point created by the checkpoint thread. It ensures that all dirty pages are written to the disk, in addition, mark the page in the cache as a clean page in the page header. Note that the checkpoint does not delete dirty pages. The checkpoint execution time can be divided into several situations: If you have configured recovery interval (min), this prevails. If no configuration is configured and the transaction log data written after the last checkpoint is more than 10 MB, It is started every minute. For example, a checkpoint is triggered when we manually execute the checkpoint or execute the backup and restart command. Aside from human operations, the specific time cannot be determined. sqlserver has an internal heuristic algorithm to control this value. However, we can enable a tracking flag 3502 for viewing. This tracking mark records the start and end of the checkpoint in the error log. SQL statement: DBCC traceon (3502) .

3. End

TodayThis article mainly introduces the execution process of the insert statement, with little content. From this process, you will find that sqlserver is really intelligent. For example, the pre-write logs here are used to protect data, delay data writing to the disk, predict SQL Execution, and monitor the Load Adjustment memory. The design is so clever, you can think about if we can refer to and learn from when designing our own software?

This is the end of today's analysis. If your description is inappropriate, please note that. Common progress is the final principle.