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?


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.

  • I totally agree with the above Remus - and thanks for an excellent response. The problem is that we are causing a supposed performance hit on a third parties applications (with several databases on a server on which we have a database of our own - we need to perform queries vs. their data). I still think you're right, mind - I/O wise I would expect the new approach to perform considerably worse than previously - contention wise, from tempdbs point of view things'll be better - from ours, somewhat worse. – Will A Jul 4 '10 at 17:26
  • How many tempdb files do you have? The standard setup does not really scale at all - you should have multiple tempdb dile and log files, one per visible processor core (2 each if hyper-v). – TomTom Jul 4 '10 at 17:34
  • 1
    You must read these two articles: sqlskills.com/BLOGS/PAUL/post/… and sqlskills.com/BLOGS/PAUL/post/… as they address the most common tempdb scalability issues. – Remus Rusanu Jul 4 '10 at 20:30
  • @TomTom whoa, whoa. Multiple log files? Really? – Aaron Bertrand Mar 5 '13 at 17:51

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?





  • +1 completely agree - optimize tempdb, don't reinvent the wheel as your implementation will be worse. :) – Donnie Jul 4 '10 at 17:04
  • I'm all for not following this path if it's not justifiable - thanks for the information Ben - I'll investigate and make suggestions to our DBA as appropriate. – Will A Jul 4 '10 at 17:27

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

  • Looks useful - thanks SPE109, I'll check into this and make recommendations to our DBA as appropriate. – Will A Jul 4 '10 at 17:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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