Understand logical reads, pre-reads, and physical reads in SQLSERVER

In my previous blog post on SQL Server indexes, Yuan You asked about the concepts of logical read, pre-read, and physical read. I think I still write a blog post to explain this question clearly.

In my previous blog post on SQL server indexes, Yuan You asked about the concepts of logical read, pre-read, and physical read. I think I still write a blog post to explain this question clearly.

SQL SERVER Data Storage Format

Before talking about several different reading methods, you must first understand the SQL SERVER data storage methods. the minimum unit of SQL SERVER storage is Page ). the size of each page is 8 KB. SQL SERVER performs atomic reading on the page. It either reads one page or does not read the page at all. There is no intermediate state. The data structure between pages is B tree (refer to my previous blog post). Therefore, SQL SERVER uses the unit of logical read, pre-read, and physical read as pages.

The total size of a single page of SQL SERVER is 8 K.

However, the data stored on this page is: 8 K = 8192 bytes-96 bytes (page header)-36 bytes (row offset) = 8060 bytes.

Therefore, the actual size of each page for storage is 8060 bytes.

For example, in the Person. Address Table in AdventureWorks above, the data space of this table is shown in SSMS:

We can use the formula to estimate the number of pages occupied: 2.250*1024*1024/8060 (data capacity per page) ≈ 293-space occupied by non-data in the table ≈ 290 (number of logical reads in)

SQL SERVER query statement execution sequence

The SQL SERVER query execution steps are very many from the microscopic perspective. Here, I want to introduce the concepts of logical reading and so on, from a high-level abstract level:

The figure is rough.

I will explain it as follows. When a query statement is run, SQL SERVER takes the first step to generate an execution plan (occupying CPU and memory resources ), synchronize the estimated data to the disk to obtain the required data (occupying IO resources, which is pre-read). Note that the first two steps are parallel, SQL SERVER improves query performance in this way.

Then, after the query plan is generated, the cache will read the data. When the cache finds that the required data is missing, the cache will read the hard disk again (physical read)

Finally, all data (logical read) is retrieved from the cache ).

The following is a simple example:

The estimated page number data can be seen through the DMV:

When the first query is complete and the query is performed again, all requested data is already in the cache. At this time, SQL SERVER only needs to read the cache, that is, only logical reads are used: