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

                                  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

4 comments:

  1. That was too deep into SQL Engine,learned something new today..! (jagthedag)

    ReplyDelete
  2. Many times when you start learning something - you end up finding something else and learn it !

    I haven't had ever tried to understand meaning of read-ahead and write-ahead - today with your simple explanation - i understood it. Thank you Saurabh.

    And indeed - Merry-go-round scan - can save lot of IO and improve performance in prod.

    ReplyDelete