WEBVTT

00:00:00.000 --> 00:00:10.500
[MUSIC].

00:00:10.500 --> 00:00:12.270
>> Hi my name is Pam,

00:00:12.270 --> 00:00:15.495
and I'm a Program Manager with
the SQL Server Engineering team.

00:00:15.495 --> 00:00:17.790
Today I'd like to do
a quick demo for you

00:00:17.790 --> 00:00:19.800
on one of the new
features of SQL Server.

00:00:19.800 --> 00:00:23.310
2019 Memory Optimized
TempDB metadata.

00:00:23.310 --> 00:00:26.070
I've already done an
overview video on

00:00:26.070 --> 00:00:27.480
this feature where
I talk a little bit

00:00:27.480 --> 00:00:29.040
about some of the challenges with

00:00:29.040 --> 00:00:32.295
TempDB performance that you
may have faced in the past and

00:00:32.295 --> 00:00:35.850
about the work we're doing in 2019
to improve TempDB performance.

00:00:35.850 --> 00:00:38.945
So I encourage you to watch that
video if you haven't seen it yet.

00:00:38.945 --> 00:00:41.600
What we'll be doing
in this demo today is

00:00:41.600 --> 00:00:45.185
focusing on the memory optimized
TempDB metadata feature,

00:00:45.185 --> 00:00:46.805
how you enable it,

00:00:46.805 --> 00:00:47.975
how you would disable it.

00:00:47.975 --> 00:00:49.640
But also, how can you

00:00:49.640 --> 00:00:51.790
tell whether you need to
turn this feature on.

00:00:51.790 --> 00:00:55.600
Are you having the problem that
this feature is designed to solve?

00:00:55.600 --> 00:00:58.770
So let's jump into the
demo and take a look.

00:01:00.220 --> 00:01:02.960
So I've got a notebook open here in

00:01:02.960 --> 00:01:05.420
Azure Data Studio
with a few commands.

00:01:05.420 --> 00:01:09.050
What we'll start with is running
the workload on SQL Server.

00:01:09.050 --> 00:01:14.315
2019 without enabling the Memory
Optimized TempDB metadata feature

00:01:14.315 --> 00:01:15.560
and we'll try to take a look at

00:01:15.560 --> 00:01:17.930
this contention that
can happen in TempDB.

00:01:17.930 --> 00:01:21.050
So the first thing I'm
going to do is start

00:01:21.050 --> 00:01:24.170
a performance monitor
collection and collect

00:01:24.170 --> 00:01:26.120
a few different
counters that will give

00:01:26.120 --> 00:01:28.430
us an idea of the
performance of the workload.

00:01:28.430 --> 00:01:31.955
Then I'm going to use
the O stress tool

00:01:31.955 --> 00:01:34.415
to run a multithreaded workload.

00:01:34.415 --> 00:01:37.700
So I've got eight processors
on this particular machine,

00:01:37.700 --> 00:01:39.950
but I'm throwing 100
concurrent threads.

00:01:39.950 --> 00:01:42.350
So I've got a very busy workload

00:01:42.350 --> 00:01:44.810
here and it's a very
heavy TempDB workload.

00:01:44.810 --> 00:01:47.210
It's a basic stored procedure
that creates a temp table,

00:01:47.210 --> 00:01:48.360
put some data into it,

00:01:48.360 --> 00:01:49.805
and then selects from it.

00:01:49.805 --> 00:01:52.200
So you can see here in Perf man.

00:01:52.200 --> 00:01:54.090
I've got some weights in progress,

00:01:54.090 --> 00:01:55.740
page latch weights in progress.

00:01:55.740 --> 00:01:58.895
I've also got the average wait time

00:01:58.895 --> 00:02:00.380
listed here in Perf man as well.

00:02:00.380 --> 00:02:02.390
So you can see I've got
paged latch weights

00:02:02.390 --> 00:02:04.775
happening while I'm
running this workload.

00:02:04.775 --> 00:02:07.640
That's not unusual with a
heavily concurrent workload.

00:02:07.640 --> 00:02:11.580
The question here is what are
these page latch weights from?

00:02:11.580 --> 00:02:12.770
We don't know necessarily.

00:02:12.770 --> 00:02:14.405
They could be from
your user database.

00:02:14.405 --> 00:02:16.430
They could be from TempDB.

00:02:16.430 --> 00:02:18.740
We really don't know just
by looking at performance

00:02:18.740 --> 00:02:21.620
monitor where these page latch
weights are coming from.

00:02:21.620 --> 00:02:23.210
So we want to head back over to

00:02:23.210 --> 00:02:25.850
Azure Data Studio and take a look at

00:02:25.850 --> 00:02:27.110
a script that can help us

00:02:27.110 --> 00:02:30.880
determine where these page
latch weights are coming from.

00:02:30.880 --> 00:02:32.230
Looks like my workload finished.

00:02:32.230 --> 00:02:34.190
So I'm just going to kick it
back off again so that we

00:02:34.190 --> 00:02:36.925
can look at that Azure Data Studio.

00:02:36.925 --> 00:02:40.090
So let's go back here.

00:02:42.130 --> 00:02:47.135
I'm going to run this query
which I have in focus.

00:02:47.135 --> 00:02:51.740
What this query is doing is
looking at all the requests from

00:02:51.740 --> 00:02:56.510
DM exact request that have
a weight type like page,

00:02:56.510 --> 00:03:00.335
meaning some sort of
page latch weight.

00:03:00.335 --> 00:03:04.010
What I can see in the results
here is that I do actually have

00:03:04.010 --> 00:03:07.295
several sessions that are
waiting on page latch.

00:03:07.295 --> 00:03:09.305
If I look at the weight resource,

00:03:09.305 --> 00:03:11.990
I can tell just from the weight
resource that they're in

00:03:11.990 --> 00:03:15.905
TempDB because the weight
resource here is 2:1:1:20.

00:03:15.905 --> 00:03:17.420
Two is database ID,

00:03:17.420 --> 00:03:18.665
two which is TempDB.

00:03:18.665 --> 00:03:23.570
One is file number 1 and
then 120 is the page number.

00:03:23.570 --> 00:03:25.325
So I can tell it's in TempDB.

00:03:25.325 --> 00:03:30.395
But if I use this new function
called DMDB page info,

00:03:30.395 --> 00:03:34.039
what this allows me to do
is take that page resource

00:03:34.039 --> 00:03:38.330
and crack it open and see
what exactly is on that page.

00:03:38.330 --> 00:03:41.355
So from that DMDB page info function,

00:03:41.355 --> 00:03:44.150
I get this object
name and you can see

00:03:44.150 --> 00:03:46.810
here that the object name
is sys schema objects,

00:03:46.810 --> 00:03:48.095
which is a system table.

00:03:48.095 --> 00:03:50.944
So this is that TempDB
metadata contention

00:03:50.944 --> 00:03:52.685
that we were talking about.

00:03:52.685 --> 00:03:54.754
This is the problem

00:03:54.754 --> 00:03:58.220
that Memory Optimized TempDB
metadata was introduced to solve.

00:03:58.220 --> 00:03:59.960
So let's go back to
our command window.

00:03:59.960 --> 00:04:01.115
We can see that it finished.

00:04:01.115 --> 00:04:02.450
Both times it executed,

00:04:02.450 --> 00:04:06.005
it took about 52 seconds
to run the workload.

00:04:06.005 --> 00:04:09.675
We can of course see the page
latch weights happening.

00:04:09.675 --> 00:04:12.300
We can see the batch
requests per second,

00:04:12.300 --> 00:04:14.100
which are topping out at,

00:04:14.100 --> 00:04:18.225
let's see here, about 350 maximum.

00:04:18.225 --> 00:04:20.210
So that's without the
feature turned on.

00:04:20.210 --> 00:04:22.265
So let's go ahead and
turn the feature on.

00:04:22.265 --> 00:04:23.795
In order to do that,

00:04:23.795 --> 00:04:25.925
we need to enable the feature in

00:04:25.925 --> 00:04:29.090
SQL Server and we also need
to restart the server.

00:04:29.090 --> 00:04:34.250
This alter server configuration
command here requires a restart.

00:04:34.250 --> 00:04:38.875
So we're going to set that Memory
Optimized TempDB metadata on.

00:04:38.875 --> 00:04:43.540
Then I'll go ahead and
restart the server.

00:04:44.360 --> 00:04:48.025
Then once I do that,

00:04:48.025 --> 00:04:50.810
I will be able to come back

00:04:50.810 --> 00:04:54.155
to Azure Data Studio and
run another command,

00:04:54.155 --> 00:04:57.470
select server property
command to see if

00:04:57.470 --> 00:05:01.160
the TempDB memory-optimized
metadata feature is on.

00:05:01.160 --> 00:05:03.265
So let's run this command.

00:05:03.265 --> 00:05:07.245
You can see it executed
and the feature is now on.

00:05:07.245 --> 00:05:10.565
It's one. So let's go ahead
and run our workload again.

00:05:10.565 --> 00:05:13.470
Let's start Perf man.

00:05:16.490 --> 00:05:19.350
Let's kick off of our workload again.

00:05:19.350 --> 00:05:20.775
Same exact workloads.

00:05:20.775 --> 00:05:24.130
Same stored procedure, 100 threads.

00:05:24.130 --> 00:05:27.080
You might notice something
different in Perf man right away.

00:05:27.080 --> 00:05:29.900
First of all, batch requests
per second is much higher.

00:05:29.900 --> 00:05:34.520
We are going up to over 500.

00:05:34.520 --> 00:05:36.320
It might even go a little bit higher.

00:05:36.320 --> 00:05:37.580
I'll let that run for a while,

00:05:37.580 --> 00:05:40.790
but also notice those page
latch weights are gone.

00:05:40.790 --> 00:05:42.605
No more page latch weights.

00:05:42.605 --> 00:05:45.740
If we come back to Azure Data
Studio and I run that command

00:05:45.740 --> 00:05:48.990
again to look at the sessions.

00:05:48.990 --> 00:05:52.310
Notice there are no sessions
that are waiting on page latch.

00:05:52.310 --> 00:05:55.865
We've completely eliminated
the page latch weights.

00:05:55.865 --> 00:05:57.590
If I come back to Perf man,

00:05:57.590 --> 00:06:00.005
yep, my workload is already finished.

00:06:00.005 --> 00:06:02.990
So you can see I've
improved throughput.

00:06:02.990 --> 00:06:05.675
I have eliminated those
page latch weights.

00:06:05.675 --> 00:06:07.580
If I come down to my workload,

00:06:07.580 --> 00:06:10.130
it's now completed in 35 seconds

00:06:10.130 --> 00:06:13.760
versus the 52 seconds
without the feature on.

00:06:13.760 --> 00:06:19.220
So this feature is designed
to allow you to help scale

00:06:19.220 --> 00:06:23.240
up your heavy TempDB
contentious workloads

00:06:23.240 --> 00:06:25.400
without making any
code changes at all.

00:06:25.400 --> 00:06:28.085
You simply turn on the configuration,

00:06:28.085 --> 00:06:31.640
restart the server and then you
can immediately have improved

00:06:31.640 --> 00:06:33.770
throughput and less
page latch weights

00:06:33.770 --> 00:06:36.320
on your TempDB contentious workloads.

00:06:36.320 --> 00:06:38.300
So I hope that helps you learn

00:06:38.300 --> 00:06:40.790
a little bit more about the
feature, how you would use it,

00:06:40.790 --> 00:06:42.860
how you would know whether
you need to turn it on

00:06:42.860 --> 00:06:45.020
or not and gets you a little bit more

00:06:45.020 --> 00:06:46.970
excited about the improvements
that are coming in

00:06:46.970 --> 00:06:49.610
SQL Server 2019 Thanks very much.

00:06:49.610 --> 00:07:04.210
[MUSIC]