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)
  • 3
    What is point in truncating the temp table, would it not be possible to simply drop and recreate it. – Shanky Oct 11 at 11:00
  • @Shanky yes, the truncate is completely useless and can be removed. Its a old, deprecated line from the dark ages of the company. Nevermind. The question here is not if truncate can or cannot be dropped. The question is: Why the behavior, whats the issue? I don't like fixing something if i dont understand the issue – Vladislav Zalesak Oct 11 at 12:24
  • Are you stating that inserting 139,000 rows only results in 300 reads? That seems extremely unlikely. – Sean Gallardy Oct 11 at 13:22
  • Yes. create table #test (PrimKey integer primary key) insert into #test select top 150000 PKColumn from Production.BigTable with set statistics io on shows no logical reads on tmp table. The same try with non-temp table shows reads – Vladislav Zalesak Oct 11 at 13:38
  • @VladislavZalesak What you are telling above is wrong you sure are missing something. You are saying that for temp tables there would be no logical reads and for physical table there would be for same set of query. Frankly that does not makes sense – Shanky Oct 11 at 15:00

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

We are continuing to work on improving tempdb performance and metadata contention, but in the meantime, there are some best practices you can employ in your code that might help avoid the contention:

  1. Do not explicitly drop temp tables at the end of a stored procedure, they will get cleaned up when the session that created them ends.
  2. Do not alter temp tables after they have been created.
  3. Do not truncate temp tables
  4. Move index creation statements on temp tables to the new inline index creation syntax that was introduced in SQL Server 2014.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.