How to configure tempdb in SQL Server
Articles,  Blog

How to configure tempdb in SQL Server


Hi, everyone! Today I’m going to talk about configuring
tempdb in a SQL Server instance. If you’re not already familiar with what tempdb
is, it’s a system database that’s designed to be a scratch area for temporary bits of
information. Many applications use tempdb, either explicitly by creating temporary tables,
or implicitly by using features such as triggers or snapshot isolation. There’s only one tempdb per SQL Server instance,
so it’s important to realize that your specific configuration of tempdb will be based on your
specific workload. In this video, I’m going to give you some general guidelines as to
where to start. When configuring tempdb, there are three main
aspects to consider. First, is the number of data files. By default,
tempdb is configured with only a single data file, and it’s often a good idea to create
more of them. Before I make any recommendations, I’m going to take a step back and explain
why this makes a difference. In every database data file, including data
files in tempdb, there are special metadata pages that track whether space is available
or used, among other things. As an example, let’s say we run an INSERT
query that targets a temporary table. This query will need to allocate space in tempdb,
and that means updating the metadata pages to say where the data will live, so it can
be found later. The metadata pages are shared resources that
could be written to by multiple threads simultaneously, and remember that even a single query can
span multiple threads if it uses parallelism. To protect the consistency of an internal
resource that’s accessible by multiple threads, SQL Server serializes access by using a lightweight
synchronization object called a latch. When multiple threads are all asking for new space
to be allocated, the latch requests can get queued up because only one thread can modify
the same page at the same time. Because a set of metadata pages exists in
every data file, we can add more data files to spread out the requests. While the latch
queues still exist, there are now more of them, and they’re all processing requests
concurrently. This reduces the amount of time a thread spends waiting in the queue, and
the less time it spends waiting, the faster it can get its work done overall. How can we tell if this queue waiting is a
problem? SQL Server keeps track of what each process
is waiting for, and we can see this information in sys.dm_os_waiting_tasks. There are many
different wait types, but the ones we’re interested in here are the PAGELATCH waits, which you
see below, and I’ve put a link in the description to a query that you can run to see them. If
you think there’s a problem, you can dig into the waits by using the DMV sys.dm_os_wait_stats.
It’s best to isolate problematic queries if you can, because these DMVs operate at the
server level. You can use the DBCC SQLPERF command to reset the wait stats in between
runs, but be careful doing this in production as there may be processes set up to record
the stats. Once you’ve identified there’s a problem,
how many data files should be added? As I mentioned before, this is a very situational
question, because the use of tempdb varies based on your system workload. Because the
latch queuing system gets clogged up the more active threads there are, the number
of data files you need is related to the number of query schedulers in the SQL Server instance.
The number of schedulers will usually be equal to the number of logical processors in the
system. The general guideline for the number of data files to create is: between one quarter
and one half of the number of schedulers, up to a maximum of eight files. Your system
may need more or less files than that, but this is just a general guideline to get you
started. It’s important to note that for this to work
properly, all the data files need to be the same size. SQL Server uses a proportional
fill algorithm that allocates more frequently from data files that have more empty space.
So, to make sure that all the allocations are evenly distributed among the data files,
the data files have to be the same size. At this point, you’re probably wondering why
I don’t just recommend adding a hundred files and being done with it. The proportional fill
algorithm recomputes the weightings every 8,000 or so allocations, and as the number
of data files increases, so does the amount of CPU required do this computation. This
can directly impact performance, so yes, there is a such thing as too many data files. Again,
though, everything depends on the workload, so you’ll just have to test and adjust. As I mentioned before, the metadata pages
responsible for tracking allocations in data files exist in all data files, not just in
tempdb data files. Allocation contention will usually only show up in tempdb, though, because
it’s a shared database within the whole instance of SQL Server. You can apply these same principals
to user databases as well, but the number of data files should probably be more conservative. The second aspect of configuring tempdb is
the size of the files. If the server has been running long enough,
all the tempdb files have probably expanded themselves to a maximum size needed by the
business cycle. For data files, take the current total size and divide it equally among the
number of files you’re going to use. The log file can be left at the current size. The two major consumers of space in tempdb
are usually temporary tables used as data loading targets, and index maintenance operations
that use the SORT_IN_TEMPDB option. The total data file size should be at least the size
of the largest of these operations. That said, some caution is necessary when
configuring the size of the files. Data in tempdb is temporary, and when SQL Server restarts,
it recreates tempdb from scratch, which means going through the allocation process for all
of the files. If the files are very large, this could add a significant amount of time
to an instance restart, and that may affect your Service Level Agreements. One way to
help with that is to enable Instant File Initialization, which will skip part of the allocation process
for data files. You can watch how to enable that feature by clicking the annotation right
here. One thing I do recommend in general, is setting
a relatively aggressive fixed growth size on all the data files, especially if Instant
File Initialization is enabled. The data files should have the same growth rate, so the file
sizes stay roughly the same for the reasons I mentioned earlier. Ideally, the files should
never be expanding to service a user operation, but if they do, you want them to expand enough
that the next operation won’t need to expand them again. That said, the expansion process
shouldn’t take an excessive amount of time. For this reason, I don’t encourage using a
percentage-based growth rate for any of the files. Finally, the third aspect is to consider whether
the tempdb files should be placed on a separate physical device than the rest of the databases.
This would be done to improve the performance of physical disk operations. It’s a best-practice to use a separate storage
location for tempdb. If using a separate physical device is necessary in the future, doing it
this way allows you to reuse the storage location, even if it’s backed by a different physical
device. If your instance of SQL Server uses tempdb very heavily, it can be a real win
to separate the files onto a separate storage device. That said, it’s a more advanced thing
to do, because it does increase the complexity of managing the server. If you found this video helpful, please click
the Like button, and subscribe to the channel to see more videos in the future. Thanks for watching!

15 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *