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)
Best Answer
The data in there is not really dropped, first of all. It has to do with how Temdb is implemented
Also, Microsoft has a great blog on TempDB and contention that addresses the following points:
Tempdb - Files, Trace flags, updates - Microsoft Blogs