More Fast Processing in tempdb

OK so last time my blog on tempdb shared some info that you may have never known, and that was the fact that if you create a permanent table in tempdb as well as one in a user database of the same DDL, and insert a bunch of rows in both, all things equal the tempdb script will run faster, much faster, than in a user database.   As mentioned, I remember Ken Henderson telling me that tempdb did some ‘shortcuts’  that other databases did not do…the only problem was, I he never told me what the shortcuts were, and I never asked.

Before I begin though, let me pause for a moment and offer you an important message – I am experimenting here (as I do in many of my blog postings).  I could give a rats ass whether 1) this material is widely known, 2) whether or not there is a “de facto” expert out there lurking among us, and/or 3) whether I am off base, incorrect, or otherwise redundant in some form or fashion.  So, don’t write me if have a comment or question that falls into any of these three categories – consider your email unread and deleted.  You didn’t write the SQL Engine - neither did I, so your expertise is probably not all that.  If you consider this material “my own views” or “user generated nonsense”, then so be it. Your opinion and you are entitled to it.  No one is making you read the material, right?  I’m not being nasty – just letting you know how this particular blog operates.  You see, I get these a-holes and lackeys sending ignorant messages and hate mail and as of late I have actually spent time responding to the garbage…an enormous waste of time. These nimrods either don’t read the blog, don’t understand my point, or take my conclusions out of context.  Again, take the information that you get from my blog for what it is and you’ll be better off I guarantee. I”m not regurgitating Books Online here at this blog, and never will.  Are we good?  Good deal.

Back to the blog.  We did an insert from before, so let’s do an update so we’re reasonably sure that we’re getting faster processing in tempdb for at least two types of DML.  I create a table with 10 million rows in AdventureWorks and then one in tempdb. Then I go update each one. Here’s the code:

drop table AdventureWorks.dbo.Data
go
select cast('' as char (4)) as col
into AdventureWorks.dbo.Data
from (select top 10000000 '' as y
     from sys.syscolumns a, sys.syscolumns b, sys.syscolumns c
     ) x

update AdventureWorks.dbo.Data
SET col='AAAA'

The update (not including the SELECT INTO), above, ran on my laptop in 2 minutes 55 seconds.  Then I ran the same script in a user database (AdventureWorks, but run it wherever you like) to compare the two.  Interestingly, in AdventureWorks, it took 7 minutes and 44 seconds, almost three times slower than tempdb.  Nice huh?  So what’s happening here?  Well, not exactly sure. I have some ideas though, so here’s one.

There’s a tidbit about tempdb that I did not know about until someone from Microsoft mentioned it to me:

The tempdb transaction log does not need to be flushed on commit

I always thought that really no database log needed to be flushed “on commit”;  it was always my understanding that when a transaction completed, the write-ahead log contained ‘dirty’ pages that were eventually written to the disk, but SQL Server didn’t necessarily have to write them at that particular moment (the time that they were committed).  If SQL Server was busy, I thought that a thread (essentially the lazy writer) would wait until resources were available to flush.  This may be incorrect now as I am finding out. I know that the recovery interval has a lot to do with the “interval” that SQL Server actually flushes for recovery purposes. Something to consider, though, given that tempdb really doesn’t recover nor does it roll forward or roll back transactions after a restart – it simply starts up a new one and then clears it (see the SQL Server logs) each time SQL Server comes back.  I need to research this further as I piece together how this relates to what we are seeing in tempdb.

If I crack open Perfmon and add database log flush counters for AdventureWorks and tempdb, run the next script in each database, I get the following:

IF OBJECT_ID ('TestTranLogFlushes') IS NOT NULL
    DROP TABLE TestTranLogFlushes 
GO
CREATE TABLE TestTranLogFlushes (id int identity, col char (1000))
GO

WHILE ((SELECT IDENT_CURRENT('TestTranLogFlushes')) < 200000)
BEGIN
    BEGIN TRY
        BEGIN TRAN
        INSERT INTO TestTranLogFlushes (col) VALUES ('')    
        IF @@ERROR=0
            COMMIT TRAN
    END TRY
    
    BEGIN CATCH
        ROLLBACK TRAN
    END CATCH    
END

Tempdb – not much happening here

User db (AdventureWorks) - one helluva lot of flushes

This one, by the way, ran in 12 seconds in tempdb, and 1:30 in AdventureWorks.  That’s over 10x faster in tempdb.  Crazy!  But, now your asking, who cares?  Who cares if tempdb can do something faster than a user database?  What could it possibly do to help?  Good question.  So, is it possible that we could actually use tempdb for some of our processing, maybe batch processing or even some online stuff, to actually leverage this fast processing?  What I mean – could we change the context of where we are performing some task over to tempdb to leverage what we are seeing?  I don’t know to be honest with you.  Stick around and let’s explore the possibilities.

Thanks for reading,

Lee Everest

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

Lighten-up, Francis.