What Is “Merry-go-round scan” In SQL Server Enterprise Edition??

Recommend reading : Read- Ahead

                                  Everyone knows that reading is always good for career and sharing while reading is good for everyone. The same concept is used by SQL Server in its costliest edition, yes I mean Enterprise edition.

        Before going with explanation, you should be aware that this feature is considered as advanced and only available in developer’s and enterprise edition. So while doing testing on lower environments we should always consider this feature.

                This feature allows multiple execution plans to share full table scans. When a plan request for new scan for any Hobt, enterprise edition engine checks for already active scans on same Hobt and if it found any scan in progress then both scan start sharing data i.e. SQL 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. This helps to reduce the number of reads to the same IO subsystem as much as possible

                 This feature is also called as Enhanced Read-ahead and Scan option

Example:

Table Data	1- 100 Rows	101 -200 Rows	201 - 300 Rows	301 - 400 Rows
SQL 1	SQL 1 start 0 sec and finish reading 0 - 400 rows in 40 ms
SQL 2	Start @ 10th ms	101 - 400 rows			1- 100 Rows
SQL 3	Start @ 20th ms		201 - 400 rows		1- 100 Rows	101 - 201 Rows
Time	0 - 10 ms	10- 20 ms	20- 30 ms	30-40 ms	40- 50 ms	50 - 60 ms

           Here's an explained example of how this feature works. Assume that SQL 1, SQL 2 and SQL 3 issue the SELECT * FROM employee having 400 rows, which results in a table / index scan. SQL1 issues the command first at 0th ms; SQL2 issues the command at 10th ms. when scan was @ row no. 101 here both sql will join the scan and start reading from row no. 101. Once they reach @ row no. 201 another Scan joins the created by SQL3 and all 3 starts reading from row 201.  Once SQL1 reached to row 400, it will finish it task and SQL 2 and SQL 3 will jump to 0th row and start reading together till 100th row and SQL2 will exit and then SQL3 will finish its reading from 101 to 200 rows.   

         And if this was standard edition than all 3 process were writing their own copy in buffer Cache or read ahead might have reduce some pressure from sql engine. And you will be struggling to find performance difference between two environments.

.

Reference :  Reading Pages in MSDN Library