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
WITH TRUNCATE (NOT ALWAYS, thats the weird part)
truncate table #test
insert into #test select top 150000 PrimKey from dbo.test
Table '#test_______________________________________________________________________ ________________________________________000000000141'. Scan count 0, logical reads 319955, physical reads 0, read-ahead reads 217, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test'. Scan count 1, logical reads 246, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(150000 rows affected)
DROP/CREATE
drop table if exists #test
create table #test (PrimKey integer primary key)
truncate table #test
insert into #test select top 150000 PrimKey from dbo.test
Table 'Test'. Scan count 1, logical reads 246, physical reads 0, read-ahead reads 232, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(150000 rows affected)
