Talking about SQL Server logical reading, physical reading and pre-reading

Summary

the an important task when performing performance optimizations for SQL Server databases is to optimize the IO overhead. By turning on Statistics io, we can understand the IO overhead of statement execution , including physical reads, logical reads, pre-reading, and so on. And do we know the difference between the three and the connection, I believe many people are not very understanding, let us explore together to find out what these three represent.

Body

One  ,Sql serverHow data is stored

the database for SQL Server includes data files and log files, and a database can have one or more data files or log files.

all data is stored in a data file, and the data file can be divided into smaller units, which we call the page. The page is the smallest unit of action, which means that reading a database from disk is a minimum of one page, and the size of each page is 8KB. Another concept is the district, which is composed of 8 contiguous pages, which is the smallest allocation unit and allocates at least one area of space when space is needed.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/4C/52/wKioL1Q7aeLiUvbpAAC01XY4PEY362.jpg "title=" 1.jpg " alt= "Wkiol1q7aeliuvbpaac01xy4pey362.jpg"/>

Two  , AnatomySQL Server IOStatistical Information2.1      primary knowledge of logical reading, physical reading and pre-reading

Let's give an example first.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/4C/51/wKiom1Q7abrSlJRyAAF_waI4tYM244.jpg "title=" 2.jpg " alt= "Wkiom1q7abrsljryaaf_wai4tym244.jpg"/>

querying the database for the first time AdventureWorks the table sales.salesorderdetail , the "logical read 1240 times, physical reads 5 times, read 1250 times" is displayed.

whether it's a logical read, a physical read, or a pre-read unit is a page, the last time we've introduced it. SQL Server reads the page atomically, either after reading a page, or completely without reading, and each page is 8KB in size .

Pre-read : In the query plan generation process, use the estimated information to go to the hard disk to read the data into the cache. Pre-read 1250 times, which is estimated to be read from the hard disk 1250 page data and put into the cache.

Physical Reading : After the query plan is generated, if the cache is missing the required data, then the missing data is read from the hard disk to the cache.

Logical Read : Removes all data from the cache. Logical read 1240 times, that is, from the cache to fetch 1240 page data.

2.2      Anatomy of a statement execution process

Let's review the process of the above query experience:

1. First, during the build execution plan,SQL Server first estimated 1250 times from the hard disk , that is, read the 1250*8kb=10,000kb Data and into the cache (that is, the process of pre-reading )

2. When executing a query plan, it is not enough to find the cached data. Continue to read the additional demand data from the hard disk and into the cache ( i.e., the process of physical reading )

3. SQL Server then reads all the required data from the cache and returns it to the client. ( i.e. the process of logical reading )

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4C/52/wKioL1Q7ag7xISh4AAConbBVTNo351.jpg "title=" 3.jpg " alt= "Wkiol1q7ag7xish4aaconbbvtno351.jpg"/>

2.3      the relationship between logical reading, physical reading and pre-reading

When we run the above query statement again, we get the following information:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4C/52/wKioL1Q7ah7Q7L6WAAGG7igNQVQ071.jpg "title=" 4.jpg " alt= "Wkiol1q7ah7q7l6waagg7ignqvq071.jpg"/>

We can find that this time there is no physical reading and pre-reading, only logical reading, which is why? We have already analyzed the principle, because the second query can read the required data directly from the cache.

According to our theory, it seems that the number of logical reads = number of physical reads + number of read-ahead. But careful readers will find that the first time the query "logical read 1240 , physical read 5 times, pre-read 1250 times", we found that the number of logical reads is not equal to the number of physical reads and the sum of the number of read-ahead. Why is that?

1. The first thing to note is that the number of logical reads does not necessarily equal the sum of the number of physical reads and the number of read-ahead times. A very simple example is when the second query is both physical and read-ahead are 0. In fact, read-ahead reads the data according to the estimated information, so the number of pages read is not necessarily accurate, possibly more than the actual number of pages or the actual number of pages.

2. If the read-ahead page contains all the data, there is no physical read

3. There are times when the number of logical reads is greater than the number of physical reads and the number of read-ahead times. This is because some of the required data already exists in the cache prior to pre-reading.

Three  , Summary

Understanding the three concepts of logical reading, physical reading and pre-reading, the key is to understand the query process of the statement and which step is to read the data from the cache, which step is to read the data from the hard disk, which step is based on the estimated information to read the data, so you can understand the three concepts.

Logical reading, physical reading and pre-reading these three times there is no absolute number of relations, the key is to the theoretical statement of the query process.

when you optimize a statement, the emphasis is on optimizing the number of logical reads. Reduce the number of logical reads by optimizing statements, thus reducing the IO overhead.

This article is from the "Ka for IT Training academy" blog, please be sure to keep this source http://canway.blog.51cto.com/8964890/1563208

Talking about SQL Server logical reading, physical reading and pre-reading