SQL Server logical read-pre-read-physical read

SQL Server logical read-pre-read-physical read

How SQL Server stores data

1. The page is the smallest operating unit, that is, read the database from disk at least one page, each page size is 8kb,sql server read to the page is atomic, either read a page, or completely unread, there is no intermediate state

2. The district is composed of 8 contiguous pages, which is the smallest allocation unit and allocates a space at least one area when space is required.

Look at the picture, the structure of the two tables is exactly the same, one inserts four data, the other inserts 100 data, the result size is 0.008;

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. (Test scale data)

1.336*1024*1024/8060=173 (approx)

Based on this formula, the number of read-ahead (using the estimated information to go to the hard disk to read the data to the cache) is obtained.

Look at the statement execution process again

When a query statement is encountered, SQL Server takes the first step of generating the execution plan (CPU and memory resources), respectively.

Synchronized with the estimated data to go to the disk to obtain the required data (take up the IO resource, this is read-ahead), note that the two first step is parallel, SQL server in this way to improve query performance.

Then the query plan is generated and then the cache reads the data. When the cache is missing the required data, let the cache read the hard disk again (physical read)

Finally, all data is fetched from the cache (logical read).

To test for accuracy, you can add:

DBCC dropcleanbuffers (do not use in the production environment ...)

Plot test:

Summarize:

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.

SQL Server logical read-pre-read-physical read