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

Www.91xueit.com instructor Han's IT system training course optimizes SQL Performance. You must understand the details of database query data, enable IO statistics in the query analyzer, and setstatisticsioon to view the query process logic

Www.91xueit.com instructor Han's IT system training course optimizes SQL Performance. You must understand the details of database query data, enable IO statistics in the query analyzer, and setstatisticsioon to view the query process logic

Instructor Han's IT system Training Course

 

To optimize SQL Performance, you must understand the details of database query data, enable IO statistics in the query analyzer, and set statistics io on to view the logic read in the query process, pre-read and physical read display. The following describes the details of database data query. You can also understand what logical read, pre-read, and physical read are. To clarify these concepts, you must understand the details of database data query.

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: