Up next in 10
Tempdb, everyone needs it but also wonder how to get most of it! Tempdb is the most important database in SQL Server Engine but less talked about. Knowing tempdb internals and to properly configure tempdb is crucial to avoid the serious implications in your application performance.
In this session, we will dive deep into the internals of tempdb database as it is very crucial and important for the performance of your applications. We will learn about the Best practices of tempdb and the latest improvements in SQL Server 2019. By the end of this session, you will gain the practical knowledge that can help you avoid tempdb problems and maximize performance of tempdb.
Conference Website: https://globaltechconferences.com/event/women-data-summit-2021/
C# Corner - Community of Software and Data Developers: https://www.c-sharpcorner.com
C# Live - Dev Streaming Destination: https://csharp.live
#Azure #Data #WomenDataSummit #Tempdb
Show More Show Less View Video Transcript
0:00
Hello everyone. Thank you so much for taking your valid time for attending the summit today
0:07
So today I'm going to talk about TempDB. So the best practices of TempDB, some of the
0:12
contention issues we have and how do we solve them. So let's get started. A little bit about
0:18
me. As I mentioned, I'm a database administrator and I'm a Microsoft certified trainer and
0:23
Microsoft certified solution expert. And I do blog for dbnuggets.com and I did my master's in
0:29
Computer Technology at Eastern Illinois University. And you can contact me on Twitter
0:34
at dbNugget, and below that's my email address. If you have any questions regarding this session
0:39
later, please do not hesitate to contact me. So what are we going to discuss in this session today
0:46
We are going to talk about the tempdb in an overview, and we are going to see how the
0:50
transaction log happens in the tempdb, and what objects do we store inside tempdb
0:56
what are the different kinds of objects. And we have a contention issue with the TempDB database
1:04
So what is that contention? What are the different types of contention
1:08
And how do we solve that contention? And at the end, we are going to see some of the recommendations
1:13
like best practices for the TempDB. So as you know, TempDB is a system database
1:20
and it is basically used for storing the temporary information, Any intermediate results that we would like to store
1:28
we are going to store in the TempDB. TempDB is basically a simple recovery database
1:34
and it is created every time we restart the SQL server. We do not want the data to be persisted
1:42
It is temporary. All the information within the TempDB is temporary. Whenever we restart, TempDB database will be created again
1:51
Only one day tempdb per instance we have. And all of like it is highly concurrent, like everybody wants to use it, right
2:00
All of the user databases want to use tempdb for storing their temporary information
2:05
And tempdb, you know, you need to make sure you configure it correctly based upon your workloads
2:12
So once we restart the SQL server, right, what happens and how the tempdb gets created
2:18
Once we restart the SQL server, the first thing that happens is the master database will be open
2:23
Once the master database is open, we all have the login information, user information inside the master database
2:30
So that's when the connections are made to the server. And connections are at this point, at this point will be allowed to the SQL server, but they cannot access the model database and tempdb database until they are created
2:43
So model database and tempdb database will be logged until the tempdb gets created
2:49
So tempdb, it gets created based upon the model database. So if you have any, you know, if you have any tables that you accidentally created in the model database, those will be also carried away to tempdb
3:02
So if at all your tempdb is not creating very fast, it is taking some time
3:07
just go ahead and look into model database like if you have any user database user tables into the
3:13
model database so basically tempdb gets the copy of the model database just like the regular other
3:19
user database how they create from the model database tempdb also gets created the same way
3:24
so while see while while this tempdb gets creating right if you go ahead and see the error log the
3:32
first thing that happens is it will open up the once it open up the master database it will go
3:37
ahead and open up the model database and then model database and tempdb database won't be available
3:43
but the connections are still open until at this point of time they can connect but they cannot
3:48
access the tempdb because it's not yet created and once once the model database is open the tempdb
3:55
go ahead and get created based upon the model database that's mean that means like it will go
4:01
ahead and create the primary data file and then it will copy over any extents, any page, like the
4:07
page extents to the tempdb database and then it will go ahead and create the log file at the step
4:16
number two and if you see the step number three, it says starting up the database tempdb. At that
4:22
particular stage, all the other NDF files, if you have any additional data files for the tempdb
4:28
those get created and the tempdb will start up. At that point of time, all of the connections
4:35
and any users who wanted to create the tempdb objects, they can be able to use the tempdb
4:41
But until the tempdb gets created, even though the user wanted to create any tempdb
4:47
they will be placed in hold. Like the queries doesn't fail, but they will be on hold until the tempdb gets ready
4:55
so what about the tempdb transaction log as we as i told you like tempdb is a system like simple
5:03
database right simple recovery model database it is minimally logged um especially because we do not
5:10
want to we do not want any data data to be persisted on tempdb once we restart it so and also there
5:18
are no checkpoints like whenever the checkpoint process kick in for every couple of minutes by the
5:24
SQL server automatically, it will go ahead and do the checkpoint process on all of the user
5:29
databases except the TempDB. It will leave the TempDB. But then if you wanted to manually go ahead
5:35
and do the checkpoint process, like go ahead and open up the management studio and run the
5:40
checkpoint process and execute it, that's when checkpoint process will also run the checkpoint
5:46
process on TempDB database. And that might take a while because that might take some time to do
5:53
the checkpoint process if you do that manually. Now, why is that? Because the general checkpoint
5:58
process doesn't touch that MDB at all, right? But then the checkpoint process will actually
6:03
truncate the transaction log whenever it runs, just like for the other simple databases
6:10
simple recovery mode databases. And the lazy writes happens like those whenever any pages
6:18
from the buffer pool are not get used. Those pages, like the least recent use pages will be
6:24
you know, they will be pushed back to the data files just for like the regular process
6:30
The lazy writer will happen on the TempDB database as well. So what are some of the uses of TempDB, right
6:38
What do we store inside TempDB? We store temporary tables, table variables
6:43
Table variables, when we talk about, like most of the people think they are stored inside the memory but basically them table
6:52
variables get stored inside the tempdb and for the index operations right um you have to go ahead
7:00
and choose where do you want to sort those um sort those indexes sort sort indexes in tempdb if you
7:06
specify it exactly like sort sort sort sort the indexes in tempdb that's when the sorting happens
7:14
inside tempdb. Like if you do not mention anything, then the sorting going to happen inside the
7:19
user database where those index operations are happening. And if you, you know, the statistics
7:26
also use the tempdb while the update stats happens if you'll use the tempdb. Like if you have triggers
7:33
if you have any triggers behind the scenes, the snapshot isolation will be enabled and the version
7:41
and store gets enabled inside the TempDB And for the CheckDB process you will have those snapshots of the database that gets created before running the check DB Those snapshots are saved inside the temp DB as well
7:57
Cursors also use temp DB. These are some of the temp DB users where
8:02
these all are the objects which get stored inside the temp DB
8:06
You can divide the objects inside the temp DB as user objects
8:12
that we create and the internal objects that we do not specify to create
8:18
Like if you see here, the internal objects, we have SARTs, Work Tables, Work Files, Version Store
8:24
These, we do not specify them to go ahead and create, but they do create behind the scenes for our queries
8:33
like inside our execution plans, right? The SARTs and then Work Tables
8:37
And if you use any triggers, those versions don't get enabled. those are the internal objects
8:43
We do not manually go ahead and create. But when you see the user objects
8:47
we do create those. We'll go ahead and create the temporary tables
8:52
table variables. There are certain type of temporary store procedures. They are like regular store procedure
8:58
but then the text behind it will be stored inside the tempDV. And we do not want to persist them
9:04
If you manually go ahead and create any objects, if you wanted to create any user objects
9:10
like user tables inside the tempdv, you can do that, but then the data won't be persisted
9:18
So there are a couple of DMVs that we can use to see how these objects are getting used
9:27
how many of these user objects or internal objects are getting created
9:31
There are a couple of DMVs that we can use, especially like sysdmdb file space usage
9:37
sysdmdb session space usage, and sysdmdb task space usage. These are based upon session, file, and the space
9:48
So let's talk about the contention. Since the SQL Server 2005, we do have contention issues
9:57
and Microsoft has been working on these contention issues until 2019. So let's talk about what different kinds of contention problems
10:06
problems we have and how we can effectively solve them. One of the type is object allocation contention
10:15
This type of contention is basically on the system pages. Like if you have any data files, right
10:24
If you have the data files for any of the databases, like user databases or tempDB databases
10:31
all of these databases have data files in them. So each data file have some of the system pages like IAM page, GAM page, S-GAM page, right
10:43
Like these are the system pages where they will capture the information of like, if you are going to create any, if you wanted to create any temp table, right
10:55
you need to go ahead and update in some pages like GAM page, SCAM page
11:03
to get those extends from those global allocation map pages. You need to go ahead and write these, how many extends do you need
11:14
how many pages you need to get those temp tables that get created
11:19
So, you know, tempdb, everybody is going to use the tempdb. all of the user databases want to create tempdp
11:26
So if multiple users and applications, if they have a process of creating
11:31
these tempdtable objects continuously, then all of those requests have to go ahead
11:37
and update that information to grab those extents or pages they need from these system pages, right
11:43
Because these system pages are the pages which will have the information
11:48
of how many extents were allocated or how many extents were not allocated and free
11:54
they have to go ahead and update them and grab that extent they need
11:58
before creating any objects. So once everybody starts using it at the same time
12:04
they have the contention on those pages. So for example, we have a data file
12:09
Each data file have these IAM page, GAM pages, GAM pages, right
12:14
So let's say like these are the metadata pages, like GAM pages, GAM page
12:18
and let's say there is a process that there are multiple threads coming along the way at the same
12:24
time and only one thread like only one request can update that IAM page or CAM page to get that
12:32
allocation extent they need. Only one request can update those pages at a time. So multiple
12:38
multiple requests cannot update those pages at a time because while the request update the page
12:43
those pages are locked actually, exclusively locked. So what happens is if we have just one data file
12:51
all of this request will be sit in the Latch Queue. Latch Queue is a queue where SQL Server will create
12:58
so that the request can go in the serializable process. No matter how many threads we have, they sit in the Latch Queue
13:06
One request can update that IAM page or GAM page at a single time
13:12
multiple requests cannot update them. So that is a problem. That's when the contention happens
13:19
All of these requests have to wait until they get their turn. So which is like all of these requests have to wait for a long time
13:27
which will cause the performance issues. So this is called as an object allocation contention
13:34
And if you can go ahead and create multiple data files, the same multiple data files
13:39
each data file have these system tables, right IAM cam and scamp pages so now if you have multiple data files right you can easily solve
13:49
that contention like object allocation contention problem because now these requests can easily
13:55
easily you know pass on those burden to the other they can share the resources they can share these
14:01
pages from the different files so that they don't have to sit in the large queue waits for a longer
14:07
time and the process will run very quick without any waiting. So that's how by creating multiple data files
14:15
you can easily solve the object allocation contention problem. And there are a couple of more advancements that have been made that we need to remember
14:27
If you are below SQL Server 2014, you need to make sure you enable trace flags 1117 and 1118
14:37
Now, why is that? Because trace flag 1117 will actually make sure that if you have multiple data files, they all auto grow at the same time
14:47
Like if you wanted to increase the space, if the auto grow happens on one data file, that will trigger rest of the data files to also auto grow
14:55
Now, why is that? Because we wanted to make sure that all of those data files are being used simultaneously
15:03
Instead of going all the requests to one data file, we want to distribute that workload onto all of those data files equally
15:13
So that is the reason we need to make sure we enable 1117. And also we need to make sure we enable 1118
15:20
So that the extents, like instead of using the mixed extents, we are making sure we can only force it to make sure we only use uniform extents only
15:32
Now why is that Because if we are using any objects right if we need more and more allocations of the pages it is better to just allocate the uniform extent
15:44
because each extent has eight pages in it. That way we do not have to go ahead and update those
15:51
IAM page and GAM pages multiple times. So that way we can reduce the contention
15:58
So if you are a BoSQL server 2014, you will have the ability like these trace flags are automatically enabled by default
16:07
So you don't have to worry about these trace flags. So and also there are a couple of more enhancements that has made
16:17
So the allocation, they made sure that allocation is not only happening on one
16:24
like there are multiple of multiple IAM pages, PFS page, and then GAM pages, GAM page on
16:31
in a single file, right? In a single data file, we will be having so many
16:36
We might be having so many these system pages. So by making a certain enhancement
16:43
what they did is they tried to make the request, go ahead and allocate for each of those PFS page
16:50
or GAM page, SCAM page equally instead of just allocating for the one file at a time
16:59
and then going to the next file, they made sure that allocation happens across all of the PFS page
17:08
or CAM page of all of those pages in all of the files so that it can go ahead
17:14
And, you know, it's like the round robin algorithm where all of those extensions
17:21
like all of those allocations get equally distributed within the files as well
17:25
because each file have those multiple system files, like IAM, GAMPages, GAMPage
17:36
So this enhancement makes sure the allocation happens across all the set of these pages, across all the files
17:48
So there is one more type of contention known as metadata contention
17:52
this type of contention doesn't only happen on temp table, temp database, but also it can happen
18:02
in all of the user databases because whenever we create any objects, right, even in the user
18:08
databases or even in the temp table, we need to go ahead and update that we have created any object
18:15
inside certain metadata objects. Those are the system objects that we have to go ahead and
18:22
insert those values whenever we make modifications to those objects like how many like what all the
18:30
columns we have what are the data types we have we we will go ahead and update that information on
18:37
these metadata tables so as i told you like tempdb is used by many users at the same time now all of
18:46
these requests come and wanted to update that information write that metadata information of
18:51
of those temp tables at the same time on these metadata pages
18:56
metadata tables inside the temp DB. So that's when the contention happen
19:01
at metadata tables within the temp DB. It does happen in the user databases
19:07
but in the user databases, once we create the tables, we put those tables, right
19:13
We do not delete them so frequently. So the metadata contention happens mostly on the temp table
19:20
because we'll go ahead and create and then delete those objects very vigorously
19:25
So this particular contention is caused on the metadata tables within the tempdb
19:34
and happens during the tempdb objects creation. Like I said, we have to go ahead and insert the information
19:42
like what all data types we have and the other metadata information of those temp tables
19:47
So it can be slightly solved. It has been slightly solved once the temp table
19:56
once we cache these temp tables, like instead of going ahead and deleting
20:01
that whenever we, whenever inside our store procedures, like let's say an example
20:06
inside our store procedure, once we create the temp table, we'll just go ahead and later drop it
20:11
So what the change they have made is instead of going ahead and dropping the temp table
20:16
completely. Why won't we just go ahead and drop the data within the temp table, but then save the
20:24
schema of the temp table in the cache? That way, the cached objects, the metadata cache objects
20:31
are still in the memory. We do not have to go ahead and manually, every time we go ahead and
20:37
wanted to create a temp table, we don't have to go ahead and insert that metadata information
20:42
within the temp table, but then go ahead and use the already existed temp table metadata in the
20:49
cache. That way we can reduce some of the contention. But what happened is once they have done that
20:56
once they have saved that metadata of the tables inside the cache, we do also have to maintain
21:04
like deletes, right? We do also the cleanup process should also delete those metadata table
21:11
over time because now we are saving it in the cache. The cleanup process will automatically
21:17
kick in based upon the usage. It will go ahead and drop it. So whenever we have to delete that
21:22
metadata, we also have the contention on the deletes because even the deletes, we have to go
21:28
ahead and delete the metadata information on those metadata tables within the tempDB. So even that
21:36
has caused a problem with the deletes as well. So what they did is
21:43
so what they did is like instead of, like because while the deletes happens
21:51
these metadata tables, these metadata tables within the tempdb, there is a contention on those while there are deletes
22:00
because those metadata tables are completely exclusively locked while those deletes are happening at the metadata tables
22:09
So they have changed the algorithm in SQL Server 2016. I would like to thank Pam Lahoud for loaning me this algorithm from Microsoft
22:20
So they have made the algorithm change like the old algorithm used to do
22:25
like whenever they wanted to delete the metadata information from the TempDB
22:31
They used to acquire the page latch exclusive weights. on those metadata tables and then scan for the metadata rows what they wanted to delete
22:41
Once they find the rows, they used to delete the metadata rows completely
22:45
But if they did not find any metadata information to delete, they used to delete that page latch exclusive way
22:51
So until the entire process, the page latch exclusive lock was acquired during this delete process
22:59
But if you see the latest algorithm that they have changed in SQL Server 2016
23:05
instead of putting like exclusive log, they just went ahead and first put the shared log
23:10
so that the other processes also can go ahead and do their own process of deletion
23:15
for the other attempt table objects. So they have just acquired the page latch shared log
23:21
and then go ahead and scan the metadata rows If they find the metadata rows then they went ahead and promote that shared like shared a page latch shared log to the exclusive log
23:33
and then delete the rows, and then release the exclusive log. Like if they did not find any rows
23:38
then just release that shared log. This, what happens is this particular new algorithm
23:44
will actually solve some more metadata contention problem because simultaneously other processes can also go ahead and work on those deletion process
23:54
because it's just shared log, page latch shared log. So this particular contention has actually solved some of the contention on the metadata tables
24:08
So they have introduced the same algorithm for the latest versions of SQL Server
24:13
Like if you see in between, they have introduced on SQL Server 2017, CU7, 2016, SP1, CU9, and 2016, SP2, CU1 as well
24:25
But then there are a couple of more things that we have to remember is, even though that, like, they have done a couple of more advancements
24:34
like once we have cached these temp table metadata that we cached inside the temp table
24:43
inside the cache, right? We do not want to delete any of those temp table objects
24:48
Once we delete inside our queries, the metadata is still there inside the cache
24:54
So they have introduced like a cleanup process, like a particular thread
25:00
a specified thread for every new manor. they have made sure that is especially a helper thread where it can go ahead
25:08
And whenever we need to have the cleanup process, we can go ahead, that process will actually go ahead and delete those cached objects
25:18
and go ahead and find out the metadata information in the metadata tables
25:24
and go ahead and do that. That helper thread is especially for deleting the metadata information
25:32
of those cached tables. There is also, like, once they did that
25:41
there is also, like, so as I told you, that there is a contention on the temp table cache objects, right
25:50
So that is how we solve the problem. Like, the helper thread will go ahead
25:54
and continuously look for the temp table objects, and that is specifically for clearing out
26:03
all of the metadata information of the temp table cached objects. So in SQL Server 2019
26:13
though they have a cause, though they have placed the temp table
26:18
like a helper thread to delete those metadata contention, they thought why not use the in-memory OLTP
26:26
like in-memory optimized OLTP, there is that option, right? In-memory OLTP of caching those object in the memory
26:35
So they thought why not use that feature for caching out these metadata objects itself
26:41
so that once they cached out all of these metadata objects inside the cache
26:46
we do not have to worry about going ahead and deleting them on the disk, right
26:50
So everything can happen inside the memory. So they have thought of caching out those metadata object
26:58
metadata tables inside the cache. That's basically solved all of those latching problems
27:07
that we have, like all of those exclusive log, shared log that we have
27:12
it have solved that problem most of the time. There are a couple of recommendations that I have said
27:21
right, from the beginning we have seen like what can actually solve our problem
27:27
from the contention issues. So basically, previously, like in the older versions of SQL Server
27:34
the default is like creating only one data file per temp table
27:39
but then for the latest version of SQL Server, the recommendations were
27:43
and for the latest versions of SQL Server, they have automatically made as a default
27:48
that we have multiple equally sized temp table data files. So you can start with one file per core
27:57
and then you can go up to Yate. And some of the companies they even need
28:03
based upon the workload, right? You can just start with one file per core
28:07
but then go up to Yate. But that's the starting point. It all depends upon how much of workload you have
28:13
how many of your threads will use tempdb and create the objects inside tempdb
28:22
And we need to make sure we enable trace flag 1117 and 1118 if you are below SQL Server 2014
28:31
but if you are above SQL Server 2014 and later versions of SQL Server
28:36
you already have those trace flags enabled. And a couple of other recommendations are like, if you create a temporary table inside
28:51
your stored procedures or inside your queries, please make sure that you do not create the
28:59
once you create the temp table, right, in between your process, like in between your
29:03
stored procedures, do not go ahead and create the indexes on those temp tables that you
29:08
already created because what will happen is that will actually invalidate those cached
29:15
metadata table that you stored inside the cache. So for the latest versions of SQL Server, you have that inline syntax where you can
29:24
go ahead and create the temp table at the same time, create the index within that create
29:29
temp table statement. So by that, you will not invalidate the cached object inside your cache
29:36
and avoid using temp tables inside your ad hoc patches because caching of these temp table objects
29:46
are directly related to your plan IDs. So for the ad hoc queries
29:53
we do not save these temp tables inside your cache. And please do not truncate the temp tables directly
30:02
because if you truncate, it will also invalidate the metadata cached object inside the cache
30:10
As I told you, for the lower versions of SQL Server, it's only one file. You need to make sure you go ahead and divide that one file
30:17
add multiple files so that you will solve that object location contention
30:25
For the newer versions of SQL Server, it is as default the multiple database files
30:30
and i would like to especially thank bob ward and pam lahoud for their support in preparing
30:37
my presentation uh if you wanted to know more about like mtb right especially there is like
30:44
three hour session that the bob ward gave a couple of years ago for past summit it's a very
30:49
it's an awesome session and pam lahoud also given a wonderful session for y8kb if you want to know
30:56
more about deep internal stuff on TempDB, please follow these references. These are very useful
31:03
And I would like to thank you
#Data Management