Explicitly dropping Temporary Tables and performance – Myth or Fact? from Lee Everest on DBPedias

Start reading here, please

If you happen to have caught any of my blogs before, I like to test (and challenge) SQL Server’s “sacred cows”; I have always written code in a number of ways to see what particular design works for a given implementation. I’ve concluded a long time ago that there are no absolutes in SQL Server development, and just when you think that one way is the right way to do something, you can certainly find another that challenges that method. This may be due to several factors: different types of data, small or large amounts of data, low to high transaction environment, hardware, server or disk setup, etc. The list goes on forever. And, every time that a noob comes to me and asks, “Hey what’s the best way to write this?”, I am always quick to say, “I don’t know, I’d have to go write it. Why don’t you try it several ways and find out for yourself?”. So, I’m geeking with another of these sacred cows, just for fun, to see what I can come up with – this one is whether explicitly dropping temporary tables is a hindrance to performance.

Once Upon a Time

About five years ago, I worked on a super-high transaction processing system that was fed by BizTalk. If you’ve ever worked with BizTalk, you know exactly what it can do to SQL Server as I do – kick off a bunch of orchestrations and and you can flat-out flood SQL Server. I talkin’ bring the database server to its knees, bra! And we did it all of the time until we learned the art of fine-tuning locks and blocks with BizTalk, and using query hints in just the right places. It was grueling but a lot of fun. At the time, Microsoft in Dallas stated that it was the largest BizTalk setup that they knew about, anywhere. We fought with our system for hours, days, even weeks, until we had it down. Great learning experience for sure. PITA, but we had some fun times.

During that time, a rep from Microsoft came by and looked at my code. For some reason or another I had gotten into the habit of explicitly dropping temporary (temp) tables, which he quickly pointed out thank you. Don’t ask me why, I have no idea at the time, I was just doing it. This was very early on in SQL Server 2005 development. Anyway, he goes on to tell me that this will cause things to run slower, and may create unnecessary CPU by doing so…essentially, his suggestion was to just go ahead let SQL Server get rid of the temp objects gracefully and without the “Drop Table #xxx”. He never mentioned anything about GAM or SGAM, allocations, or reuse of temp objects. And because we were suffering from locks, blocks, and deadlocks, I was willing to try anything to help speed things up. We also had quite a few nice scripts to look into performance, so we could readily see if things helped or hurt our code.

A guru chimes in on the subject

This month’s SQL Server Magazine (July 2010), super-stealth Ninja SQL guru Andrew J. Kelly, a SQL Server MVP and practice manger at Solid Quality Mentors, had a paragraph about this very thing – explicitly dropping temp tables. He mentioned that in one situation a query or process decreased in execution time by 50ms from removing drops and/or truncates, however, he didn’t detail the situation much or offer up any test results or cases to speak of, only that he “confirmed that explicitly dropping a temporary table will defeat the ability of using caching mechanisms”. I don’t doubt him a bit, he’s one helluva SQL Server technician, but wouldn’t it be fun to check this out for ourselves nonetheless?

No guru here, but I did stay at a Holiday Inn Express last night

I tested this notion of firing off scripts that create hundreds of transactions/second today to see if I could find out for myself whether or not there was any fact-based evidence that explicitly dropping temp tables caused slowdowns in a SQL Server. As I type I am debating on if I should post my code or not because I don’t really think folks will try it out. Maybe I’ll just skip that part, but if you want it send me an email.

Essentially what I have put together for my test cases is the following:

A batch file that fire off several sqlcmd commands using DOS start, so that I get multiple spids at a time running concurrently and hitting the same tables
A stored procedure using crossing multiple databases that does a simple fetch of a customer, fetch a product, check to see if product amount >0, create an order, and insert the order into an Order table. All are done randomly by doing ORDER BYs using NEWID()
The proc creates a temp table and then inserts some metadata at the end
The proc either drops/does not drop the temporary table depending on the test
In perfmon, I observe roughly 650 transactions/second in tempdb, and maybe 200 transactions/second on average across the three databases. I actually added several counters in perfmon, including as work tables created and work table cache, none of which were terribly interesting
If you want the code, ping me and I’ll zip it up and email it to you as mentioned, or try your own design out for comparative purposes

I”m watching the Bachelorette while writing this, BTW.

My meager eight tests are below. Note that I run them with the temp table drop, and without, and the last two are with a large column so that I get close to a page allocated for each insert. (A friend suggested this, why I have no idea. But I thought I’d see if it made any difference because it sounded reasonable). Interesting findings I guess:

Notice that with the explicit temp table drop, I get an average execution time of 248 seconds, and without the drop and average time of 285 seconds. Again, this test is executing 600+ transactions/second across three spids running a loop of 10,000 rows per iteration. In all, I end up inserting between 150K and 190K records inserted during a single run, which lasts about three minutes. In my tests – granted, probably not worth much – show that dropping a temp table is actually faster given my test, hardware, and stored procedure.

The end, or just the beginning?

What’s fun about what we do as SQL Devs and DBAs is that we are constantly facing change; change in SQL Server and how it performs, changes as new features are added, changes as the database engine is tweaked. This is what is so interesting about working with this great tool! One minute, just when you think you have something figured out, a whole new list of ideas opens up from something that someone experiences. What I have presented here is just dorking with SQL Server…I’ve really not done more than a couple of hours worth of work, and certainly don’t have a well-thought presentation by any stretch. Yet I’m sort of surprised at what I found. And, what’s even more exciting is that you or someone else could take this idea and come up with, given a different set of constraints, the very opposite results that I have.

Thanks for reading,

Lee Everest

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

“Land's sakes, Wolfie; ain't you gonna eat me???”