Writing code that scales – Part 4 – Temp DB

Temp DB is not normally something that SQL developers think about – after all , it’s a “background” feature of Sql Server that has nothing to do with the T-SQL code or Stored Procedure that they are working on.

However , that assessment is incorrect. Temp DB is a vital cog in the engine ( or should that be gearbox ? ) of SQL Server , and if your code places too much of strain on TempDB, it will definitely lead to performance and scalability issues later on.

While you might not be too concerned with the setup of Temp DB as a developer ( eg. Whether there are multiple file groups on multiple disks), some of your code will have a direct impact on it, the most obvious one being when you use Temp Tables. IF you are the type of developer who decides that it’s a good thing to go and create Temporary Tables whenever possible , remember that the performance you get in the development environment is not the performance you will get in production. On the production server , Temp DB will be shared with other databases.

Not only will this impact your code , but if you are using tempdb inefficiently it will impact other production applications.

Things to Consider

Before you create Temp Tables :

1. Don’t use temp tables unnecessarily. Sometimes a clever statement ( like a CTE ) will do the job. As far as I know , a CTE won’t create a temp table if the amount of data is small enough to fit in RAM. Another option for small data sets is the table variable, although once again , if the set of data is very large, even a table variable will write to TempDB.
2. If you have a set of data that will be referenced by multiple queries , why not persist it to a proper table ? You can always truncate it when it’s not needed anymore and it will be stored on the disk that the database uses, not the potentially high impact disk that temp db uses.

If you have to use Temp Tables :

1. Always drop the temp table after you have used it.
2. You don’t have to put all the columns and rows from the permanent table into the Temp Table
3. Try not to use SELECT INTO to create the temp table. This will create locks on system objects while it figures out how to create the Temp Table
4. Consider placing a clustered index on the TempTable. There will be a cost to populating the temp table , but if it is a large data set being referenced by many operations , it might be valuable ( in this case however I still prefer a permanent table )