TempDB is a very unique database. It is critically important but wiped out every time you restart SQL. SQL server does a lot of important work with the TempDB, but it's all temporary. It is a scratch file if you will.
TempDB gets its own disks. The faster the better for both reads and writes. This file can get a lot of activity and the disk contention it creates will be noticeable. Some query operations can spill out of ram into the TempDB and it's possible to sort indexes in there as well.
Use more than one TempDB if you have lots of cores in your server. Lots of people have different ideas on how many files you should use for TempDB. One rule of thumb is one file per 4 cores. A few files is OK, but don’t go overboard with it. The important detail is to manually size the files so they are all the same size. SQL will use them more evenly when you do that.
Another good tip I picked up from one of my local SQL user groups is to make TempDB your default database instead of master for all users that don't have a more appropriate default. The idea is that if you forget to change to the right database in management studio, your scripts will run in TempDB instead of Master. So if you create a bunch of tables in TempDB, no big deal because they will clean themselves up.
No comments:
Post a Comment