>> Query processing is
a very important factor in how your workload runs.
In this episode, Kate Smith
joins us to tell us about query processing and
how intelligent query processing makes
your workloads run even faster.
This week on Data Exposed.
[MUSIC]
Hi, I'm Anna Hoffman and welcome to this episode of Data Exposed.
Today, I'm joined by our repeat guest,
Kate Smith, a Program Manager on the Azure SQL team.
Kate, thanks so much for coming
back on the show today. How are you doing?
>> I'm doing great. Thank you for having me on.
>> Awesome, great to have you on.
When you came on before, you did
a really great series about elastic jobs,
and we'll be sure to include a link
to that in the description for our viewers.
But today we are going to be talking about query processing.
I think this is a really great topic,
a topic that we probably don't talk about enough.
But I'd love if you could just give us an overview of
what good query processing even means and why does it matter?
>> That's a great question, Anna.
Query processing, or QP,
as I like to refer to it,
is something that when it's going well,
you don't even think about it.
You don't even know what's happening,
you don't want to think about it.
Ideally, it's just everything is smooth and it's all good.
What it does in terms
of how it benefits you to have a good query processor
is it expands the capability of your database.
Sometimes there are queries that would not return if they
were executed as written without
any optimization or any efficiency hacks.
By hacks, I just mean tricks that
the optimizer does to make things run more quickly.
It allows queries to execute that maybe wouldn't execute in
an appropriate time or finish it
all if we didn't have good optimization and execution.
It uses the researches of the database effectively,
it allows queries to run in
parallel in appropriately sized amounts of resource bubbles.
So they'll have the resources they need,
but one query won't starve out
the other queries or prevent another queries from executing.
It reduces the amount of manual intervention that you have to do.
I've heard of all tricks that people have done
when they have a large gnarly query and
they're trying to get it to complete or trying
to get it completely on time,
and there's lots of tricks that I've heard people doing.
But a good optimizer's going to reduce
the amount of time you'll have to spend doing that manual stuff,
and allow you to just run your queries and enjoy good performance.
It will also adjust to changing conditions to some degree.
If you have a query that when it's first executed,
just gets a small number of rows but then the tables grow,
and you re-execute that same query.
Ideally, the query would execute again with
a slightly different optimization to
account for the larger number of rows.
That's the long and short of
what a good query processor does and why it matters.
>> Awesome, yeah. It seems like from your great explanation that
this thing is doing the work underneath the hood,
figuring out how your queries are actually going to be run,
and in a big part are going to actually impact the performance.
Now, when we talk about query processing,
what does it actually do and how
does it compare to intelligent query processing?
Which we've heard about before
and talked about on the show before,
which is also available.
>> Great question. Query processing
versus intelligent query processing.
Query processing, what it does is it will reorganize your query,
create a plan that says,
this is how I'm going to execute the query,
then it will choose specific execution strategies,
execute on those, etc.
Now, query processing in general is all that planning,
and all that stuff is based off of a set of underlying assumptions
and aggregate data statistics about the query or about the data.
What can happen is that,
sometimes there's an abrupt change as the data
size changes and it does that adaptation we talked about,
that there'll be an abrupt change to the way
a query is executed and that can
cause noticeable performance difference.
It could also have a plan
that chooses an execution strategy based on
an appropriate guess based on statistics
that don't actually match the reality of the query.
Then those two things can get out of sync
and that can also cause performance issues.
What intelligent query processing does
is it takes all of those good parts of query processing,
and it enhances those with specific strategies
designed to make transitions between plans or mistakes,
not that the query processor made a mistake,
but like I said,
we have to use assumptions,
and when those assumptions don't match reality,
we can actually now adjust to,
"Oh actually, this is the reality," and
make the assumption part play less of a role in
the query processing and make
the actual reality of the query go board to the forefront.
When you have more accurate data,
then you can have
a better execution and faster query with fewer surprises
and a smoother transition between plans.
>> Awesome, that's really cool. I think that's
a really great explanation.
We have the query processor,
which is doing the processing,
the optimizing and intelligent query processing
is really just taking it one step further.
Now, when you say intelligent, I start to think,
machine learning's being used,
maybe this is some AI model.
Where is this data that we're using coming from?
Is that something folks should be concerned about?
>> No. All intelligent query processing is doing,
it's not doing AI type training or anything like that.
What it does is it takes
your workload and it looks at
how things are doing with your workload
and makes adjustments based on that.
Some features of intelligent query processing will
kick in only on subsequent executions of the query.
You execute it at one time,
like let's say memory grant feedback,
you execute it one time and "Oh no,
we didn't give the query enough memory
and it's built the disk," and that's a very costly operation.
We'll remember that for
next time and we'll give the query more memory.
It doesn't have anything to do with anybody else's data set,
that we're not sharing any of that with anybody,
anything like that.
>> So it's cache?
>> All of that stuff is just based on what
you've executed and how your queries are doing.
>> That's really great to know.
I've heard Pedro Lopes talk about this before, and he says,
''The greatest thing about intelligent query processing
is you just run faster,'' which I think is awesome.
It's a great story. Now, how do I know if this is working,
if it's in action, if it's enabled, what do I have to do?
>> The way that these features are turned on, turned off, etc.
If you are using the latest compatibility level of your database
and you have got the latest stuff,
you get these features for free.
You don't have to do anything to enable them or turn them on.
There are specific ways for each feature to turn
them off, that's in our documentation.
But if you want them,
you just increase your compatibility
to the latest and you get them.
You don't have to do anything else,
and you might notice a difference in your workload right away.
>> Wow, that's pretty cool.
Kate, this has been a really great,
succinct session to understand query processing,
the importance, and how intelligent query processing works.
If you had to summarize or give people some advice on
how to think about this, how would you do that?
>> Well, I would say intelligent query processing
takes the awesome work that query processing already does.
It adds the ability to learn,
the ability to adapt.
It already adapted a little bit.
It adapts a little bit better with
the intelligent query processing features,
and it enhances the efficiency of
the operations that it's doing the way it chooses to do them.
Learn, adapt, and enhance efficiency,
all three of those things,
when you take them together,
you come up with a formula for better plans and fewer surprises,
which is what we want for making your
workload better in the long run.
>> Awesome, I love that. Better plans and less surprises,
that's always good and
better performance is all we're always going for.
Kate, thanks so much for coming on the show today.
I believe you've written a series of
blog posts on this topic as well,
so we'll be sure to put that in
the description for our viewers to go learn more.
Again, thanks for joining us on the show.
For our viewers, thanks for watching.
If you like this show or episode, please give it a like,
give us a comment on what you're doing with
intelligent query processing or how you're
seeing its effects on your workloads,
and we hope to see you next time on Data Exposed.
[MUSIC]