It is widely known that SQL Server Enterprise Edition contains a range of improvements, which under certain conditions allow you to perform operations in a more optimal way in comparison with Standard Edition. One of these improvements is Advanced Scanning, which allows multiple scans to share one physical read from the disk. You can also come across another name of this feature – Merry-go-Round Scan.
For example, we have a large Table1 consisting of 1 million pages. User A starts executing T-SQL, which requires the table scan. When the 200,000 pages have been scanned User B starts another query, which also requires scanning of Table1. To scan all pages after 200 000th the Database Engine will schedule only one physical read and return the resulting strings to both execution plans. After 500,000 pages have been scanned User C connects and also requests all data from Table1. Now physical scan is divided between 3 queries. Once the scan is over for User A, it will continue for User B and User C, but from the 1st to 200th page for User B and, respectively, to 500 000th for User C.
What’s the point of this optimization? In case you have very large tables that do not fit entirely into the memory and scans result in “washing out” of the buffer pool, and these scans are often performed in parallel, then this optimization will help to reduce load on the buffer pool and do scan multiple times.
To demonstrate this, let’s create a sufficiently large table with a clustered index in database AdventureWorks2012.
use
[AdventureWorks2012];
go
select
[DatabaseLogID],
[PostTime],
[DatabaseUser],
[Event],
[Schema],
[Object],
[TSQL],
[XmlEvent]
into [dbo].[BigDatabaseLog]
from [dbo].[DatabaseLog];
go
insert into [dbo].[BigDatabaseLog]
(
[PostTime],
[DatabaseUser],
[Event],
[Schema],
[Object],
[TSQL],
[XmlEvent]
)
select
[PostTime],
[DatabaseUser],
[Event],
[Schema],
[Object],
[TSQL],
[XmlEvent]
from [dbo].[DatabaseLog];
go 200
create clustered index [IX_CL_DatabaseLogID]
on [dbo].[BigDatabaseLog]
(
[DatabaseLogID] asc
) on [PRIMARY];
go
It’s also worth noting that Advanced Scanning works only if there is no requirement to order data. For example, as in the following query execution plan:
This optimization demonstrates that the clustered index scan with no requirement to order data can lead to the output of not sorted data. And indeed, if I run the specified query in a few sessions with a pause between starts, it doesn’t guarantee that resulting data will be sorted.
In addition, I should note that I performed this experiment for only scanning clustered index, which does not fit entirely into the buffer pool. I set 400 MB as the maximum size of memory in SQL Server. And in two situations described above this went wrong:
- When scanning a heap table (without a clustered index) data were always returned in the same order.
- When the whole table is cached in the buffer pool, the results also come back in the same order. But this does not conflict with anything, because there are not heavy read from the disk operations, but only logical read from the buffer pool.