SQL Server - Temporary vs. Physical Tables

Question

Hi All,

A movement is afoot in my place of employ to move away from using #temp tables and instead to use permanent physical tables with SPIDs. Whenever one would have previously INSERTed INTO a #temp table, now an INSERT INTO dbo.MyPermanentTable (SPID, ...) VALUES (@@SPID, ...) is required - together with a bunch of DELETE FROM dbo.MyPermanentTable WHERE SPID = @@SPID statements at the beginning of e.g. a stored procedure. Additionally, it goes without saying that anywhere that these 'permanent tables for storing temporary data' are used, one has to be careful to include a WHERE SPID = @@SPID.

The logic behind the move towards this practice is that it'll improve overall performance of the server on which the queries are running (by reducing I/O and contention in tempdb). I'm not keen on this approach for a number of reasons - it's ugly, potentially dangerous and seems like it may well harm the performance of those queries that use the new scheme.

Does anyone have any experience with this or similar approaches to eliminating #temp tables?

Thanks,

Will.

Answer 1

This seems like a drastic solution. There are many articles online about reducing tempdb contention (and optimizing its usage) -- has your org thoroughly examined that avenue?

http://www.sql-server-performance.com/tips/tempdb_p1.aspx

http://www.sqlservercentral.com/blogs/robert_davis/archive/2010/03/05/Breaking-Down-TempDB-Contention.aspx

http://searchsqlserver.techtarget.com/tip/Optimize-tempdb-in-SQL-Server-by-striping-and-splitting-to-multiple-files

etc.

Answer 2

Sounds to me like you should be troubleshooting the performance problems within tempDB, there's a few suggestions in here

Answer 3

It can be demonstrated quite easily that it will not reduce IO nor contention, but instead increase both.

• IO: Every row inserted, read or deleted from a #temp table will be now inserted, read or deleted from the @@SPID table. But every row will be wider with an additional @@SPID column, hence the number of pages needed will slightly increase and the IO will be ever so slightly bigger. But more importantly, the drop of a #temp table and the initialization of a new session's #temp table by a session will now have to be simulated with a DELETE FROM @@spidTable WHERE spid = @@SPID, and thus truncate/create operation (ie. page extent management operations) will be transformed in row operations, incomparable slower.
• Contention: Every scan that was using page locks on the #temp table will now have the potential to lock a page with unrelated spid rows, thus creating previously non-existing contention. Every update that does more that hits the lock escalation threshold has the opportunity to escalate the lock to table lock and thus block every other spid.

So while is true that you won't hit the mythical IAM/SGAM/GAM contention in tempdb, the sole reason why this would happen is because your operations will become far slower due to ordinary extra IO and extra contention.