While I was preparing for my Tempdb presentation, I learned many interesting facts about Tempdb. Thanks so much Bob Ward (t|g) for providing me with the resources to prepare for my presentation. Bob Ward has presented an amazing 3 hour session about Tempdb for the PASS Summit couple of years ago. This information is invaluable.
Why I am blogging about the interesting things about Tempdb then?
There were lot of things about Tempdb that surprised me as I learn about tempdb from Bob Ward presentation. Here is the link to the complete session. I thought of writing a blog post about these interesting and cool things so you all can know about them as well.
Do you know?
- Each time you restart SQL Server, tempdb gets created from the model database and acquire all the properties from the model database including any objects you created in model database? First the primary data file gets created from model database copying one extent at a time from model database to tempdb database. Later transactional log file gets generated. After that, secondary database files will be created for tempdb. Until the tempdb is created, the model and the tempdb databases will be locked and no one can connect to it though user connections are allowed at this point of time as master database will be first opened before tempdb.
- You can create tempdb very quickly by using the -f startup parameter but still retain the tempdb original sizes. This will help when you are having problems starting tempdb.
- If you enabled Instant file initialization, creating data files will be quick because we do not have to zero out the database files and immediately start using these files. Instant file initialization doesn’t support zeroing out of the transaction log files. If you would like to increase the size of the log file later, then we have to zero out the entire log. When you restart the tempdb and if you already have the big transactional log file available, we do not have to zero out the entire log. We just have to zero some of the records out the first virtual log files.
- When a temp table is dropped, the meta data of the temp table is cached. Temp table data is truncated keeping the temp table meta data for reuse.
- You cannot cache the temp table when it is not associated with stored procedure, functions and triggers. Temp table caching is not supported for ad-hoc queries.
- If multiple people are using the same stored procedure having the same temp table at the same time, multiple temp tables can get created and they can be reused as all these tables are cached.
- Tempdb do the minimum logging. We do very less logging because we do not keep the data persisted when we restart the SQL Server. We create tempdb each time we restart with SQL Server. We don’t need a crash recovery for this database. We need transaction log for rollbacks. There is no redo for tempdb because the objects we create are not persisted on restart.
- When the Checkpoint process runs on SQL Server automatically, it skips the checkpoint on tempdb. When you manually run a checkpoint, then the checkpoint process happens on tempdb which can take some time to run because we do not do the checkpoint by regular automatic process. Checkpoint process will truncate the t-log of tempdb.
- Table variables are stored in tempdb and there is no rollback support for table variables.
- Index sorting is done in the user databases and not in tempdb unless you use SORT_IN_TEMPDB option. If the sort is not enough to fit in memory, it will spill to disk and use temporary space in tempdb.
- You are enabling the version store in tempdb if you are using snapshot isolation, read committed snapshot isolation, online index rebuild and triggers.
- If you try to change the size of the tempdb file using management studio, altering of the file to change the size doesn’t happen. SHRINKFILE happens behind the scenes. When you manually alter the file using the script, the alter database and changing the file will be successful and will change when you restart the server.
- Auto shrink is not supported for tempdb and we cannot shrink the internal objects inside the tempdb.
- When you run CHECKDB on tempdb, we run with implicit TABLOCK because a database snapshot is not supported for checkdb on tempdb. Online checkdb is not supported for tempdb. Checkdb on tempdb will run check tables on all the tables including the temp tables we create. We cannot do repair on tempdb because we cannot place the tempdb in single user mode. CHECKSUM is supported in tempdb.
- We can change the location of the tempdb files while in use but it is effected only after the SQL Server restart.
These are some of the interesting and cool things I learned about tempdb! I hope you learned something new today. Thanks for reading!