Sql-server – Monitor number of records returned in a long-running SELECT statement during SSAS processing

There is an implicit CREATE TABLE at the beginning and DROP TABLE @T at the end as the table goes out of scope that aren't associated with either the INSERT or the SELECT statement.

If you replace with a #temp table you should see some additional reads for both the drop and create statements. For me I see 36 for the CREATE and 100 for the DROP so that accounts for 136 of your missing 137 reads.

(Script with #temp table)

EXEC sp_executesql N'
   CREATE TABLE #T 
  ( 
     x INT 
  ) 

INSERT INTO #T 
            (x) 
SELECT TOP 10 number 
FROM   master..spt_values 

SELECT * 
FROM   #T 
UNION 
SELECT * 
FROM   #T 

DROP TABLE #T
'

An execution plan is only going to have the code that was last compiled and used, to my knowledge. In troubleshooting applications myself I have come across where a statement I was looking at through the execution plan was actually written with sp_executesql and so was dynamic T-SQL that contained a bit more logic than the end result.

You don't specify the version you are working with but I would suggest using a server-side trace or Extended Event session to map out the sequence of commands being passed so you fully understand what is being done in your situation.

My first thought is the application (or the developer) wrote the insert statement and in some form or fashion is doing a check on something (i.e. maybe errors). In that check if it is good they are simply passing what ends up looking like SELECT 0, where if an issue occurred it might return SELECT 3910 that would mean something to that application. You are only going to see the full logic (if any) behind all of it by doing a trace on the commands.