SQL Server 2017: Adaptive Query Processing

SQL Server 2017: Adaptive Query Processing


[MUSIC] Hello, everyone, my name is Joe Sack
and I’m a Program Manager for the SQL Server Product team. And today, we’re gonna be talking
about SQL Server vNext and Adaptive Query Processing. So to help frame the discussion, we’re gonna talk about plan quality
and cardinality estimation. And the first thing to understand is
that query performance is dependent on overall query plan quality. So my plan quality is basically the
join ordering or operation ordering and then also the actual physical
algorithms that I select for a plan. And those are extremely important
for overall performance and it’s heavily dependent on cardinality
estimation, or CE for short. And the idea here is that I want to
know the number of rows that might be flowing through the actual query. And if I estimated properly, then I
can do things like properly select to join, properly order
the actual operations and so on. So, the CE is actually in
a very involved process and we have some statistical
calculations that we perform to get cardinality estimation, and we also
go through a few assumptions, and sometimes we get it wrong. And so hence, we have this
new set of features around Adaptive Query Processing
that I’ll talk about today. So, in terms of risks
of misestimation. So let’s say,
I get the estimate wrong. I’m estimating 100 rows and
I get a half a million rows. What are some of the consequences
of that misestimation? So first of all, you’re gonna get
a plan that might not be appropriate for the actual task at hand and then
you might see situations where your query doesn’t perform
as well as it should or you’re using excessive resources. So I’m using excessive CPU,
memory, IO and so on. And then also in terms of overall
performance, if you look here, you have reduced throughput and
concurrency. You might think, well,
this only affects my one query. But if I’m asking for
too much memory, let’s say, I’m asking for 1 gig and they
end up only using 2 meg of that, you still will have that 1 gig for
your request for its duration and you might be gating with other
requests that also need 1 gig. And the whole idea here is if you’re
asking for too much, we don’t give it back and you can therefore slow
down overall query concurrency. And then what other cost or risk of misestimation is you
might have to refactor your code. So in the example of an MSTVF or multi-statement table-valued
function, you might need to rewrite it to
an inline table-valued function. You might need to put it
into a stored procedure, but the whole idea is that sometimes
you might have to work around the misestimates that we’re making. So, in terms of an example. So we have a actual
SQL statement here and I’m selecting from a Fact
table under a Dimension table. And I’m also joining to
a multi-statement table-valued function or MSTVF for short and
developers tend to like MSTVFs or functions in general,
because I can reuse them. I can encapsulate code in one spot. However, from a DBA prospective,
you might find that it’s not a good thing, because there’s a fixed
estimate that we use for MSTVFs and I’ll show you an example
plan right here. So this is a subset of the plan and
you see here that there is a table scan for an MSTVF, and
we’re estimating 100 rows. So you see in yellow, estimated
rows of 100 versus half a million rows for actual rows
falling through the plan. And because we’re making this 100
row estimate, you’ll see downstream we’re making decisions that likely
aren’t going to be good for overall performance. So we have a nested loop operation
and that’s just 100 row assumption, but we have half a million
rows flowing through that and we also have things like
memory grant spills. So we’re asking for a memory grant
with the assumption that we have a 100 rows that we
need through that plan. But with a half a million,
we need much more memory. So now, we’re spilling to disk and
then performance suffers. So, with SQL Server vNext,
we’re introducing a new feature family called
Adaptive Query Processing and the first feature I’ll talk
about is interleaved execution. So I’ve already mentioned MSTVFs and
what we’re going to do is for the first time, we’re breaking the
pipeline between query optimization and execution and we’re
allowing an iterative approach. So if we see a query
that has an MSTVF in it, we will optimize up to
the point of that MSTVF. We’ll execute it, we’ll grab the
cardinality estimate, we’ll revise the downstream optimization process
to then use that new estimate. And the idea here is that we’ll get
a better quality plan based on that informed decision. Second area is batch mode
memory grant feedback and the concept here is that I might
have a query that spills to disk or I might have a query that is
taking too much memory, and I’m only using a small
portion of it. And if we find that this happens,
we will update the cache plan memory grant information, so
that consecutive executions can then benefit from that
adjusted memory and we’ll keep adjusting until we get it
right and then leave it where it is. And then the third is
batch-mode adaptive joins and the concept behind this is I have
a build input, and I’m making an assumption about a join
algorithm that might be optimal. But if I don’t exceed
a certain threshold, then I’m going to go ahead and use a nested loop operation
instead of a hash match operation. So the idea here is if
it’s a small result set, let’s go with nested loop,
large, let’s go with hash join. So with that, let me demonstrate all three of the
Adaptive Query Processing features. I’m going to start off by setting
the wideworldImportersDW database to Compatibility_Level=130. So, you can see the prior
behavior of an MSTVFs and then show you Compatibility_Level
140 with Interleaved Execution. So this query right here,
it’s similar to what you just saw. I’m selecting from fact order and
joining to stock item. Let’s go ahead and execute this,
and I’m going to include the actual execution plan, and
this is the prior state. So, this is what developers and
DBAs are used to. It might take, I think up
to ten seconds to run this. In this case, we had 6 seconds,
144,000 rows. And if we look at the execution
plan, a few noteworthy areas. The first is Table Scan. So if I hover over this Table Scan,
it’s actually the Table Scan for the MSTVF. And you can see here that we have
estimated number of rows 100 versus actual number of rows,
half a million or so. Now the second area is we have
two different warning symbols, so you see a warning symbol here and
here. And if I hover over
the warning symbols, you can actually see that
we are spilling to disc. So you see our warnings right here
and then operator used tempdb to spill data, and you see spill
level 2 with 1 spilled thread. Now if I hover over the Select, you can also see that we have
a Memory Grand of 4 meg. And again, this is all based on the
assumption that 100 rows are flowing through the plan. So even though we end up
having half a million, we don’t adapt, we don’t correct,
you stop with that plan. Now, let’s go ahead and change
the Compatibility_Level to 140. So I’m gonna clear the cache,
set it to 140 and then let’s re-execute
this query again. And this time, by default, you’re going to have Interleaved
Execution working out of the box. There’s not an option that
you have to set for this. So if we look at the Execution plan, this time you see that we have
the Table Scan has been relocated. So you see that it’s now on
the outer position and you also see, if you hover over Table Scan,
that we have a half a million rows estimated versus the actual
half a million rows estimated. So it’s exactly the same,
because we execute it and then we take that updated estimate
which really isn’t an estimate, it’s an actual, and we apply
it to the downstream subtree. And then other options or
other observations, you see there’s a hash join, another
hash join, no warning symbols. And if you hover over the Select, you’re gonna see that we have
a Memory Grant of 60 meg. And so we had an increased Memory
Grant, because now we’re making the proper assumption about
rows flowing through the tree. So for the second demo, I’m going to show
Batch-Mode Memory Grant Feedback. And I’m gonna go ahead and just
clear the cache and I’m going to create a new stored procedure
called Fact Order by Lineage Key. And I’m going to go ahead and
execute this, knowing that for the lineage key that I’m sending for this query that no
rows will be returned. So I’m executing this,
I get no rows. If you see my plan, it’s based
on a very low row assumption. And now, I’m going to actually
execute based on a value that I know will return all rows. So in this case, the lineage key
of 9 is every row in that table. So, what just happened here? I have a stored procedure and
I’m sniffing a parameter. And on the first execution,
it’s based on 8. And so I’m doing a skinny
plan execution and then I follow it up with a cash
plan that I’m going to reuse with a value that
returns all the rows. And so if you look at the execution
plan you’re gonna see a few problems. So, you see I have three
different warnings here. So a Hash Match warning,
Sort warning, we have a warning that
the Selected self. And if I hover over the sort, you can see that we have
a warning right here. Operator used tempdb to spill data
during execution, spill level 8 and 1 spilled thread. So, we didn’t ask for
enough memory because we were basing it on our original skinny
plan with a lineage ID of 8. And if you look at
the Select operator, you can see that our
Memory Grant is 5 meg. Now with memory grant feedback,
we can help consecutive executions. So the first execution suffered,
because we were using parameters sensitivity issue plan with
the value of 8 as an assumption, but let’s go ahead and
reexecute this query again. And keep in mind with memory grant
feedback what we’ll do is we’ll modify the cache plan, so that
the memory amount gets increased or decreased based on what we need. And if you look at
the Execution plan now, you see that we have no warnings. We don’t have that warning on Hash,
Sort or Select. And if we look at our Select now,
our cache plan asks for 176 meg. So, a big improvement and the performance then
is helped accordingly. Now for the third demo,
I’m going to clear the cache again. And I’m going to execute a stored
procedure that actually runs some trace flags
behind the scenes and this is because this
is a private build. But once this goes into general
availability, SQL Server vNext, you just need to have
Compatibility_Level 140 and I’m gonna include live query stats,
so that you can see the rows flow
through the different branches. So, I have the query, I’m joining
[Fact] [Order] to [Dimension] [Stock Item] and
I have a Quantity equals 360. And if we execute this,
we’re gonna see a new operator. This is the Adaptive Join
operator and you’re gonna see three
different branches. The first branch is our build phase. So, we’re taking an input and we’re
scanning all rows in that input. And it’s part of our build
phase of the hash operation. And then we have an optional
branch for the probe phase, and an optional branch for the Clustered
Index Seek nested loop phase. Now, if the row
threshold is exceeded, I will go ahead with a full
hash joint operation. So, we can see here that
this branch actually has rows flowing through it. So, 672 of 672 and
you see that this branch for the nested loop has a 0 of 336. Now let’s go ahead and I’m gonna make sure I didn’t delete
any rows with quality of 361 and I’m going to do select top 5, insert
5 rows with that value of 361. And now,
I’m going to rerun this query. But this time,using a value of 361
but we know we’ll get 5 rows back. And this time, if we actually
hover over Adaptive Join, you’re gonna see a few
different new attributes. So we have Actual Join Types,
Nested Loops, Estimated Join Type, HashMatch. We also have this
Adaptive Threshold Rows. So 49 rows, if we exceed that
threshold greater than equal, we’ll use the hash operation. If we don’t exceed it for
the built phase, let me go ahead and use a nested loop operation. And if you look at the plan,
you see that this time, this branch is not used. We see 0 of 672 rows passed. But this time, our nested loop,
actually has rows flow through it. So we have the Clustered Index Seek, 5 of 99 which means that we reverted
to a nested loop operation. Because with the low number of rows,
it’s more optimal for a nested loop. Thank you for watching the demo. I hope you have an opportunity
to download SQL Server vNext and then also look at
Adaptive Query Processing features. Thanks for watching. [MUSIC]

Leave a Reply

Your email address will not be published. Required fields are marked *