SQL Server logical read, pre-read, and physical read

form of SQL Server data store

1. 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.
2. 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.
3. 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.

First look at a query:

DBCC dropcleanbuffers    --Empty cache on    --Open IO statistics --query statement   

The message is displayed as follows:

(1475170 times.) (1 rows affected)   

　　

The size of the table above is 17.406M.

The data stored on each page is: 8k=8192 bytes-96 bytes (page header)-36 bytes (row offset) = 8060 bytes.

17.406*1024*1024/8060≈2 264

There are also some non-data-occupying spaces in the table, so the result of the above formula is approximately equal to the logical read count.

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

understand the various readings from the execution order

SQL Server queries are understood in terms of understanding the various reading steps, as follows:

(Figure is Careyson Brother's)

To explain the various readings:

When SQL Server executes a query statement, SQL Serer starts the first step, generates a query plan, and uses the estimated data to go to the disk to read the data (pre-read), and the first two steps are parallel. 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 page_count from sys.dm_db_index_physical_stats (db_id ('testdatacenter'),  OBJECT_ID ('person'),null,null,'sampled')     

The results appear as follows:

　　

SQL Server is pre-read based on this thing.

If we execute the above query statement at this point:

From person    -- query statement 

See the following message:

(1475170 times.) (1 rows affected)   

Why is this all a logical reading? Because just read once, the data are all already in the cache, only need to read from the cache, do not need to read the hard disk.

This article learns from: http://www.cnblogs.com/CareySon/archive/2011/12/23/2299127.html

SQL Server logical read, pre-read, and physical read