[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]