浅谈SQL Server逻辑读、物理读、预读

大家好，欢迎来到IT知识分享网。

目录

（一）前言

（二）正文

1. SQL Server的数据存储方式

2. 剖析IO统计信息

（1）初识三读

（2）逻辑读、物理读、预读的关系

（3）看图理解【逻辑读】、【物理读】、【预读】

（三）总结

（一）前言

在判定SQL SERVER中SQL执行性能时候，我们经常会接触到这些概念：逻辑读/物理读/预读。大家都叫读，常常傻傻分不清^_^，所以本文将着重介绍下这部分概念，这将有助于大家在理解SQL的IO以及阅读执行计划时候有一个初步的预判基础。

（二）正文

1. SQL Server的数据存储方式

要理解逻辑读、物理读、预读这三个概念，先要搞懂SQL Server的数据存储方式。

SQL Server数据库包括数据文件和日志文件，一个数据库可以有一个或多少数据文件、日志文件。所有的数据存储在数据文件中，数据文件可以划分为再小的单元，我们称为“页”。每页大小8k。8个页面构成一个区。SQL Server对于页的读取是最原子性，要么读完一页，要么完全不读。页之间的数据组织结构为B树。 所以SQL Server对于逻辑读、物理读、预读的单位都是页。

2. 剖析IO统计信息

（1）初识三读

我们先看下如下的例子，查询Customers表。

先打开检查SQL的读与写功能：

set statistics io on;

运行查询：

select CUSTOMER_ID,CONSULTANT_NUMBER,c.BIRTH_DATE from cn.CUSTOMERS c where c.BIRTH_DATE = '1986-11-05 00:00:00.000' and c.CONSULTANT_NUMBER is not null and c.TERMINATION_DATE is null and c.CUSTOMER_STATUS = 0 and c.CONSULTANT_NUMBER <> '1';

从截图中可以看出，这里读取多少次也就是读取了【多少页】数据。这是一个需要注意的地方！

预读：在查询计划生成的过程中，用估计的信息去硬盘读取数据到缓存中，预读546页，也就是从硬盘中读取了546页放到了缓存中。

物理读：查询计划生成好以后，如果缓存缺少所需要的数据，再从硬盘里读取缺少的数据到缓存里。

逻辑读：从缓存中读取数据。逻辑读26829次，也就是从缓存中读取26829页数据。

（2）逻辑读、物理读、预读的关系

 再次运行上面的语句得出以下结果

从图中可以看出，这次没有物理读取和预读次数，只有逻辑读取次数，根据前面的概念我们可以分析出：第二次查询可以直接从缓存中读取所需要的数据。 按照我们的理论，貌似逻辑读取次数=物理读取次数+预读次数。但你会发现前面我们26829并不等于546+0,而且数值相差极大，这又是为什么呢？

     1、首先要说明，逻辑读取次数并不绝对等于物理读取次数和预读次数之和。第二次查询物理读取次数和预读次数都是0。预读是按照估计的信息去读取信息，因此读取的页数并不一定准确，可能多于也可能少于实际的页数。

     2、如果预读的页数包括了全部数据，那么就不会有物理读取次数。

     3、有时候出现逻辑读取次数大于物理读取次数加上预读次数，这是因为在预读之前缓存中已经存在部分需要的数据。

（3）看图理解【逻辑读】、【物理读】、【预读】

 当SQL Server执行一个查询时，SQL Server会开始第一步，生成执行计划。同时用估计的数据去硬盘读取数据（预读）。这两个第一步是并行的，SQL Server通过这种方式来提高查询性能。执行计划生成好以后去缓存读取数据。当发现缓存缺少所需要的数据后让缓存再次去读硬盘（物理读）。然后从缓存中读取所有数据（逻辑读）。

      估计的页数也可以从这个DMV中查询。如果第二次缓存后可使DBCC DROPCLEANBUFFERS清理缓存（生产慎用）。

SELECT page_count 　　FROM sys.dm_db_index_physical_stats 　　(DB_ID('[XXX.XXXXXX.XXXXXXX]'),OBJECT_ID('[cn].[CUSTOMERS]'),NULL,NULL,'sampled');

也可以顺便看下该DMV的完整字段信息，如下：

（三）总结

理解逻辑读、物理读、预读这三个概念主要是理解语句的查询过程以及那个步骤是去缓存数据、那个步骤又是去硬盘读取数据、那个步骤又是根据估计的信息去读取数据。从应用的角度来说这三个数量并不是绝对的数量加和关系，关键还是要看理论语句的查询过程。在对语句进行优化时重点还是逻辑读的次数，通过优化语句来检查逻辑读的次数来减少IO开销。