[MUSIC].
>> Hey, welcome to a new episode of Data Exposed.
My name is Pedro Lopes.
I'm a Program Manager in the SQL Server Engineering team.
Today, I'm going to be talking
about Intelligent Database, specifically,
Intelligent Query Processing in SQL Server 2019,
and also Azure SQL Database.
So let's get to it. SQL Server 2019 introduces
groundbreaking query performance enhancements
and they're the Intelligent Query Processing family.
These make up the latest on Microsoft's mission to make sure
that critical parallel workloads improve when running at scale,
while remaining adaptive to the constantly changing world of data,
as data moves in and out of databases.
In this video, I'm going to give you
a quick overview of the Intelligent Database world
that really takes a leap
forward with the upcoming SQL Server 2019,
and introduce you to a number of features that we'll dive
into deeper in other videos in this series.
Intelligent Query Processing in SQL Server is available by
default on the latest database compatibility level setting.
That means that after you upgrade,
this can be available just by
flipping the switch to use the latest compatibility setting.
Intelligent Query Processing also delivers broad impact
that improves the performance of
existing workloads with minimal implementation effort.
This really means that most time,
there is zero need to refactor your code.
Intelligent Query Processing improves
critical parallel workloads when running at scale,
and as data flows in and out of your database,
we will adapt to that and
maintain a level of predictable performance.
For example, by introducing
a feedback mechanism into the memory usage,
we can ensure that your workload executes in a predictable way.
If a given query execution would maybe take up too much memory,
we can correct it and
increase the concurrency factor of your database.
If a given equity execution does not get enough memory and
ends up using additional IO throughout is known as a spill,
then we can also find that
and correct the situation so that the operation
executes in memory and
performs as expected in subsequent executions.
This feature is now enabled for
all execution modes in the database center.
Batch mode for more data warehouse and analytical workloads,
and row mode for your critical OLTP workloads.
We're also going into new areas
which we're calling approximate query processing.
For example, think of a scenario where a railroad company keeps
track of numbers of tickets that are
bought and used in the entire railroad system.
They keep track of this in order to implement
some flow control measurements when it's needed,
perhaps by adapting the schedules of the trains,
or the number of trains in
the system to meet the needs of the moment.
This information is also updated in a dashboard
that folks in Downtown Central can take a look at.
Now, in this scenario part of
the workload will be surely to run a query that's
constantly looking at obtaining
the row count of all the tickets that are sold and used,
and this is probably coming from a very
large stable perhaps with billions and billions of rows.
Now, this recurrent query would normally take up
considerable resources on your server, namely memory.
If it's executed repeatedly,
can really affect
the performance characteristics of your database.
However, in this scenario,
the railroad company doesn't necessarily need
an actual count of all the tickets that are sold and used.
But a very high approximation is enough
to trigger all the required decision making.
This is where approximate count distinct comes in,
by allowing a query to repeatedly obtain
the approximated count of tickets sold and used
without the severe impacts to your database performance that
your normal count query would take today.
By enabling Batch Mode on Row Store,
we effectively unleash
the processing mode that's especially optimized
for analytical workloads over any table on your database.
This means that even for scenarios where
a column store index would not be an option,
the database engine can still execute this in an optimized way.
In turn, this opens the scope of
features like Adaptive join to be used by your workload.
Adaptive join which is only available in batch mode can
now be leveraged across all the tables and most of your workloads.
We also tackled some of the most recurrent anti-patterns
that become a problem for managed SQL Server workloads.
The use of Table Variables and the use
of Scalar UDFs, for example,
and now we can unlock new levels of
query optimization that were not possible until recently.
All of this, we'll discuss deeper and with
demos in upcoming videos in
the series about the intelligent database,
specifically intelligent query processing.
But if you want to know more about it today,
then please go to this aka.ms/IQP
URL where you see all the documentation
on Intelligent Query Processing in SQL Databases.
If you want to experiment some of these by yourself right now,
we also have demos that you can look at if you
go to our GitHub repository and the short URL would
be aka.ms/IQPDemos where you'll
be able to look at all these features and experiment by yourself.
So again, take care.
I'll be talking to you soon.
[MUSIC].