Today I read a very interesting feature of SQL Enterprise. I must say it very intelligently named after the Merry go round (a continuous cycle of activities or events, especially when regarded as pointless.)
So this is basically an interesting feature of SQL enterprise that efficiently combined the multiple scan requests and manages the optimum use of the resources.
When there is a request from user to get the details that involve the scanning of the data pages and while the request and scan is in progress 2nd user initiates the request for the same data.
Point to note here:
1. If we keep 2nd user in a queue the user experiences a delay in process.
2. If we accept point 1 this implies: we are going to scan the same data pages again causing more Disk contention and damage: due to frequent movement of disk hand causing wear and tear.
3. Consuming CPU resources unnecessarily.
4. It will simply be inefficient.
5. It will also cause fight for buffer space.
To avoid this there is a feature of advanced scanning; this feature solves all our problems and makes the system more efficient.
Explanation:
Refer Figure A: suppose we have a table that has odd 10,000 data pages. User 1 initiates a query and SQL server starts scanning now it has reached 2,500 page & User 2 initiate a query and this query requires the same data pages to be scanned so SQL server will join the scan for these 2 users. From 2,501 pages the scan results are shared between users. Not scanning reached 4,000 pages and User 3 requested the same data pages so again SQL server will scan data pages and share the results between User 1, 2 & 3. Not when the End of the scan is reached i.e. 10,000th (END) page is scanned, User1 will get the results and is out of the motion. User 2 & 3 scanning will bounce back to page 1 (start) and continue. When scanning reaches the 2,500th Page the User 2 is out or motion. But User 3 will continue till 4,000th page.
This scanning continues till all the scan requests are fulfilled. This is called ‘’Merry Go round ‘’.
 |
| Figure A |
Without advanced scanning, each user would have to compete for buffer space and cause disk arm contention. The same pages would then be read once for each user, instead of read one time and shared by multiple users, slowing down performance and taxing resources.
Reference: MSDN
My LinkedIn;
