>> You've probably heard about automatic tuning,
but how could it make your workloads faster
without you doing anything?
Learn more in this episode of Data Exposed.
[MUSIC]
>> Hi. I'm Anna Hoffman,
and welcome to this episode of Data Exposed MVP edition.
Today, I'm joined by Denny Cherry.
Thanks for joining us today, Denny.
>> Hey, Anna, thanks for having me. It's great to be here.
>> Great to have you here. Before we get started,
let's start by talking a little bit about you
and what it is that you do.
>> Sure. Like you said, I'm Denny Cherry,
like you said, from Denny Cherry & Associates Consulting.
I'm the founder and CEO of the company.
We're based here in beautiful San Diego, California.
I've been a Microsoft MVP for 13 plus years now, I think,
and I've been working with SQL Server for about 25 years
and I have been working with Azure since about six months
after it was released.
I've been doing this stuff for a very long time.
>> Wow. Awesome. Well, we're excited to hear from you today.
Today, we're going to be talking about Azure SQL Database
and something specifically that is only available
in Azure SQL Database today, I believe.
I think it's automatic tuning.
Why don't you start by telling us a little bit about what it is,
how it might help, and other things we might want to know?
>> Yes. The automatic tuning features in Azure are really cool.
What they do is they take your workload,
most importantly, they look at your existing workload
and figure out what index is needed
to be added to the database
in order to improve the performance of the application.
They can also look at the existing indexes that are there,
and based on your workload,
figure out which indexes should be removed
because they simply aren't being used by anything.
It help save some space
as well as improve performance in the application.
>> Cool. I mean, that sounds pretty cool.
Now, how is it doing this
and how can I be assured that it's going to do the right thing
and it actually does improve my performance?
>> This is all built around the Microsoft Query Store.
That's been in the product since SQL Server 2016.
It's a well-baked product
that is gathering all this information up at this point.
Now, in the back-end, they're going to go through
the workload that's running against the server,
they're going to go through what's actually stored on the box,
what indexes are there,
and they're going to combine all that data together
and figure out what indexes
are going to help make the database faster.
Now, SQL Server did a lot of this for you just natively,
through execution plan
and seeing what indexes are going to be helpful.
But this is taking that a step further
by combining all that data together and figuring out
not only which indexes are going to be helpful,
but which indexes are turning to be duplicates of other indexes
or subsets of indexes that is also recommending.
It's not building the same index
with slightly different columns
because obviously, that would be a huge waste of space,
it would end up slowing down some of the other things
happening within the database.
>> Awesome. Great, Denny.
Another question, if I have this turned on, for example,
and it decides it needs to go create an index,
is it going to do that immediately?
>> It'll do that in the background.
It will do it pretty much as soon as it figure out
it needs to create an index,
but it doesn't necessarily have to actually build them.
You can't have it not build things or have it build things.
Let me show you in fact how you would actually turn this feature on
because it's a really easy thing to turn on.
It's probably one of the easier features in Azure to turn on.
>> Awesome. Let's take a look.
>> Here we have a database that's been created
in Azure on one of my virtual servers.
It's just a regular database.
If we go down here to Automatic Tuning,
we can simply turn the feature on
or turn the feature off as we need to,
and we can inherit the permissions
from either our Azure subscription
or from our server settings if we want to inherit them,
or if we don't, we can just force this settings to be on
or force the settings to be off.
We've got several settings here we can turn on and off.
We can force plan.
We can create index or we could drop index.
We can turn these all on and off
depending on what we want SQL Server to do.
>> Thanks, Denny. That seems really easy to set up.
>> Yeah. It is really, really easy to set up.
I think it's one of the easiest features there is
to turn on in Azure
and it's not being used by enough people, unfortunately.
>> Interesting. In your opinion, what are some of the caveats,
when might this be good to use?
When might you actually not recommend using this?
Or who is this going to be best for?
>> When this is going to be really the most useful feature
is going to be people that are not that familiar with databases.
Maybe they're really good at programming,
but they're not that great at performance tuning
the actual database itself.
They can go turn this feature on.
It's going to do a lot of the heavy lifting for you.
It's not perfect by any means,
but it is going to do a lot for you.
You can get maybe 80, 90 percent of the way there
without having to actually do a lot of work.
When this is going to be really useful
is when you have known consistent workloads.
When you're running the same kind of queries
against the box on an extremely regular basis,
that's where it's really going to make the most sense for you.
We've got sporadic workloads or maybe you've got
end of quarter queries that are running on the box,
those really aren't going to be all that useful to you
because by the time Azure figures out you need those indexes,
you're not running those queries anymore.
This can be a good lead into the fact that
that drop index feature of this product
can be very, very dangerous.
Because if you've got indexes on your system
that are specifically there for end of quarter,
end of year reporting,
those probably aren't going to get used
by your application as part of its regular workload.
So the drop index feature may go through
and remove all those indexes.
Then when the end of quarter,
the end of year comes around,
suddenly there is a report that management
is expecting to run aren't going to work so well.
All of a sudden, we're going to have a big problem ahead of us.
>> Yeah, definitely. That's a good got you
that I actually never thought about.
Thanks for sharing that with us.
On your other point, you mentioned that
this can get you 80, 90 percent there.
Now, what if it does something and it's the wrong thing to do,
like it creates an index and it doesn't help?
>> Yeah. That's a great question.
The metrics that are going on and being looked at
to figure what indexes should be made,
those are going to be looked at after indexes are created as well.
If Azure, in this case,
looks at those metrics and goes,
hey, performance is actually worse after we created this index,
it will actually roll back that change
and basically pull that index back out
because that wasn't a beneficial index for an add.
The system obviously does make mistakes and it can add indexes,
either because we've got weird queries
or we got weird things going on in the system.
So it can add indexes that may not be super beneficial
or may even be detrimental.
But it will back out any problems that it does cause.
>> That's pretty cool.
Yes, it's not perfect, it can make mistakes,
but at the same time, it's going to realize
when it makes those mistakes and backtrack accordingly.
Cool. Awesome.
Denny, thanks so much for joining us today.
This has been really useful.
If you have one thing you wanted our viewers to walk away with,
what would you tell them?
>> I'd point out the fact that while it's not perfect,
it is definitely something worth looking at
in 98, 99 percent of use cases.
It's a really useful feature that I don't think is used enough
by enough people that are moving up to the Cloud.
>> Awesome. Cool. Well, thanks so much, Denny.
We'll put some references in the description.
For our viewers, thanks for joining us today.
We hope you liked this video.
Please subscribe to our channel and leave us a comment,
and let us know if you're using automatic tuning.
We hope to see you next time on Data Exposed.
[MUSIC]