Paul White
The following db<>fiddle:
<>https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=2cb25ee984d14a05757a85cf318e3b5b
...produces an execution plan with an *RID Lookup* like:
The *RID Lookup* is expected, because the nonclustered index is not covering for the query. The execution engine has to fetch the `padding` column from the heap base table.
My question is about the *Compute Scalar*:
**What is the `BmkToPage` function, and what purpose does it serve in this plan?**
Top Answer
Paul White
The *Nested Loops Join* has the *Optimized* property:
This means SQL Server sorts rows arriving from the *Index Seek* to promote a sequential I/O data access pattern at the *RID Lookup*. The sort is invisible, and known as a *batch sort*. Craig Freedman discusses optimized nested loops and batch sorting in the article [OPTIMIZED Nested Loops Joins][1].
The invisible batch sort is unlike a normal *Sort* plan operator in that it can never spill to *tempdb*. A batch sort makes a best effort to sort rows into the desired order using the memory grant it is given. When full sorting cannot be achieved, the partially sorted output is still helpful to the *RID Lookup*. The optimizer chooses a batch sort when it thinks sorting could be useful, but it cannot justify the cost of a full Sort operator
The general idea is to sort the upcoming *RID Lookups* into heap page id order, to promote sequential I/O. The heap page numbers are stored in the nonclustered index as part of the heap bookmark (labelled `Bmk1000` in this plan). These page numbers are extracted from the bookmark by the intrinsic function `BmkToPage`.
The function itself is trivial, but you may encounter adverse performance effects due to the invisible sorting. Older versions of SQL Server could also suffer from an [excessive memory grant][3] for the hidden sort.
Optimized nested loops can be disabled in modern SQL Server versions with the query hint:
```sql
OPTION (USE HINT ('DISABLE_OPTIMIZED_NESTED_LOOP'));
```
Note that this hint will also prevent an explicit *Sort* operator before the *Nested Loops Join*.
Disabling optimized nested loops alone can be achieved with [documented trace flag 2340][5]:
>Causes SQL Server not to use a sort operation (batch sort) for optimized Nested Loops joins when generating a plan.
### Further reading
- [Batch Sort and Nested Loops][2] by Dmitry Piliugin.
- [Addressing large memory grant requests from optimized Nested Loops][3] by Pedro Lopes.
- [Nested Loops Prefetching][4] by me.
[1]: https://blogs.msdn.microsoft.com/craigfr/2009/03/18/optimized-nested-loops-joins/
[2]: https://www.sqlshack.com/batch-sort-and-nested-loops/
[3]: https://blogs.msdn.microsoft.com/sql_server_team/addressing-large-memory-grant-requests-from-optimized-nested-loops/
[4]: https://www.sql.kiwi/2013/08/sql-server-internals-nested-loops-prefetching.html
[5]: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql