Merry-Go-Round Scans in SQL Server | SansSQL

Friday, July 26, 2013

Merry-Go-Round Scans in SQL Server

There might be many reasons for choosing an enterprise edition, but the basic one will be because of the availability of the advanced features in enterprise edition which is not available in standard edition.
Because of these advanced features, you can even see some difference of performance on enterprise edition when compared to standard edition.
Merry-Go-Round Scans also known as Advanced scanning in SQL server is a feature available in SQL Server Enterprise Edition.

This 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.
At that point, the first execution plan has the complete results of a scan, but the second execution plan must still retrieve the data pages that were read before it joined the in-progress scan. The scan for the second execution plan then wraps back to the first data page of the table and scans forward to where it joined the first scan. Any number of scans can be combined like this. The Database Engine will keep looping through the data pages until it has completed all the scans. This mechanism is also called "merry-go-round scanning" and demonstrates why the order of the results returned from a SELECT statement cannot be guaranteed without an ORDER BY clause.

For example, assume that you have a table with 500,000 pages. 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.

Referencehttp://msdn.microsoft.com/en-us/library/ms191475.aspx

2 comments:

magnifictraining said...

Valuable information and excellent design you got here! I would like to thank you for sharing your thoughts and time into the stuff you post!!
Hadoop online training

Srinu Srinu said...

nice article

SAP REFX Online Training

Post a Comment