CSDN上有篇帖子http://topic.csdn.net/u/20100223/15/644e6212-9fdc-42de-81ad-785d28ed71d3.html 讨论查询计划读取索引页的问题。
主要问题是： 在一张建有聚集索引（没有其它非聚集索引）的表执行select count(*) 。查询显示扫描了所有的索引中间层叶。从理论上讲，由于每个数据页都记录着它的上一个page和下一个page,那么最小的io读取应该是:
读根页--->读最小的中间页--->读数据叶子---->依次往后读全部数据页.
但实际上却是读取全部的索引中间页。
帖子中比较精彩的回复：
预读
关于计算逻辑读的数量
实际上： 是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)。该读取作为缓冲区高速缓存中相应数量（可能是非相邻的）缓冲区的一次散播-聚集读取来执行。如果此范围内的任何页在缓冲区高速缓存中已存 在，当读取完成时，所读取的相应页将被放弃。如果相应页在缓存中已存在，也可以从任何一端“裁剪”页的范围。
有两种类型的预读：一种用于数据页，一种用于索引页。
读取数据页
用 于读取数据页的表扫描在数据库引擎中非常有效。SQL Server 数据库中的索引分配映射 (IAM) 页列出了表或索引使用的区。存储引擎可以读取 IAM 以生成必须读取的磁盘地址的排序列表。这使得存储引擎能够根据要读取的磁盘位置，将其 I/O 操作优化为按顺序执行的大型顺序读取。有关 IAM 页的详细信息，请参阅管理对象使用的空间 。
读取索引页
存储引擎按键的顺序依次读取索引页。例如，下图显示了一组叶级页的简化表示法，该组叶级页包含映射叶级页的键集和中间索引节点。有关索引中页的结构的详细信息，请参阅聚集索引结构 。
存 储引擎使用高于叶级的中间索引页上的信息为包含键的页安排序列预读。如果请求针对的是 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,000th 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,000th 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.
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
