Hi, this is Seth Juarez.
This is another episode of Azure Friday.
I'm here with Velko to talk all about automatic tuning.
Wanna tell us you who are and a little bit about the feature?
>> Yep, thank you, Seth, for having me.
My name is Velko,
I'm a programmer in Azure SQL Database.
I'm working on these intelligent database scenarios and
today I wanted to demo a little bit about automatic tuning.
>> Fantastic, so tell us what automatic tuning is for
those who wanna know and
then let's get in to see how we can do it.
>> Yep, automatic tuning is a feature on Azure SQL Database
and also available partly on SQL Server 2017 that
automatically tunes your performance.
Basically by observing the workload that is happening on
[INAUDIBLE] and figuring out what are the optimization
opportunities that SQL Server can apply.
>> I see, so traditionally, I actually have to go in there and
put my own indexes all over whatever because I have to
figure out what it is.
You're saying that now it will just do it for
you automatically?
>> Yeah, exactly.
>> All right, I need to see to believe it.
>> I can show you right away.
So first I'm going to show you the demo that
finds the good plan and when the plan regression happens,
it automatically fixes the plan regression.
So what I have here is a small application and
as you can see it is running about 70 requests per second and
it is running actually this simple query.
But this table underneath has the SQL data, so when you sent
a query with specific parameters a planned regression can happen.
Basically a SQL Server chooses a plan that is optimal for
this single parameter, but for
other parameters, it doesn't work that well.
So when this happens and
when this plan is cached, the performance of other requests
that are coming with different parameters regresses.
And actually by clicking here on this button,
actually that would happen.
I'll clear the cache,
I'll send this exact same query with the problematic parameter,
but afterwards, other queries will start coming.
And you see the regression happening, so let me show you.
I click Regression,
you see the number of requests is falling down.
In the back end currently,
SQL Server is estimating this new plan.
It is comparing it with the baseline plan that it had for
the previous executions of this query.
And as you can see, it figured out that the previous
plan was actually better performing.
And it started automatically forcing the new plan.
So you no longer have to wake up in the middle of the night to
fix the same proplanner regression.
>> So let's see if I understand this correctly.
So what happens is whenever you run a query
it creates a plan and then it caches that for speed.
>> Yep. >> But then the query
might change a little bit based upon a parameter and
the cached Query plan is not gonna be as fast.
>> Yep.
>> And so what it's gonna do is it's looking at the queries that
are coming in, saying my cash plan needs to change a little
bit, it'll automatically adjust it for you.
>> Yep, yep.
In a sense basically [INAUDIBLE] picks multiple plans.
>> Uh-huh. >> And
one of the plans is good for most of the executions.
But there is this single parameter that is problematic
and when that plan gets cached, the plan regression for
all other executions happens.
And basically that's what is the tactic here and
then the good plan is first.
>> Is this something I need to set up on my database to do or
this just going to happen for me if I'm using a SQL?
>> You just need to enable it and actually here below,
I have the TC file syntax for
enabling this particular automatic tuning option.
There are other options in automatic tuning as well,
like create missing indexes and
drop duplicated indexes And I wanna show you quickly how you
can enable this in Azure Portal as well.
>> Let's do it.
>> So I am on the Azure Portal now,
I'll select a single database.
And here in the database features list, I have a tile for
automatic tuning, I can click on it, and
then I see all the options that are available in Azure.
So one information is that,
forcing a good plan is available on SQL Server 2017.
While creating index and drop the duplicate and
unused indices is available on Azure SQL database on
the [INAUDIBLE], okay.
You can enable automatic tuning by selecting the overall
database level option.
So you can inherit settings from your server,
so you have a bunch of database under a server you can just
configure it on the server level.
>> Smart.
>> And all databases just inherit, or
you can choose to inherit Azure defaults.
And these are basically the defaults that we think are good
at this time for the service and for the database in general.
When I select these high level database options, I can go and
explicitly select any of these options.
So let's say I want to have a drop-in that's explicitly
enabled, because I know I have probably duplicate indexes for
some reason, but for
the other two I want to inherit the options from Azure defaults.
I just select this, hit Apply.
And on the right side,
I see the current settings that are currently available.
Azure defaults, at the moment, for Azure SQL Database are force
plan-enabled and create a machine as enabled as well.
>> I see, so let's take a look at each one.
Cuz one of the things that I've, I've used databases before,
but I haven't looked at the plans in a long time.
I just figured, I send the query and magic things happen.
So every time you send a query,
there is a plan that is generated and cached?
>> Yep.
>> And that cached plan is not parameter invariant?
Because it just depends on your data, is that correct?
>> Yeah.
>> So if I'm sending a parameter,
there that changes the plan that's gonna cause severe,
you call them regressions?
>> Yep. >> Which makes things go
a little bit slower.
And so what is it that's actually happening on to
the covers when you're changing it?
>> So what is happening is when the query comes,
the SQL Server figures out based on the statistics
what is the proper plan to generate.
And as I said,
if you send with one parameter, the statistics for
that parameter may be different than for all of the others.
And then the different plan is created and
after that this plan is cached, SQL Server will say hey,
I already have this plan cached, so why don't I use that plan?
And for other parameters that plan might not be that good.
Previously what you had to do is when this happens,
you will need to go DMVs to figure out hey,
this is a good plan, this is a bad plan.
And then based on that, decide how to force this good plan for
all of my [INAUDIBLE].
>> I see, so previously this is where the pain point is
you had to actually go in and say, no use this plan.
Use this plan [INAUDIBLE].
>> Yeah, SQL Server in most cases picks the good plan.
But there are these specific cases where because
the data is secured and some other reason underneath,
this plan regressions can happen.
And the problem was that they can happen all of a sudden
then you need to wake up in the middle of the night.
In most cases, you would wake up at 3 AM and you go there and
fix the plan.
And fixing the plan is not that big of a deal if you know
the DMVs and you understand how to credit them and
where to look for this information.
But this will make this all go away,
it will do it automatically.
>> That's awesome.
The other two things is about indices because you have create
index and drop index.
What is it that it's going to be doing when it's
automatically doing when it comes to index?
It is going to literally create and apply index?
>> Literally.
>> So let me show you.
>> Okay.
>> Now that I enable automatic tuning,
all of this things start happening in the back end.
And I can scroll here a little bit down and
now select Performance Recommendations.
Here you can track all the recommendations that automatic
tuning applied.
And as you can see,
most of them are currently in the pending state.
Automatic tuning takes care of the user workload so
it will never do the operation when
your database is under high utilization.
It will wait for a proper time to apply it so
that your user workload is not affected.
In addition, all of the actions that automatic
tuning does are non blocking.
So if you create an index, that is automatically created index
exist in the database it will not block your operations.
So when you can have a regular index on a certain column, and
you try to drop that column, that will be blocked.
SQL Server will tell you hey, you already have this index,
you might want to drop the index first, and
then you can drop this column.
But if automatic tuning created the index, then this index would
just move out of the way letting your operation pass.
One additional important point here, and
I can show you how the applied recommendation looks like
that was verified and now is in the successful state.
You can see all of this information that tell you, hey,
how did this information help me,
how did it improve my database?
It's reduced, the d2 user this much,
this is the number of improved queries, this is the number of
queries that's slightly regressed because of this index.
This is the size of the index.
What does this say is that all automatic tuning operations
are verified after being applied.
So we keep the baseline of your performance,
we apply the recommendation, and
then we verify the recommendation was indeed good.
And if it was good, we would provide you with this report.
This would tell you, this is the improvement that you achieved
because of automatic tuning.
In case the recommendation for some reason was not good for
your database, it would be automatically reverted.
Making sure that your performance
only improves with automatic tuning.
>> And it only does this in a way that doesn't impact what's,
because if you're under high utilization,
most likely you're on Black Friday.
It's not gonna be doing all these things for you.
>> Yeah, it would postpone the operation, it will recognize
the opportunity, but it will postpone it for a later time.
>> So this is literally like having an accountant for your
SQL data at your SQL database that's sitting there, crunching
the numbers all the time, saying I have some recommendations.
I'm going to go ahead and do that for you.
And then it's going to tell you how it improved it.
>> Exactly. >> And if it didn't improve it,
you say you know what, I'm going to just take it off.
>> Yeah, exactly.
>> And this is done all, is there a specific skew that
we have to have or is it just all of them?
>> All of them, all of them are available in Azure.
>> That's amazing, so
where could people go to find out more about this,
if they wanna read more?
>> We'll have the links posted here as well,
and you can reach out to me definitely on the comments,
I'll be there to reply if you have any questions.
>> Well, this is amazing.
Before we had to, like Neanderthals, go in there and
create our own indices.
>> [LAUGH] >> We had to look at query plan
performance, etc.
Thanks so much for spending some time with us,
we've been learning all about how to do automatic tuning on
your Azure SQL databases, here on Azure Friday
[MUSIC]