1

Recently I came across reading in one of the blogs that if a temp table name is prefixed with different/random names (in different procedures) that could benefit in reducing the concurrency issues / latch waits on tempdb.

For example same temp table name #iamTemp used in different procedures vs #95e8D_iamTemp, #A81Me_iamTemp, #x611B_iamTemp ... in various procedures.

I used following code to do a test run but I'm not convinced with the results, all I'm trying to understand is whether it is helpful or not.

--For same temp table prefix

drop table if exists #abc
create table #abc(id int)
insert into #abc values (1)
insert into sm_sandbox..same_temp_table_prefix
SELECT @@spid as SPID, sys.fn_PhysLocFormatter (%%physloc%%) AS 'Location(File:Page:Slot)', getdate() as [DateTime], '#abc' as tempTableName, *
FROM #abc

--For random temp table prefix
declare @tempName sysname,
        @sql nvarchar(2000)
select @tempName='#'+LEFT(REPLACE(NEWID(),'-',''),5)
select @tempName
set @sql = '
                drop table if exists '+@tempName+';
                create table '+@tempName+'(id int);
                insert into '+@tempName+' values (1);
                insert into sm_sandbox..random_temp_table_prefix
                SELECT @@spid as SPID, sys.fn_PhysLocFormatter (%%physloc%%) AS [Location(File:Page:Slot)], getdate() as [DateTime], '''+@tempName+''' as tempTableName, *              
                FROM '+@tempName+';'

exec sp_executesql @sql

And, used this SQL Query stress tool to simulate concurrency and the test was made with multiple variations of threads and iterations. But in all the cases I have seen both ways of temp table creation were falling under random files and pages.

Results attached below.

enter image description here

This is the referenced blog comment:

That table is basically sysobjects. My guess is that you are creating and dropping tables very quickly in tempdb. The solution to that contention is either not to do that, or use long table names, and potentially with very different starting portions of the name, to spread the inserts out over multiple pages in that system table.

2
  • 1
    @SivaDasari, the referenced comments are about tempdb metadata contention, specifically PAGELATCH_EX page waits on the underlying sys.sysschobjs.nc2 system table index on the table name key. The suggestion is these can be mitigated with more random table names. That contention is unrelated to the physical temp table location highlighted in your question.
    – Dan Guzman
    Commented Apr 23, 2022 at 18:15
  • Thank you @DanGuzman for the clarification, that makes total sense now. To my learning I will find a way to do some tests and see how the temp tables are allocated in system table sys.sysschobjs.nc2 and see.
    – S.D.
    Commented Apr 25, 2022 at 16:10

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.