[MUSIC]>>Hi. I’m Pam, and I’m a Program Manager with the SQL Server Engineering team
in the storage engine area. Today in this video, I’d like to talk to you a little bit about some of the
improvements that are coming in SQL Server 2019
for TempDB performance. So let’s start with a little
bit of a background on TempDB. What is it? It’s essentially just a database like any of
your other user databases. The difference with TempDB is
it’s used for temporary storage. So that means it’s not durable. We recreate it every time
the server is started, and we’re able to do things like
minimally logged transactions, because we don’t have to
worry about recovery. The workload in TempDB however is different than most
of your user databases. Because it’s used for
temporary storage, objects and data are frequently
being created and destroyed, and there’s very high concurrency. There’s only one TempDB
database on your servers. So even if you have
multiple databases and many different applications
connecting to the server, they’re all going to be
connecting to the same TempDB. So you generally are going to have many threads concurrently
creating in string objects, and that can cause some
contention points. Unfortunately, or fortunately,
TempDB is critical to performance. So it’s generally used for
data that can’t fit in memory. So in the process of a query, we may need temporary storage
for things like hash operations, or sort operations, and all of
that ends up spilling to TempDB. So it can be directly felt by your applications as
they’re executing queries. So it’s very important
for TempDB to be fast. Even if you’re not using
temporary tables explicitly, chances are you’re using TempDB. So it’s of course used for temp
tables and table variables, but it’s also used for things
like the row version store for snapshot isolation,
for hash worktables, for query processing, for spools, also in query processing,
for triggers, for cursors, sorts,
online index operations, CHECKDB, table-valued functions,
even statistics updates. There are many
different operations in SQL Server that use TempDB
for temporary storage. So because of this, many users face contention in TempDB, and that generally breaks down
into three different categories. The first is object
allocation contention, which is what happens
when we’re allocating new space for objects in TempDB, or deallocating space from an object. This shows up as PAGELATCH
waits on PFS and GAM pages. These are special pages
that are used inside the database file to
track space allocation. So you would see different threads waiting on a PAGELATCH wait type, with a weight resource
of a PFS or a GAM page. The next is metadata contention, and this happens whenever we are creating or
destroying temp tables, and we’re are modifying the metadata that’s
associated with those tables. So what you’re seeing here is
PAGELATCH contention again, but this time the pages
belong to system objects. Tables like sis-obj
values, for example. This is where we are storing the metadata for all
the objects in TempDB. Then the last type of contention
which is relatively new, we’re just starting to see this now, is Temp Table Cache contention. So we do cache temp
table objects to try to help with both metadata and
object allocation contention, but as servers get larger, we get a lot more CPU and a lot
more memory in these servers, we start to now see contention on the memory objects that support
that temp table cache. So in this case, you might be seeing CMEMTHREAD waits, or SOS_CACHESTORE spinlock waits
during the course of execution. Of course, those waits can be
due to many different things, but in this case, they would be
related to TempDB workloads. So there are a few
things you can do today to help alleviate some
of that contention. The first thing is to
just make sure that TempDB is configured properly. So you want to have multiple
equally sized files, and if you’re in SQL
Server 2014 or earlier, you want to make sure that
you have Traced Flags 1117 and 1118 enabled. You can also make some
changes to your code to help encourage temp table caching, which can again get around
some of that contention. So the main thing here is to not alter a temp table after
it’s been created. Anytime you alter a temp table
after it’s been created, we can’t use the cache, and so we end up having to do a
lot more creating and dropping. So we don’t want to for example create an index after
the table has been created, because that’s an alter, and that’s going to cause
the cache to not be reused. The other thing is to avoid using
ad-hoc batches with temp tables. If you’re going to use temp tables, using stored procedures is better, because then we can nearly
guarantee cache reuse. Then the last is the version
of SQL server you’re running. So first of all in 2016 or later, we automatically
configured TempDB for you. So you don’t have to worry about
manually creating multiple files, and manually turning
on those trace flags. But also in various service
packs and cumulative updates, in 2016 and 2017, we’ve released improvements
that help minimize some of these metadata contention and
object allocation contention. So you want to make
sure you’re running the latest cumulative update to take advantage of all
those improvements. Now, if after all of that you’re still seeing contention
against TempDB, then you want to start
looking at SQL Server 2019, because we are adding a lot to
help with this TempDB performance. So let’s talk about SQL Server 2019. So by default, out of the box, there are two types of
improvements that you will see that will impact
TempDB performance. The first is some temp
table cache improvements. We’ve done things like
partitioning cache objects, and optimizing cache lookup, in order to get around some of
that temp table cache contention. Another one is
concurrent PFS updates. So this will address object
allocation contention. So this is something that allows
us to have multiple threads, share a latch on the PFS page, and therefore we can have more concurrent threads and less contention on those
object allocation pages. Then the last one is
an opt-in feature, and this is memory
optimized metadata table. So what we’re doing here is
moving those system objects into memory optimized tables that have latch free lock-free structures. This greatly increases
the concurrency that we can have against
those metadata tables, and that helps us alleviate
that metadata contention. So just to summarize, why is my TempDB causing me pain? Well, first of all it’s used
for everything in your server. There’s all stuff that’s
dumped into TempDB. So even if you’re not
using temp tables, chances are you’ve got
a lot of TempDB access. We’ve got three different types of contention that we’re dealing with, especially in larger servers with
highly concurrent applications. So what are we doing about it? SQL Server 2019. We are introducing temp
table caching improvements, concurrent PFS updates, and
memory-optimized TempDB metadata, in order to help you improve
your TempDB scalability. So start evaluating
SQL Server 2019 today. See if you can improve performance
of your TempDB heavy workloads, and also stay tuned for a
future video where we’ll demo some of these
contention and issues, and how to detect them, as well as how to turn on and use the new memory optimized TempDB metadata
feature. Thanks very much. [MUSIC]