>> The last performance topic that we
have is related to intelligence.
Now, we saved it to the end because while a lot of
these features and capabilities are available in SQL Server today,
there are some new capabilities that you
get when you can leverage the power of Azure.
>> Yeah. As I said,
intelligent performance is us trying to help you.
We've talked a lot of topics about
how to maintain and accelerate your performance,
but what if SQL Server and Azure SQL could
assist you in the process of making your performance faster.
In this topic of intelligent performance,
it really comes to three major areas:
intelligent query performance,
a concept called automatic plan correction, and automatic tuning.
The first topic is intelligent query performance.
The query optimizer is built into the database engine.
We wanted to build into
the engine capabilities to
handle almost any workload you could throw at us,
adapt to it, and make it faster with zero code changes.
You enable this functionality you see
here on the screen not by a feature.
But by turning on database compatibility,
we started this journey in Azure SQL and in SQL
17 with something called adaptive query processing.
Then continued on this journey in SQL 19 and Azure SQL with
dbcompat 150 for the overall family
called intelligent query processing.
>> Thanks Bob. This is really interesting.
It seems like there's a lot that we're doing behind the scenes,
whether it's SQL Server or Azure SQL to
make our customers faster without any code changes,
which is pretty incredible.
But I see it aside here and I
wanted to call it out because we haven't talked about yet.
That's about dbcompat.
Can you explain to us a little bit about
how dbcompat relates in this world of Azure SQL?
>> It's a great question. It's great call-out.
We're talking about dbcompat from a performance perspective,
but really it's significant from a migration perspective.
When you take a SQL Server and migrate it to Azure SQL,
or even create a new Azure SQL database in some cases,
we're going to use a dbcompat level that's going to give you
the functionality that you're used
to from a previous version of SQL Server.
So dbcompat 140 lines up with the SQL 17, for example.
It's really nice because you can take advantage of
all functionality in Azure SQL.
But risk and mitigate your migration
and not to worry so much about breaking
changes in application problems,
because you're using an older compat level.
But then when you're ready to take
advantage of things like intelligent query performance,
you can then change your compat level,
you can do that in the portal, you can do that in T-SQL,
to Management Studio, and then all of a sudden now
you can light up things like intelligent query processing.
We're giving you the best of both worlds.
In fact, since we're a version with SQL Server,
database compatibility is the way to in many cases,
control the functionality that you
might be using in an Azure SQL deployment.
Now, the second area of
intelligent performance is automatic plan correction.
We introduced this functionality with Azure SQL and SQL 17.
The scenario is that as sometimes a query plan may change on you,
you can regress your performance.
We call this a query plan regression problem,
and sometimes it happens out of your control.
Look at this graphic right here.
You're going along great with performance.
All of a sudden, a query plan gets evicted from cache,
a new plan comes in for the same query.
Things like parameter sets of plans can cause this issue,
and performance just really goes bad. Now, you know what to do.
Would it be nice if Azure SQL
could not only detect these problems,
but fix them automatically?
We call this automatic plan correction,
part of the automatic tuning family.
In SQL Server and Azure SQL,
you can enable this in Azure SQL through the portal or through
T-SQL and through SQL Server through
T-SQL functionality with alter database.
You can even by default,
when the query store is enabled,
see our recommendations on how to
revert to a plan that can give you better performance,
or in Azure SQL by default,
we're going to fix these automatically.
Can you imagine a scenario where you really got performance
had a small blip and it came back and you didn't really know why,
but it's because behind the scenes,
we could do an automatic plan correction fixed for you.
The results of this is only
exposed to a Dynamic Management View that you
would see through Azure SQL or through SQL Server.
That's automatic plan correction.
Now, the final piece of the puzzle of it tells
the performance is specific to Azure SQL Database,
and I call that automatic tuning
mostly through index recommendations.
In Azure SQL Database, first of all,
because you have the Query Store enabled,
you're going to see performance visualizations
light up in the portal,
where you can go in and look at
query performance over time or look at
reports to see your topmost resource-consuming queries.
In addition, as you can
see here in the graphic that we're showing you,
Azure SQL Database using machine learning,
can behind the scenes,
detect query patterns and even
recommend possible indexes for you to create.
You can also see this information through DMVs,
and the story gets better.
Not only could it recommend things,
but if you decide to do a configuration at
your logical server or at a database level,
we can automatically behind the scenes create the indexes for you,
monitor performance of using that index over time,
and if we decide it's not healthy,
we could even drop the index behind the scenes.
That combined together is a machine learning approach that we
think is pretty amazing called automatic
tuning in Azure SQL database.
>> We've seen that Azure SQL supports things from SQL Server
like IQP and automatic plan correction.
We also took a brief aside to talk about dbcompat,
which can help you move to Azure SQL with
the compatibility you're currently on
today and shift when you're ready.
Now, using the power of Azure,
we're also able to leverage
machine learning in your database to help with things like
creating and dropping indexes
or parameterizing queries automatically.
In the next video, you're going to see how.
See you in the next video.