Sql Server is a complicated and efficient data storage and retrieval engine. It has many unique and fascinating techniques to make the overall engine more efficient. One such concept is Advanced Scanning.

In any database, you’ll have many users running queries simultaneously. All queries, more or less, query the same set of tables in the database. We have some popular (core) tables and a bunch of look up or supporting tables. While each user may be querying a unique set of records, many times, there is a lot of overlap on the records queried by multiple users. This is where Advanced Scanning comes into play.

According to MSDN: “In SQL Server Enterprise, the advanced scan feature allows multiple tasks to share full table scans. If the execution plan of a Transact-SQL statement requires a scan of the data pages in a table and the 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 one time and passes the rows from each page to both execution plans. This continues until the end of the table is reached.”

Example from the MSDN article:

For example, assume that you have a table with 500,000 pages.

Advanced Scanning

UserA executes a Transact-SQL statement that requires a scan of the table. When that scan has processed 100,000 pages, UserB executes another Transact-SQL statement that scans the same table. The Database Engine schedules 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 Transact-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 it reads the 500,000th row, the scan for UserA is complete, and the scans for UserB and UserC wrap back and start to read the pages starting with page 1. When the Database Engine gets to page 100,000, the scan for UserB is completed. The scan for UserC then keeps going alone until it reads page 200,000. At this point, all the scans have been completed.

This allows to share the buffer space between queries that are retrieving the same set of records and reduces disk contention. Read once and use it multiple times for multiples queries.

Side Note: By default, Shared Buffer is shared among all the queries. Any data that is already available in Shared Buffer would be used to respond to queries rather than going to disk every time. As you can see above Advanced Scanning is goes beyond that. 

Hope this helps,
_Sqltimes