Shulou(Shulou.com)05/31 Report--

This article shows you the difference between SQL Server pre-reading and physical reading. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Pre-reading is done at the same time as generation and execution, which can improve the performance of IO. On the other hand, the physical read is not read to the disk until the cache reads the data and finds that the missing data is found after the execution plan is generated. It becomes a logical read when all data can be read from the cache.

Here's an example:

About 600W of data is stored in the LINEITEM table, and we use SET STATISTICS IO ON:

SET STATISTICS IO ON

SELECT COUNT (*) FROM LINEITEM

Table 'LINEITEM'. Scan count 3, logical reads 22328, physicalreads 3, read-ahead reads20331, lob logical reads 0, lob physicalreads 0, lob read-ahead reads 0.

Repeat this statement to see that the pre-read has changed to 0 because all the data is already in the cache:

SELECT COUNT (*) FROM LINEITEM

Table 'LINEITEM'. Scan count 3, logical reads 22328, physicalreads 0, read-ahead reads 0,lob logical reads 0,lob physicalreads 0,lob read-ahead reads 0.

Explanation for pre-reading:

The database engine supports a performance optimization mechanism called "read-ahead". Read-ahead first predicts the data and index pages needed to execute the query execution plan, and then reads them into the buffer cache before the query actually uses them. This allows the calculation to overlap with Ihammer O, making full use of CPU and disk.

The read-ahead mechanism allows the database engine to read up to 64 consecutive pages (512KB) from a file. The read is performed as a scatter-aggregate read of a corresponding number of (possibly non-adjacent) buffers in the buffer cache. If any page in this range is already in the buffer cache, the corresponding page read will be discarded when the read is complete. If the corresponding page already exists in the cache, you can also "crop" the range of the page from either end.

What is the difference between SQL Server pre-reading and physical reading? have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.