Fast processing in tempdb

About 5 years ago or so, I was chatting with the late great Ken Henderson and we were talking about tempdb processing.  This was about the time when SQL Server 2005 had just come out and I was working on a highly-visible, large transaction processing system at a local retailer in Dallas.  This project by the way was super cutting edge implementation and used BizTalk to write thousands of transactions per second across our SQL Server.  I had noticed in testing that tempdb tended to be able to handle substantial throughput over user databases, so I pinged him Ken about this.  One reason that I also brought it up was because I noticed in all of this previous books (including his classic The Guru's Guide to Transact-SQL) his examples were mostly done in tempdb, and I was curious why he had used this space for his work.  This database does in fact process transactions at a significant rate compared to other databases;  however, geeking with it I’m seeing a few gotchas for moving some SQL Server processing there.  I’m going to jot down some notes on this subject in a little series of blogs.

Let’s check it out.  Run the following:

SET NOCOUNT ON
DROP TABLE tempdb.dbo.test
GO
DROP TABLE AdventureWorks.dbo.Test
CREATE TABLE tempdb.dbo.Test (id int identity(1,1) not null PRIMARY KEY, col char (1020) )
GO
CREATE TABLE Adventureworks..Test (id int identity(1,1) not null PRIMARY KEY, col char (1020))
GO 

I’ve created two “permanent” SQL Server tables, one in tempdb and one in a user database.  If you don’t have AdventureWorks then simply put it somewhere else.  Create the data:

DECLARE @ID int=10000
BEGIN
    TopLoop:
    INSERT AdventureWorks..Test (col)
        SELECT ''
    SET @ID-=1
    IF @ID > 0
        GOTO TopLoop    
END
GO

Now run the same script, but replace AdventureWorks with tempdb.  Here is the trace from profiler:

The script which inserted slightly less than 10MB into AdventureWorks ran in 3.376 seconds, while the same script that was run in tempdb ran in 471 milliseconds, roughly 2.9 seconds faster which is equivalent to about 86% faster percentage-wise.  Smokin’!  This test was also not done on any special box, and no mods were made to tempdb or the tempdb files – I’m using a single file on a dual-core laptop. (I don’t have to worry about throughput or bottlenecks here since I’m not allocating many IAM pages in this simple example).  Compare different sizes – you’ll find that tempdb times will beat the user database times in all cases, small amounts of data or large.

Cool eh?  There are some gotchas to doing this, however.  I’ll show you some of the things that I found…next time.

Thanks for reading,

Lee Everest

------------------

‘Tis the season!