Read - Ahead is a performance optimization mechanism which
read and bring the pages request by SQL engine to fulfill a into the
buffer cache before they are actually used by the query. This allows
computation and I/O to overlap, taking full advantage of both the CPU and
the disk.
- Logical Read: SQL Engine requests a page from the buffer cache.
- Physical read: SQL engine request a page from disk to buffer cache
Read-Ahead
mechanism allows the Database Engine to read up to 64 contiguous pages
(512KB) from one file and if any of the pages in the range are already
present in the buffer cache, the corresponding page from the read will be
discarded when the read completes. The range of pages may also be
"trimmed" from either end.
There are 3
kinds of read-ahead:
- Data Pages: Storage engine will sort list of data pages from IAM pages in a sorted list of the disk addresses and read them.
- Cluster Index Pages: Storage engine uses the non-leaf index page in b tree to schedule serial read-ahead for the pages that contain the keys. It reads index pages serially in key order.
- Non-Cluster Index Pages: The storage engine uses pref-etching to speed base table lookups from non-clustered indexes. The leaf rows of a non-clustered index contain pointers to the data rows that contain each specific key value. As the storage 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 storage engine to retrieve data rows from the underlying table before it has completed the scan of the non-clustered index. Pref-etching is used regardless of whether the table has a clustered index.
SQL server enterprise version supports advance version of Read- Ahead called as "Merry-go-round scan"
Welcome !!
ReplyDelete