Поделиться через

What’s in Enterprise only? (Concurrent, Shared, or Merry-go-round Scans)

  • Статья

When a new scan is started on any given B-Tree, SQL Server checks whether the new scan can consume items in any order (non-deterministic) and whether another scan is already active for the same object. If that’s the case, the scans are linked together and the new scan piggy backs at the current position of the prior scan. After the prior scan completes, the new scan must restart the scan to obtain items it previously didn’t read. This works independently of the number of scans and is known as concurrent scans, shared scans or even merry-go-round scans. Merry-go-round because each data consumer takes one tour of a virtually permanently active scan. The aim of this technique is to reduce the number of random seeks to the IO subsystem as much as possible, in scenarios where multiple concurrent scans of huge tables are frequent. In order for SQL Server to trigger this mechanism for a given scan, there are two requirements: 1) the number of leaf pages of the object being scanned must be bigger than the buffer pool’s commited target pages, and 2) the instance of SQL running the query must be an Enterprise Edition (or a Developer Edition or an Evaluation Edition, both of which behave exactly like the Enterprise does. The only difference among them three is their licensing.)

This post is part of the a series I decided to call “Understanding the value of the Enterprise Edition, one feature at a time” which I started in September 2011 and will grow on a weekly basis, having a new post incorporated to the family every Friday.

Comments

  • Anonymous
    March 08, 2013
    Hello, Regarding the first requirement for a merry-go-round scan: the number of leaf pages of the object being scanned must be bigger than the buffer pool’s commited target pages.  On a NUMA server and for a serial scan, is the threshold the total buffer pool committed pages, or the committed pages of NUMA node local to the scanning thread?  For a parallel scan with threads across NUMA nodes, is the threshold the sum of committed pages of the buffer pool on the NUMA nodes for scan tasks if spread across multiple nodes? Thanks!