Sybase数据库技术，数据库恢复专家

CSDN上有篇帖子http://topic.csdn.net/u/20100223/15/644e6212-9fdc-42de-81ad-785d28ed71d3.html 讨论查询计划读取索引页的问题。

主要问题是： 在一张建有聚集索引（没有其它非聚集索引）的表执行select count(*) 。查询显示扫描了所有的索引中间层叶。从理论上讲，由于每个数据页都记录着它的上一个page和下一个page,那么最小的io读取应该是:
读根页--->读最小的中间页--->读数据叶子---->依次往后读全部数据页.

但实际上却是读取全部的索引中间页。

帖子中比较精彩的回复：

1
2
3
4
5

首先楼主的说法不够准确，根据我的测试结果来看，更准确的说法应该是SQL server在做索引全扫(INDEX FULL scan)时，会先扫描全部的level 1的节点，然后在扫描处于level 0的叶节点。也就是说一次索引全扫的顺序是（这个顺序对于clustered INDEX和普通INDEX都是一样的）：
读根页--->读最小的中间页（level 1以上的层）--->依次读level 1的所有节点的索引页---->读全部数据页
上述顺序可以在将数据表索引层数增加到3层以上时得到验证。
实际上我觉得SQL server这样做是很有道理的，楼主认为的是所读的页越少，io就越小，这是不对的。对于SQL server来说，一次io并不一定只读取一个页，很多时候特别是做表全扫或索引全扫时，SQL server都会尽量的让一次io读取尽可能多的数据页，这样才能较少io的次数。为达到这个目的，SQL server首先就要知道它到底需要读取那些页，知道要读取页的分布之后，就可以安排io调度器尽可能的将临近的数据页用一次io读取上来，而要知道需要读取数据页的分布就需要先读取到level 1上的所有页，以找出要读取io页的分布情况，然后再做最优的io读取安排。
同样的道理，如果SQL server是先定位到叶节点的起始数据页，然后从依次的读取所有的数据页的话，那SQL server就只能一次io读取一个数据页，然后找出对应的下一页的指针，再去读取下一个数据页，这样实际上花费的io会远比批量读取数据页多很多的。这就好比我们在做表的lookup时，如果需要lookup的行非常之多的话SQL server会转而选择表扫描来达到目的的。

预读

我觉得SQL Server内部存在这样一个机制:
它会尽量将当前使用的及使用最频繁的表的数据都带到buffer中来，这跟buffer的算法有一定的关系。
清掉buffer後，你不做任何动作，隔几秒钟，sql会自动将master库中一些比较重要的表都带到buffer中，或者
你select * from tb where 1 <>1 ，尽管该查询不会扫描任何数据，但sql还是会自动将tb的所有索引页到带到
buffer中去，因为它可能觉得tb这个表後续可能会使用到.

关于计算逻辑读的数量

IAM页是否会计算在逻辑读中我都是持怀疑态度的。
比如说，一个堆表扫描时肯定是从IAM页开始的，然而用set statistics io on看到的逻辑读数量却只是表所占用的总页数，并没有加上相应的IAM页数量。
而索引扫描却更有意思，就像楼主说的那样，会比我们计算的数据页加索引页的页数还要多1个，但这1个却不知是从何而来的。按照堆表扫描的计算方法，我认为更不可能是IAM页，再说索引扫描也不会用到IAM页。
一直没有看到有描述逻辑读怎么计算的资料。
在没有详细资料的情况下要研究的这么精确，不纠结才怪

实际上： 是sql server 内部引擎设计上的问题。sql server 利用预读机制来改善IO。通过读取所有的索引中间层叶sql server能够知道预读哪些页面，而不是利用数据页面上的前后链接指针那样一页一页的读。 sql server 最大一次IO可以读取64页。

以下是从msdn上摘下来的帮助文档。

SQL Server 2008 联机丛书（2009 年 7 月）

SQL Server 数据库引擎实例的 I/O 包括逻辑读取和物理读取。每次数据库引擎从缓冲区高速缓存 请求页时都会发生逻辑读取。如果页当前不在缓冲区高速缓存中，物理读取将首先将页从磁盘复制到缓存中。

数据库引擎实例生成的读取请求由关系引擎控制，并由存储引擎优化。关系引擎决定最有效的访问方法（例如，表扫描、索引扫描或键读取）；存储引擎的访问方法和缓冲区管理器组件确定要执行的读取的常规模式，并对实现访问方法所需的读取进行优化。执行批处理的线程将安排读取。

预读

数据库引擎支持称为“预读”的性能优化机制。预读首先预测执行查询执行计划所需的数据和索引页，然后在查询实际使用这些页之前将它们读入缓冲区高速缓存。这样可以让计算和 I/O 重叠进行，从而充分利用 CPU 和磁盘。

预 读机制允许数据库引擎从一个文件中读取最多 64 个连续页 (512KB)。该读取作为缓冲区高速缓存中相应数量（可能是非相邻的）缓冲区的一次散播-聚集读取来执行。如果此范围内的任何页在缓冲区高速缓存中已存 在，当读取完成时，所读取的相应页将被放弃。如果相应页在缓存中已存在，也可以从任何一端“裁剪”页的范围。

有两种类型的预读：一种用于数据页，一种用于索引页。

读取数据页

读取索引页

存储引擎按键的顺序依次读取索引页。例如，下图显示了一组叶级页的简化表示法，该组叶级页包含映射叶级页的键集和中间索引节点。有关索引中页的结构的详细信息，请参阅聚集索引结构 。

存 储引擎使用高于叶级的中间索引页上的信息为包含键的页安排序列预读。如果请求针对的是 ABC 到 DEF 之间的所有键，则存储引擎将首先读取高于叶级页的索引页，但它并不是仅仅按顺序读取页 504 到页 556（即指定范围内的包含键的最后一页）之间的每个数据页。相反，存储引擎将扫描中间索引页并生成必须要读取的叶级页的列表。然后，存储引擎会按键的顺 序安排所有读取。存储引擎还会识别出页 504/505 以及页 527/528 是相邻页，并执行一次散播读取，从而在单个操作中检索这些相邻页。如果在一个序列操作中要检索许多页，则存储引擎将一次安排一个读取块。完成这些读取子集 后，存储引擎将安排同等数量的新读取，直到安排完所需的全部读取。

存储引擎使用预提取加快非聚集索引的基表查找。 非聚集索引的叶级行包含指针，指向含有每个特定键值的数据行。存储引擎浏览非聚集索引的叶级页时，它也会开始计划异步读取已检索了其指针的数据行。这可以 使存储引擎在完成非聚集索引的扫描之前从基础表中检索数据行。无论表是否有聚集索引，都会使用预提取。SQL Server Enterprise 比 SQL Server 其他版本使用更多的预提取，可以预读更多页。在任何版本中都无法配置预提取的级别。有关非聚集索引的详细信息，请参阅非聚集索引结构 。

高级扫描

在 SQL Server Enterprise 中，高级扫描功能使得多项任务可以共享完全表扫描。如果 Transact-SQL 语句的执行计划需要扫描表中的数据页，并且数据库引擎检测到其他执行计划正在扫描该表，则数据库引擎会在第二个扫描的当前位置将第二个扫描加入第一个扫 描。数据库引擎会一次读取一页，并将每一页的行传递给这两个执行计划。此操作将一直持续到该表的结尾处。

此时，第一个执行 计划已有完整的扫描结果，而第二个执行计划仍必须检索在它加入正在进行的扫描之前读取的数据页。然后，第二个执行计划中的扫描将绕回到表的第一个数据页， 并从这里向前扫描到它加入第一个扫描时所处的位置。可以按这种方式组合任意数量的扫描。数据库引擎将循环遍历数据页，直到完成所有扫描。这种机制也称为 “走马灯式扫描”，说明了为何在没有 ORDER BY 子句的情况下无法保证 SELECT 语句所返回结果的顺序。

例 如，假设某个表有 500,000 页。UserA 执行了一条 Transact-SQL 语句，要求对该表进行扫描。当扫描已处理了 100,000 页时，UserB 执行了另一条 Transact-SQL 语句，要对同一个表进行扫描。数据库引擎将为页 100,001 之后的页安排一组读取请求，并将每页中的行同时传递回两个扫描。当扫描到页 200,000 时，UserC 执行了另一条 Transact-SQL 语句，要对同一个表进行扫描。则从页 200,001 开始，数据库引擎将把它读取的每一页中的行传递回所有三个扫描。当数据库引擎读取完第 500,000 行之后，UserA 的扫描就完成了，而 UserB 和 UserC 的扫描将绕回到页 1 开始读取。当数据库引擎到达页 100,000 时，UserB 的扫描就完成了。然后 UserC 的扫描将继续进行，直到它读取完页 200,000。此时，所有扫描便均已完成。

在没有高级扫描的情况下，每个用户都必须要争用缓冲区空间并因此导致磁盘臂争用。然后,会分别为每个用户读取一次相同的页，而不是一次读取并由多个用户共享，这样会降低性能并加重资源负担。

The read requests generated by an instance of Microsoft® SQL Server™ 2000 are controlled by the relational engine and further optimized by the storage engine. The access method used to read pages from a table, such as a table scan, an index scan, or a keyed read, determines the general pattern of reads that will be performed. The relational engine determines the most effective access method. This request is then given to the storage engine, which optimizes the reads required to implement the access method. The thread executing the batch schedules the reads.

Table scans are extremely efficient in SQL Server 2000. The IAM pages in a SQL Server 2000 database list the extents used by a table or index. The storage engine can read the IAM to build a sorted list of the disk addresses that must be read. This allows SQL Server 2000 to optimize its I/Os as large sequential reads that are done in sequence based on their location on the disk. SQL Server 2000 issues multiple serial read-ahead reads at once for each file involved in the scan. This takes advantage of striped disk sets. SQL Server 2000 Enterprise Edition dynamically adjusts the maximum number of read ahead pages based on the amount of memory present; it is fixed in all other editions of SQL Server 2000.

One part of the SQL Server 2000 Enterprise Edition advanced scan feature allows multiple tasks to share full table scans. If the execution plan of a SQL statement calls for a scan of the data pages in a table, and the relational database engine detects that the table is already being scanned for another execution plan, the database engine joins the second scan to the first, at the current location of the second scan. The database engine reads each page once and passes the rows from each page to both execution plans. This continues until the end of the table is reached. At that point, the first execution plan has the complete results of a scan, but the second execution plan must still retrieve the data pages that occur before the point at which it joined the in-progress scan. The scan for second execution plan then wraps back to the first data page of the table and scans forward to the point at which it joined the first scan. Any number of scans can be combined in this way, the database engine will keep looping through the data pages until it has completed all the scans.

For example, assume that you have a table with 500,000 pages. UserA executes a SQL statement that requires a scan of the table. When that scan has processed 100,000 pages, UserB executes another SQL statement that scans the same table. The database engine will schedule one set of read requests for pages after 100,001, and passes the rows from each page back to both scans. When the scan reaches the 200,000^th page, UserC executes another SQL statement that scans the same table. Starting with page 200,001, the database engine passes the rows from each page it reads back to all three scans. After reading the 500,000^th row, the scan for UserA is complete, and the scans for UserB and UserC wrap back and start reading pages starting with page 1. When the database engine gets to page 100,000, the scan for UserB is complete. The scan for Userc then keeps going alone until it reads page 200,000, at which point all the scans have been completed.

Reading Index Pages

SQL Server 2000 reads index pages serially in key order. For example, this illustration shows a simplified representation of a set of leaf pages containing a set of keys and the intermediate index node mapping the leaf pages.

SQL Server 2000 uses the information in the intermediate index page above the leaf level to schedule serial read-ahead I/Os for the pages containing the keys. If a request is made for all the keys from 'ABC' to 'DEF', the instance of SQL Server 2000 first reads the index page above the leaf page. It does not, however, simply read each individual data page in sequence from page 504 to page 556, the last one with keys in the desired range. Instead, the storage engine scans the intermediate index page and builds a list of the leaf pages that must be read. The storage engine then schedules all the I/Os in key order. The storage engine also recognizes that pages 504/505 and 527/528 are contiguous, and performs a single scatter-gather read to retrieve the adjacent pages in one operation. When there are many pages to be retrieved in a serial operation, SQL Server schedules a block of reads at a time. When a subset of these reads is completed, SQL Server schedules an equal number of new reads until all the needed reads have been scheduled.

SQL Server 2000 uses pre-fetching to speed the processing of non-clustered indexes. The leaf rows of a non-clustered index contain pointers to the data rows containing each specific key value. As the database engine reads through the leaf pages of the non-clustered index, it also starts scheduling asynchronous reads for the data rows whose pointers have already been retrieved. This allows the database engine to start retrieving rows before it has completed the scan of the non-clustered index. This process is followed regardless of whether or not the table has a clustered index. SQL Server 2000 Enterprise Edition uses more pre-fetching than other editions of SQL Server, and the level of pre-fetching is not configurable in any edition.