Logical and physical reads in SQL Server

The first thing to understand is logical reading and physical reading:

• Pre-read: Use the estimated information to go to the hard disk to read the data to the cache. Pre-read 100 times, which is estimated to read 100 pages of data from the hard disk to the cache.
• Physical reads: After the query plan is generated, if the cache is missing the required data, let the cache read the hard disk again. Physically read 10 pages and read 10 pages of data from the hard disk to the cache.
• Logical read: All data is fetched from the cache. Read Logic 100 times, which means fetching 100 pages of data from the cache.

The smallest unit of SQL Server storage is the page, with each page size of 8k,sql server reading to the page is atomic, either after reading a page or not reading at all. Even if you just want to get a piece of data, you have to finish reading a page. The data organization structure between pages is a B-tree structure. So the unit of SQL Server for logical read, read-ahead, and physical reads is the page.

Example:

The total size of the SQL Server page is: 8K

But the data stored on this page will be: 8k=8192 bytes-96 bytes (header)-36 bytes (row offset) = 8060 bytes

So the actual size of each page used for storage is 8060 bytes.

We can approximate how many pages are occupied by the formula: 2032*1024/8060 (data capacity per page) ≈258-the space ≈290 (number of logical reads in) for non-data in the table

Basically, a logical read, a physical read, and a read-ahead are all equal to how many pages are scanned.

The order in which SQL Server query statements are executed :

When SQL Server executes a query statement, SQL Server begins the first step of generating a query plan, which requires the query processor to read the definitions of each table and the statistics for each index on the table, when the query plan is generated and is actually handed to the query executor for execution, SQL Server will use another thread to query the buffer that "potentially needed data" is read from the disk (provided the data is not in the cache), which is read-ahead. SQL Server improves query performance in this way.

　　After the query plan is generated, the cache reads the data, and when the cache is found to be missing the required data, let the cache read the hard disk again (physical read) and then fetch all the data (logical read) from the cache.

The estimated number of pages can be seen through the DMV: SELECT * from Sys.dm_db_index_physical_stats ..., page_count display as 254;

At this point, the previous query statement is executed:

Storage engine notices pattern and kicks off read ahead, which starts to read data not yet requested into buffer pool. These is counted as read ahead reads, but not as logical or physical reads. Next time the scan or seek needs data, it is already in the buffer pool, so only logical Io, no physical io.

From the foreign forum, it is not difficult to understand, that is, when the second query, if the data is already cached, then only the logical read, no physical read, because the data can be found directly in the cache.
Reference : http://www.cnblogs.com/CareySon/archive/2011/12/23/2299127.html

Logical and physical reads in SQL Server