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