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.
Best Answer
There is an implicit
CREATE TABLE
at the beginning andDROP TABLE @T
at the end as the table goes out of scope that aren't associated with either theINSERT
or theSELECT
statement.If you replace with a
#temp
table you should see some additional reads for both thedrop
andcreate
statements. For me I see 36 for theCREATE
and 100 for theDROP
so that accounts for 136 of your missing 137 reads.(Script with
#temp
table)