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.
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.
sys.sysschobjs.nc2
and see.