Truncating TEMP table before insert leads to big amount of logical reads on TEMP TABLE

by Vladislav Zalesak   Last Updated October 11, 2018 11:06 AM

Setup: We have a complex set of queries, that runs quite often (about 3000 times per hour) for different users. At the start of the process, .NET application creates a set of temp tables during the process, .NET application calls a set of SQL queries, they look like this:

truncate #QuerySpecificTable

insert into #QuerySpecificTable
select PrimaryKey
  from Production.QuerySpecificTable
  join ...
 where ...

Issue i came across while performance tuning one specific query. ~139000 records get inserted into the temp table. This causes ~300k logical reads on the #QuerySpecificTable.

When i remove the truncate, it drops to ~300 reads on #QuerySpecificTable. CPU statistics show drop in CPU time (~900->300ms so not sure about precision and reliability.)

The issue is, when trying to do this in SSMS, i cannot 100% reproduce the behaviour and make sure and demonstrate removing the truncate (even through it wouldnt be a problem) is a good thing to do. The biggest issue is NOT KNOWING why and what is happening here

Related Questions

Updated October 07, 2017 11:06 AM

Updated May 03, 2018 05:06 AM

Updated May 18, 2017 17:06 PM