Initial discussion on SQL Server logical reading, physical reading, and pre-reading

Objective

The content of this article is not original, is to record themselves in the learning Io, the implementation of the plan in the process of learning other Daniel's blog and experience and record, the reason is to write down in order to record their own in the process of tracing several problems, and to clarify these issues. The original address has been posted at the end of this chapter.

1. How SQL Server data is stored

To understand the three concepts of logical reading, physical reading, and pre-reading, first understand how SQL Server data is stored.

The SQL Server database includes data files and log files, and a database can have one or more data files and log files. All data is stored in a data file, and the data file can be divided into smaller units, which we call "pages". Size 8k per page. 8 pages form a zone. SQL Server reads the page most atomically, either after reading a page or not reading at all. The data organization structure between pages is a B-tree. So SQL Server is a page for logical read, physical read, and pre-read units.

2. Analyze IO Statistics

2.1 First three readings

Let's look at an example first. Sample database AdventureWorks. Query Sales.SalesOrderDetail

As you can see, how many times it reads the "How many pages" of data. This is also a place I didn't understand at first.

Pre-read: In the process of generating the query plan, using the estimated information to go to the hard disk to read the data into the cache, pre-read 1242 pages, that is, read from the hard disk 1242 pages into the cache.

Physical reads: After the query plan is generated, if the cache is missing the required data, then it reads the missing data from the hard disk into the cache.

Logical read: Reads data from the cache. Read the logic 1240 times, i.e. read 1240 pages of data from the cache.

2.2 The relationship between logical reading, physical reading and pre-reading

Run the above statement again to produce the following results

It can be seen that this time there is no room for reading and pre-reading, only the number of logical reads, according to the previous concept we can analyze: The second query can be directly from the cache to read the required data . According to our theory, it seems that the number of logical reads = physical reads + read-ahead times. But you will find that our 1240 is not equal to 1242+3.

1, the first to explain that the number of logical reads is not absolutely equal to the number of physical reads and the sum of the number of read-ahead. The number of physical reads and read-ahead times for the second query is 0. Read-ahead reads information according to estimated information, so the number of pages read is not necessarily accurate, probably more or less than the actual number of pages.

2. If the number of pre-read pages includes all data, there will be no physical reads.

3. Sometimes the number of logical reads is greater than the number of physical reads plus the number of read-ahead times, because some of the required data already exists in the cache prior to pre-reading.

3, see the figure Understanding "logical reading", "Physical reading", "pre-reading"

When SQL Server executes a query, SQL Server starts the first step, generating the execution plan. Also use the estimated data to go to the hard disk to read data (pre-read). These two first steps are parallel, and SQL Server improves query performance in this way. The execution plan is generated so that the cache reads the data later. Once the cache is found to be missing the required data, let the cache read the hard disk again (physical read). All data (logical reads) are then read from the cache.

The estimated number of pages can also be queried from this DMV. If you use DBCC DROPCLEANBUFFERS to clean up the cache after the second cache (production is used with caution).

SELECT Page_count from Sys.dm_db_index_physical_stats (db_id (' adventureworks2008r2 '), object_id (' Sales.SalesOrderDetail '), Null,null, ' sampled ')

Summarize

Understanding the three concepts of logical reading, physical reading, and pre-reading are mainly to understand the query process of the statement and that step is to cache the data, the step is to go to the hard disk to read the data, that step is based on the estimated information to read the data. From the point of view of application, the three quantity is not the absolute quantity plus and the relation, the key is to look at the theoretical statement of the query process. The number of logical reads that are focused or read when the statement is optimized, and the number of logical reads to be checked by the optimization statement to reduce the IO overhead.

Resources

Http://www.canway.net/Original/shujuku/012CE2016.html

Http://www.cnblogs.com/CareySon/archive/2011/12/23/2299127.html

Http://www.cnblogs.com/kissdodog/archive/2013/06/25/3155016.html

Initial discussion on SQL Server logical reading, physical reading, and pre-reading