3

In the docs it says we can share index scans (Advanced Scanning). Now here they used wait stats and statistics and i did not find an extended events session that could directly identify it.

The question is - how can i know that an advanced scan has been used? I know about ordered false property, but that just says that it did not use the order in the index key?

Martin Smith
  • 87,941
  • 15
  • 255
  • 354
Suleyman Essa
  • 167
  • 1
  • 8

1 Answers1

6

It depends on what you mean by a "direct way". There's no extended event or trace flag*, for example.

One can attach a debugger and see if the merry-go-round (MGR) code is activated, which is arguably direct but not always convenient. I usually use Get/SetMerryGoRoundPosition on sqlmin!BaseSharedHoBt.

There are indirect methods like examining the physical locators of returned records and comparing them.

The Enterprise Edition Advanced Scanning feature (marketing name, internal name is MGR) is only activated for some types of allocation ordered scans, which are otherwise large enough compared with the buffer pool. The details are complex, without a clear pattern, and undocumented. Your second link uses a heap/table scan not an index scan by the way.

As I understand it, MGR was only lightly documented so it could be used in industry standard benchmarks. A benchmark special, in other words.

As a point of possible interest, simultaneous scans are not technically required to use MGR. The current position of an MGR scan for a rowset is updated once per second. A same-rowset qualifying scan that starts less than one second after another qualifying scan finished will use MGR.


* Undocumented trace flag 6974 disables MGR

Paul White
  • 94,921
  • 30
  • 437
  • 687