We have an application which has heavy usage of temp tables. I'm trying to sort out what kind of lock issues this can present. So I did a test where I created a temp table and then inserted 100.000 rows in it:
declare @counter int
set @counter = 0
while @counter < 1000000
set @counter = @counter + 1
insert into #tmp values('test','test','test',NULL,NULL)
At the same time I executed sp_lock to see what happens. What I see is that an X lock of type FIL appears. So, this means that every insert creates a database file lock on the tempdb? Doesn't sound that good.
View Complete Post