[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]