Understanding logical reads, prefetching, and physical reads in SQL Server _mssql

Forms of SQL Server data storage

Before talking about several different ways to read, you first need to understand how SQL Server data is stored. The smallest unit of SQL Server storage is a page. Each page size is 8k,sql server is atomic to read a page, or read a page, or simply not read it, There is no middle state. The data between pages is organized into B-tree (please refer to my previous blog post). So SQL Server for logical read, prefetching, and physical reading units are pages .

The total size of SQL Server page is: 8K

But the data stored on this page would 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 AdventureWorks above, through the SSMs see the table's data space is:

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

The order in which SQL Server query statements are executed

Steps for SQL Server query execution if you look at it from a microscopic perspective, it will be very much. In order to describe the concepts of logical reading, I look at the higher levels of abstraction:

The figure is a bit rough.

Let me explain the diagram below. When a query statement is encountered, the SQL Server takes the first step, generating execution plans (consuming CPU and memory resources), synchronizing with estimated data to obtain the required data on the disk (for IO resources, which is prefetching), note that the two first steps are parallel, SQL Server in this way to improve query performance.

Then the query plan is generated to cache the read data. Let the cache read the hard drive again after discovering that the cache is missing the required data (physical read)

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

Let me go through a simple example to illustrate:

This estimate of the page data can be seen through this DMV:

When our first query completes, the query again, all the requested data is already in the cache, SQL Server at this time as long as the cache read the line, that is, only to read logically: