TempDB is a very popular resource, which is used inside the SQL server. You can consider TempDB as a dump yard for anything which does not fit into the memory. It is a nondurable memory, which means that TempDB is recreated when the SQL services restart. New log files and new data sets are recreated each time in TempDB. So, it means that you should not put any objects in TempDB which you need persistently. You cannot use temp TempDB as your development database, except you consider it for a quick temporary test. Once your services restart, you may lose everything stored on TempDB, and it starts fresh again.
TempDB tends to work ok as a proportional fill, i.e., a round-robin style approach. It writes the input data in all the files based on the space available in each file and then alternates between the files to find the best place to do the work. So TempDB is like any other user database except for logging and durability. A transactional log is maintained on TempDB, but the log is used only to rollback data and not for recovery. TempDB is also known as nondurable as in typical use cases. It does not need recovery as the new one gets created each time.
TempDB also acts to be low-latency fast storage. It features a hike in concurrency rate in terms of transactions, which will write data onto the TempDB. It happens so all the time, so the users need to ensure that it is pinned to the fastest possible disk. As the TempDB best practices, you should also isolate the data files and log files on the corresponding disks.
Storage in TempDB
While considering what to store in the TempDB, you must note that when you are writing codes, it will cause the data to take up some space in the DB. While determining the types of operations, you have to execute, consider what sort of operations are needed. TempDB is similar to an operating system that pages memory to the given disk space. Feeding to the disc means that there is not enough memory granted for the particular operation, and it is forced to spill to TempDB to execute. Let us have a quick look at what is stored at TempDB.
- Local or global temporary indexes and tables.
- Stored procedures (temporary).
- Table variables
- Table value functions
- Tables used in the cursors.
Availability group replica tends to use snapshot isolation, which uses the row version of each transaction. These transactions are usually stored in the version store inside TempDB. They tend to be stored on the secondary replicas to avoid any blocking and to prevent the transactions from being applied at the first point. Blocking can be an issue for the long-running transactions, which may lead to the version store cleanup process being blocked and may cause your TempDB to fill up quickly.
If this issue happens, then you may not be able to failover. Make sure that you are fully aware of how these all work and how to manage them well. While using read only replica secondaries, the query optimizer may also create some temporary shards within TempDB. These shards from the primary database are then replicated to secondary. In SQL Server’s case, it also needs statistics on read-only that are based on the queries that run on secondary. Query Optimizer may use these statistics to optimize the workload on the secondary.
Now, as you understand the nature of issues in TempDB, here are some tips to consider while you try to fix these issues.
To take advantage of the previously discussed round-robin processing approach of TempDB, you must have multiple data files to spread the workload proportionally across all. To ensure that it happens this way, one must ensure these files are of equal sizes, and the auto-growth rates are also the same. If you have a file that is larger than the others, you may end up with contention. The system tends to place all workload on the biggest file by thinking that it has more free space. When you are putting in a new file during restart, the engine will end up using the new file only as it has the largest size of free space. You may have to reset TempDB with a restart to ensure the proportional file usage is properly maintained. You should also make it a routine practice by checking TempDB regularly to ensure that all these are well aligned.
Adding more files
Along with enabling proper usage of the round-robin logic, you may also think of adding more files, which will give you more special pages to work with. With the addition of each data file, you get 4GB of additional space allocated. You may also need to monitor your TempDB usage and the growth rates as a starting point. Another important thing you need to do is keep an eye on the virtual log file, which is also an indicator of these events. The latest version of the SQL server will help you simplify all these during the installation itself. This will help you determine the number of files you may need and create them as part of the setup process itself.
The processing of TempDB happens on the actual disk memory for most of the operations. Since TempDB is very concurrent and used so much inside the engine, it is important to ensure that it is placed on the fastest possible disks. You may use flash drives also if possible. These are usually SSD solid-state drives. You should always remember that NVMe’s are SSDs, but all the SSDs are not NVMe’s. There are various types of SSDs. Irrespective of what enterprise-grade SSD you use, all these can be ideal for handling the TempDB workloads.