The merry-go-round scan or advanced scan is something that I’ve seen mentioned a few times recently and it’s a lovely little feature of Enterprise Edition that not all that many people seem to know about.
The thing to remember here that this is only available in Enterprise Edition, so unfortunately those of you on Standard won’t see the benefits of this.
Scans are Expensive
As we all know, full table scans can be very expensive, poor old SQL is forced to read every single row in a table (of course that doesn’t always mean that it’s a bad choice for SQL).
Lets assume we’ve got a table scan happening that results in 1,000,000 page reads, that’s quite a bit of work for SQL to do. Now imagine another query comes in and needs to scan the same table, that’s also going to need to do 1,000,000 reads to get the data that it needs. If this table happens to be frequently accessed, this is soon going add up.
Is There a More Efficient Way to Deal with this Situation?
So is there a way that SQL could make this process more efficient? Perhaps if there are two queries both scanning the same table at the same time, could they somehow join forces and share the workload?
Well that’s pretty much what a merry-go-round scan is going to do. If a query is running and has scanned half of a table when a second query starts and needs to scan the same table, SQL can detect that there’s already a scan happening on the table that the new query needs to scan. The new query can ‘share’ the scan started by the first query.
When this happens, as the scan reads a page it’s passed to both the first and second query. This means that a page only needs to be read once, despite it serving two or more queries. When the scan finishes, the first query has all the data that it needs but the second will still be missing any pages that were read before it started. To deal with that situation, the scan will now loop back to the top of the table and pull in all the pages that the second query missed.
The great thing here is that any number of queries can get in on a merry go round scan and it can loop back over the table as many times as needed until all queries have the data that they want.
So hopefully you can see how a merry-go-around scan can really help reduce reads on a busy server or frequently accessed table.
SQL Undercover 