WEBVTT

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

00:00:10.560 --> 00:00:12.975
>> Hey, welcome to a new
episode of Data Exposed.

00:00:12.975 --> 00:00:14.460
My name is Pedro Lopes.

00:00:14.460 --> 00:00:16.920
I'm a Program Manager in the
SQL Server Engineering team.

00:00:16.920 --> 00:00:18.510
Today, I'm going to be talking

00:00:18.510 --> 00:00:20.670
about Intelligent
Database, specifically,

00:00:20.670 --> 00:00:23.809
Intelligent Query Processing
in SQL Server 2019,

00:00:23.809 --> 00:00:25.925
and also Azure SQL Database.

00:00:25.925 --> 00:00:29.390
So let's get to it. SQL
Server 2019 introduces

00:00:29.390 --> 00:00:31.864
groundbreaking query
performance enhancements

00:00:31.864 --> 00:00:34.655
and they're the Intelligent
Query Processing family.

00:00:34.655 --> 00:00:37.820
These make up the latest on
Microsoft's mission to make sure

00:00:37.820 --> 00:00:41.690
that critical parallel workloads
improve when running at scale,

00:00:41.690 --> 00:00:45.470
while remaining adaptive to the
constantly changing world of data,

00:00:45.470 --> 00:00:47.855
as data moves in and
out of databases.

00:00:47.855 --> 00:00:49.670
In this video, I'm going to give you

00:00:49.670 --> 00:00:51.980
a quick overview of the
Intelligent Database world

00:00:51.980 --> 00:00:53.030
that really takes a leap

00:00:53.030 --> 00:00:56.150
forward with the upcoming
SQL Server 2019,

00:00:56.150 --> 00:00:58.700
and introduce you to a number
of features that we'll dive

00:00:58.700 --> 00:01:02.130
into deeper in other
videos in this series.

00:01:03.170 --> 00:01:07.510
Intelligent Query Processing
in SQL Server is available by

00:01:07.510 --> 00:01:11.245
default on the latest database
compatibility level setting.

00:01:11.245 --> 00:01:13.210
That means that after you upgrade,

00:01:13.210 --> 00:01:15.130
this can be available just by

00:01:15.130 --> 00:01:18.000
flipping the switch to use the
latest compatibility setting.

00:01:18.000 --> 00:01:22.030
Intelligent Query Processing
also delivers broad impact

00:01:22.030 --> 00:01:23.440
that improves the performance of

00:01:23.440 --> 00:01:26.650
existing workloads with
minimal implementation effort.

00:01:26.650 --> 00:01:28.390
This really means that most time,

00:01:28.390 --> 00:01:30.965
there is zero need to
refactor your code.

00:01:30.965 --> 00:01:33.310
Intelligent Query Processing improves

00:01:33.310 --> 00:01:36.190
critical parallel workloads
when running at scale,

00:01:36.190 --> 00:01:39.355
and as data flows in and
out of your database,

00:01:39.355 --> 00:01:41.380
we will adapt to that and

00:01:41.380 --> 00:01:44.660
maintain a level of
predictable performance.

00:01:44.660 --> 00:01:47.450
For example, by introducing

00:01:47.450 --> 00:01:49.880
a feedback mechanism
into the memory usage,

00:01:49.880 --> 00:01:53.630
we can ensure that your workload
executes in a predictable way.

00:01:53.630 --> 00:01:58.190
If a given query execution would
maybe take up too much memory,

00:01:58.190 --> 00:01:59.750
we can correct it and

00:01:59.750 --> 00:02:02.375
increase the concurrency
factor of your database.

00:02:02.375 --> 00:02:06.020
If a given equity execution
does not get enough memory and

00:02:06.020 --> 00:02:09.560
ends up using additional IO
throughout is known as a spill,

00:02:09.560 --> 00:02:11.315
then we can also find that

00:02:11.315 --> 00:02:13.565
and correct the situation
so that the operation

00:02:13.565 --> 00:02:15.260
executes in memory and

00:02:15.260 --> 00:02:18.200
performs as expected in
subsequent executions.

00:02:18.200 --> 00:02:20.540
This feature is now enabled for

00:02:20.540 --> 00:02:22.835
all execution modes in
the database center.

00:02:22.835 --> 00:02:27.170
Batch mode for more data warehouse
and analytical workloads,

00:02:27.170 --> 00:02:31.410
and row mode for your
critical OLTP workloads.

00:02:31.700 --> 00:02:34.640
We're also going into new areas

00:02:34.640 --> 00:02:37.220
which we're calling
approximate query processing.

00:02:37.220 --> 00:02:40.640
For example, think of a scenario
where a railroad company keeps

00:02:40.640 --> 00:02:42.350
track of numbers of tickets that are

00:02:42.350 --> 00:02:44.935
bought and used in the
entire railroad system.

00:02:44.935 --> 00:02:47.030
They keep track of this
in order to implement

00:02:47.030 --> 00:02:49.730
some flow control measurements
when it's needed,

00:02:49.730 --> 00:02:52.610
perhaps by adapting the
schedules of the trains,

00:02:52.610 --> 00:02:53.630
or the number of trains in

00:02:53.630 --> 00:02:55.810
the system to meet the
needs of the moment.

00:02:55.810 --> 00:02:58.920
This information is also
updated in a dashboard

00:02:58.920 --> 00:03:02.530
that folks in Downtown
Central can take a look at.

00:03:02.530 --> 00:03:04.220
Now, in this scenario part of

00:03:04.220 --> 00:03:06.830
the workload will be surely
to run a query that's

00:03:06.830 --> 00:03:09.020
constantly looking at obtaining

00:03:09.020 --> 00:03:12.005
the row count of all the
tickets that are sold and used,

00:03:12.005 --> 00:03:14.600
and this is probably
coming from a very

00:03:14.600 --> 00:03:17.605
large stable perhaps with
billions and billions of rows.

00:03:17.605 --> 00:03:20.540
Now, this recurrent query
would normally take up

00:03:20.540 --> 00:03:23.735
considerable resources on
your server, namely memory.

00:03:23.735 --> 00:03:25.639
If it's executed repeatedly,

00:03:25.639 --> 00:03:26.690
can really affect

00:03:26.690 --> 00:03:28.900
the performance characteristics
of your database.

00:03:28.900 --> 00:03:30.670
However, in this scenario,

00:03:30.670 --> 00:03:32.750
the railroad company
doesn't necessarily need

00:03:32.750 --> 00:03:35.830
an actual count of all the
tickets that are sold and used.

00:03:35.830 --> 00:03:37.790
But a very high
approximation is enough

00:03:37.790 --> 00:03:40.280
to trigger all the
required decision making.

00:03:40.280 --> 00:03:42.935
This is where approximate
count distinct comes in,

00:03:42.935 --> 00:03:45.500
by allowing a query
to repeatedly obtain

00:03:45.500 --> 00:03:48.185
the approximated count
of tickets sold and used

00:03:48.185 --> 00:03:51.080
without the severe impacts to
your database performance that

00:03:51.080 --> 00:03:55.420
your normal count query
would take today.

00:03:55.640 --> 00:03:58.695
By enabling Batch Mode on Row Store,

00:03:58.695 --> 00:03:59.950
we effectively unleash

00:03:59.950 --> 00:04:02.150
the processing mode that's
especially optimized

00:04:02.150 --> 00:04:05.975
for analytical workloads over
any table on your database.

00:04:05.975 --> 00:04:08.180
This means that even
for scenarios where

00:04:08.180 --> 00:04:10.385
a column store index
would not be an option,

00:04:10.385 --> 00:04:14.395
the database engine can still
execute this in an optimized way.

00:04:14.395 --> 00:04:17.375
In turn, this opens the scope of

00:04:17.375 --> 00:04:20.630
features like Adaptive join
to be used by your workload.

00:04:20.630 --> 00:04:24.170
Adaptive join which is only
available in batch mode can

00:04:24.170 --> 00:04:28.940
now be leveraged across all the
tables and most of your workloads.

00:04:29.590 --> 00:04:33.170
We also tackled some of the
most recurrent anti-patterns

00:04:33.170 --> 00:04:36.260
that become a problem for
managed SQL Server workloads.

00:04:36.260 --> 00:04:38.150
The use of Table
Variables and the use

00:04:38.150 --> 00:04:40.105
of Scalar UDFs, for example,

00:04:40.105 --> 00:04:42.440
and now we can unlock new levels of

00:04:42.440 --> 00:04:46.375
query optimization that were
not possible until recently.

00:04:46.375 --> 00:04:49.310
All of this, we'll
discuss deeper and with

00:04:49.310 --> 00:04:51.080
demos in upcoming videos in

00:04:51.080 --> 00:04:53.270
the series about the
intelligent database,

00:04:53.270 --> 00:04:56.020
specifically intelligent
query processing.

00:04:56.020 --> 00:04:59.505
But if you want to know
more about it today,

00:04:59.505 --> 00:05:04.200
then please go to this aka.ms/IQP

00:05:04.200 --> 00:05:06.899
URL where you see all
the documentation

00:05:06.899 --> 00:05:09.535
on Intelligent Query
Processing in SQL Databases.

00:05:09.535 --> 00:05:13.100
If you want to experiment some
of these by yourself right now,

00:05:13.100 --> 00:05:16.040
we also have demos that
you can look at if you

00:05:16.040 --> 00:05:18.980
go to our GitHub repository
and the short URL would

00:05:18.980 --> 00:05:22.430
be aka.ms/IQPDemos where you'll

00:05:22.430 --> 00:05:25.900
be able to look at all these
features and experiment by yourself.

00:05:25.900 --> 00:05:27.795
So again, take care.

00:05:27.795 --> 00:05:28.980
I'll be talking to you soon.

00:05:28.980 --> 00:05:43.780
[MUSIC].