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

Source: Internet
Author: User
Tags sql server query
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:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.