Temp tables and the people who love them

The other day I received a call for help on a situation I hadn't encountered before. The caller provides a hosting service in which several db's reside on a single server running on a single instance. The problem they were having is that some reporting being done by one customer on their db was blocking all of the other customers ability to run their reports. Knowing the application the caller was speaking of, my first reaction was simply, "that's not possible, the app doesn't work that way." Believing that as I may, all evidence was to the contrary so I was forced to alter my thought process in attempt to be a little more productive. Before going over there to look at the situation myself I mentioned that I was leaving to my supervisor and explained what the problem was. He suggested that I look at the way temp table were being built as he had read something a while back where the writer had a similar problem.

That is when it hit me, all of the db's on that server are using the same tempdb. Knowing how the application was designed and knowing that running multiple db's on a hosted server was never part of said design, this made perfect sense. Before going to the site, I called and asked them to start looking at tempdb. Upon my arrival they had determined that tempdb was indeed the culprit. The problem was that they had put a new server into production and had the tempdb sized to small and had the auto grow feature turned on. This was causing tempdb to be in a near constant state of growth. When the db grows it locks itself so it was in a near constant state of locking.

So what did we learn? First, contrary to Dilbert cartoons, your supervisor might know something. Second, know your environment. Know whether other apps will be using the same tempdb or if several instances of your app will access the same tempdb or other db's. Third, sizing any db is important. Sizing tempdb is even more so. Starting out with an incredibly small db size and letting MSSQLServer auto grow it for you by a percentage is asking for trouble. (Technically, this could go under number two of knowing your environment, but when you use the term first you should always at least make it to third).

In addition, in an effort to help us all better understand why, when and how to use tempdb, here is a link to an article at sqlservercentral.com that explains it in a very nice and easy to understand way. As always, there are exceptions to the rules and testing is the best way to determine the performance impact of your choice.

And once again feel free to ask questions, post comments, or simply say hi. It is always nice to know this is being read by someone.