Why do some rows returned by sys.dm_exec_query_profiles have "???" for the physical operator name?

Question

SQL Server 2014 introduced the sys.dm_exec_query_profiles 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:

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:

Running the query with MAXDOP 1 resulted in a few more hidden nodes. The ??? node showed up in the same place as before:

There was also a new one not present in the parallel plan:

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?

score 7 · Accepted Answer · edited Jun 04 '21 at 03:39

Batch mode adapters (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 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 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:

╔════════════════════╦════════════════════════╦═════════╦═══════════╗
║    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:

╔════════════════════╦════════════════════════╦═════════╦═══════════╗
║    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.

Why do some rows returned by sys.dm_exec_query_profiles have "???" for the physical operator name?

1 Answers1