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!