Is anyone able to explain some behaviour I'm seeing with SQL Profiler? Taking the following two batches:
exec sp_executesql N' declare @t table ( x int ) insert into @t (x) select top 10 number from dbo.gennumbers select * from @t union select * from @t ' exec sp_executesql N' select top 10 number from dbo.gennumbers union select top 10 number from dbo.gennumbers '
(this is a simplification of some refactoring I'm doing; in reality the temp table is populated through a CTE that's reused in the final query, so mitigating the amount of duplicated work SQL Server needs to do by caching the data).
In this example, GenNumbers is simply a numbers table with a single column that just contains a sequential run of numbers - nothing special.
In profiler, I'm getting the follow results:
Batch 1, with the temp table:
Event TextData Reads SP:StmtCompleted INSERT INTO(...) 27 SP:StmtCompleted SELECT * FROM ... 6 SQL:StmtCompleted exec sp_executesql 170
Batch 2, a direct hit:
Event TextData Reads SP:StmtCompleted SELECT TOP 10 * ... 6 SQL:StmtCompleted exec sp_executesql 6
What I'm trying to understand is the anomoly of the final read count on the
SQL:StmtCompleted. I was expecting that the total reads should equal the sum of all reads that occur within the batch, however that doesn't seem to be the case in batch 1 - in fact, it's significantly higher. Whereas batch 2, that does a single select, final reads == actual reads.
There's nothing obvious in the execution plan to see where this extra time is going, but maybe I'm not capturing the right thing - does anyone have any thoughts on what's actually going on? I'm wondering whether I need to worry about the extra 100 reads that SQL Server is "giving" me, given that this read count is five or six times higher than the actual read count.
I'm assuming it's something to do with SQL needing to maintain information about the temporary table, but it's not clear exactly what. Any light shed would be useful.