初谈SQL Server逻辑读、物理读、预读【转】

前言

       本文涉及的内容均不是原创，是记录自己在学习IO、执行计划的过程中学习其他大牛的博客和心得并记录下来，之所以想写下来是为了记录自己在追溯的过程遇到的几个问题，并把这些问题弄清楚。 本章最后已贴出原文地址。

 

1、SQL Server的数据存储方式

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

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

 

2、剖析IO统计信息

2.1 初识三读

     先来看个例子。示例数据库AdventureWorks。查询Sales.SalesOrderDetail

     从截图中可以看出，这里读取多少次也就是读取了【多少页】数据。这个也是我一开始没搞懂的地方。

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

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

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

 

2.2 逻辑读、物理读、预读的关系

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

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

     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('AdventureWorks2008R2'),OBJECT_ID('Sales.SalesOrderDetail'),NULL,NULL,'sampled')

 

总结

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

http://www.2cto.com/database/201605/513734.html