[SQL] understands logical reads, pre-read and physical reads in SQL Server

form of SQL Server data store

Before you talk about several different ways of reading, the first thing to understand is how SQL Server data is stored. The smallest unit of SQL Server storage is pages (page). Each page size is 8k,sql server reads for pages are atomic, either after reading a page or not reading at all, There is no intermediate state. The data structure between pages is a B-tree (see my previous blog post). So SQL Server is a page for logical read, pre-read, and physical read units.

The total size of the SQL Server page is: 8K

But the data stored on this page will be: 8k=8192 bytes-96 bytes (header)-36 bytes (row offset) = 8060 bytes

So the actual size of each page used for storage is 8060 bytes.

For example, the Person.Address table in the above AdventureWorks, the data space of this table can be seen through SSMs:

We can figure out how many pages are occupied by the formula: 2.250*1024*1024/8060 (data capacity per page) ≈293-The Space ≈290 (number of logical reads in) for non-data occupied by the table

the order in which SQL Server query statements are executed

The steps of SQL Server query execution if you look at the microscopic level, that would be a lot. To tell the concepts of logical reading, I look at the higher levels of abstraction:

The figure is somewhat coarse.

Let me explain one. When a query statement is encountered, SQL Server takes the first step to generate the execution plan (CPU and memory resources), synchronously uses the estimated data to go to the disk to obtain the required data (consumes IO resources, this is read-ahead), note that the two first step is parallel, SQL Server improves query performance in this way.

Then the query plan is generated and then the cache reads the data. When the cache is missing the required data, let the cache read the hard disk again (physical read)

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

Let me go through a simple example to illustrate the following:

This estimated page data can be seen through this DMV:

When our first query is completed and the query is made again, all the requested data is now in the cache, and SQL Server simply reads the cache as long as it is read only logically:

[SQL] understands logical reads, pre-read and physical reads in SQL Server