SQL Server 2014 introduced the [sys.dm_exec_query_profiles][1] DMV which provides a way to monitor real time progress of executing queries. Recently I was looking at a somewhat complex query in SQL Server 2016 SP1 and noticed that `sys.dm_exec_query_profiles` contained extra nodes that weren't present in the query plan. These nodes had `???` for the `physical_operator_name`:
[![???][2]][2]
For the parallel version of the query there was just one hidden node. The node had an `object_id` associated with the inner table of a nested loop join. According to the `node_id` here is where the node should have appeared:
[![parallel ???][3]][3]
Running the query with `MAXDOP 1` resulted in a few more hidden nodes. The `???` node showed up in the same place as before:
[![serial 1 ???][4]][4]
There was also a new one not present in the parallel plan:
[![serial 2 ???][5]][5]
These only seem to show up around some nested loop joins. I cannot recall seeing this behavior on SQL Server 2014. Unfortunately, the query is complex and I cannot upload an anonymized plan.
What's going on here? Why does `sys.dm_exec_query_profiles` report extra query plan nodes that don't show up in the graphical plan?
[1]: https://msdn.microsoft.com/en-us/library/dn223301.aspx
[2]: https://i.stack.imgur.com/w2A9j.png
[3]: https://i.stack.imgur.com/1jbQD.png
[4]: https://i.stack.imgur.com/OJUpL.png
[5]: https://i.stack.imgur.com/70pEe.png
[Batch mode adapters][1] (places in a query plan in which row processing switches to batch processing or the other way around) show up as `???` in the DMV with a `thread_id` of 0. However, the example query doesn't use batch processing so that isn't the cause here.
Nested loops prefetching can also be responsible for extra rows showing up in `sys.dm_exec_query_profiles`. There is a [documented trace flag][2] for disabling nested loop prefetching:
> Trace flag 8744 disables pre-fetching for the Nested Loops operator.
>
> Incorrect use of this trace flag may cause additional physical reads when SQL Server executes plans that contain the Nested Loops operator. For more information about the Nested Loops operator, see the "Logical and physical operators reference" topic in SQL Server 2005 Books Online.
If I add a query hint of `QUERYTRACEON 8744` to the query then the `???` nodes no longer appear.
For a reproducible example of nested loop prefetching I'm going to borrow Paul White's example against Adventure Works from his [Nested Loops Prefetching][3] article:
SELECT TOP (1000)
P.Name,
TH.TransactionID
FROM Production.Product AS P
JOIN Production.TransactionHistory AS TH
ON TH.ProductID = P.ProductID
WHERE
P.Name LIKE N'[K-P]%'
ORDER BY
P.Name,
TH.TransactionID;
If I run that query against SQL Server 2016 SP1 and quickly capture the output of `sys.dm_exec_query_profiles` I get the following results:
```none
╔════════════════════╦════════════════════════╦═════════╦═══════════╗
║ OBJECT_NAME ║ physical_operator_name ║ node_id ║ thread_id ║
╠════════════════════╬════════════════════════╬═════════╬═══════════╣
║ NULL ║ Top ║ 0 ║ 0 ║
║ NULL ║ Nested Loops ║ 1 ║ 0 ║
║ TransactionHistory ║ ??? ║ 2 ║ 0 ║
║ Product ║ Index Seek ║ 3 ║ 0 ║
║ TransactionHistory ║ Index Seek ║ 4 ║ 0 ║
╚════════════════════╩════════════════════════╩═════════╩═══════════╝
```
If I run the same query in SQL Server 2014 I get these results:
```none
╔════════════════════╦════════════════════════╦═════════╦═══════════╗
║ OBJECT_NAME ║ physical_operator_name ║ node_id ║ thread_id ║
╠════════════════════╬════════════════════════╬═════════╬═══════════╣
║ NULL ║ Top ║ 0 ║ 0 ║
║ NULL ║ Nested Loops ║ 1 ║ 0 ║
║ Product ║ Index Seek ║ 3 ║ 0 ║
║ TransactionHistory ║ Index Seek ║ 4 ║ 0 ║
╚════════════════════╩════════════════════════╩═════════╩═══════════╝
```
In both cases the nested loop prefetch optimization happens. It appears that only SQL Server 2016 reports it though which could explain why I've never seen this in SQL Server 2014.
[1]: https://dba.stackexchange.com/questions/97650/what-exactly-can-sql-server-2014-execute-in-batch-mode
[2]: https://support.microsoft.com/en-us/help/920093/tuning-options-for-sql-server-when-running-in-high-performance-workloads
[3]: https://www.sql.kiwi/2013/08/sql-server-internals-nested-loops-prefetching.html
