>> Learn about some of the new performance capabilities in
SQL Server 2022 this week on Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman,
and welcome to this episode of Data Exposed.
Today, I'm joined by Pedro from the SQL Server team.
Pedro, thanks so much for joining us today.
>> Hi, Anna my pleasure. Thank you for having me.
>> Awesome, it's a pleasure having
you on the show, and I'm excited to
continue our discussion about SQL Server 2022.
Pedro, for folks who haven't been tuning into the series,
I would encourage you to go do so.
But for folks who haven't,
can you tell us a little bit about
what SQL Server 2022 is all about?
>> Yes. If you have watched
Bob Ward's short presentation on the overview of SQL Server 2022,
you've seen that one of the main topics,
and the main areas of investment is
connectivity to Azure and bringing
in the power of Azure to your local database.
However, we are also,
as we've done in previous versions,
investing heavily on additional security features,
performance, and scalability.
Today, I'm here to talk to you about some of the performance
features that we are putting into this new version of SQL Server.
>> Awesome, cool. What are some of those new capabilities?
>> We start with Query Store.
Now Query Store is not new.
But the way we're using Query Store is new.
Query Store was introduced back in SQL Server
2016 as we told you back then,
as a flight recorder for your query performance.
We have a system that gathers
query performance data, and allows you to
have insights over your workloads over time.
Now we are using that to actually
build new capabilities in intelligent query processing.
But for that, we need to clean house a little
bit to make sure, for example,
that Query Store is now enabled by default for new databases,
and that's something that's brand new in SQL Server 2022.
The other thing that's brand new is also we're adding
support for secondary replicas in Query Store.
In other words, until now,
Query Store only collected data from workloads running in
the primary replica of an always-on availability group topology.
Now we're bringing in support to also
collect that query performance data from secondary replicas,
and this together with another feature that's not mentioned here,
which is the ability to have
hinting support directly in Query Store,
will allow us to build
new intelligent query processing scenarios, and
bring better capabilities into the performance space.
>> Awesome, cool. When we
talk about intelligent query processing,
I know it's been a journey.
Can you tell us a little bit about that journey,
and maybe a few of
the highlights that are coming in new to SQL Server 2022?
>> It's a big journey,
as you see on the screen here, the tree
is getting larger, and larger.
We started very timidly back in SQL Server 2017,
as you see highlighted in blue
there with Adaptive Query Processing,
and then we expanded the family of features than
the scenarios that we are tackling in SQL Server 2019,
and Intelligent Query Processing was something we coined back
when bringing in Scalar UDF Inlining, and Batch Mode for Row Store.
This has always been present in Azure SQL's database, because it's
the same database engine that powers
both SQL Server, and Azure SQL database,
and Azure SQL database is also typically some months, or years
ahead of SQL Server in terms of any major new feature.
Now we're bringing in another addition to this tree,
and you see there in white boxes, and bold,
some new features that tackle
new scenarios or existing scenarios I might say
that are new in this automated Intelligent Query Processing firm.
>> Awesome, cool. Just to pick a few,
all of these seem very interesting to me.
But the first one that catches my eye is CE Feedback.
Can you tell me what that is, and how we relate to say,
someone moving from SQL Server 2012?
>> Well, that's an excellent question because if you are moving
from an older version that's reaching end of life next year,
like SQL Server 2012,
then what happens is you're using something
that is usually dubbed as
the old C, or old cardinality estimation model.
Now, when you move to newer versions,
starting in 2014, and above,
we have newer versions of
these cardinality estimation models which are dubbed the new Cs.
What happens is when you leap over versions
from 2012 all the way to 2022,
there is the possibility that in some cases,
that is workload that might regress in terms of performance.
What we're doing here is we've accumulated the use of
experience working with customers
that have tackled these scenarios,
coming from old versions to new versions,
and we are putting those lessons into this feature,
into CE Feedback so that when we
detect that certain types of workloads regress,
we are able to automatically experiment,
try out new, or different model assumptions,
and see if those work better, and if they do,
we'll make them persistent in
Query Store so that they start getting used going forward.
In essence, it's like query tuning completely
automated without you having
to be a Query Performance expert essentially.
>> Wow, very cool.
Moving right along, because I know we want to get to
the demo DOP Feedback,
I'm guessing this has to do with degree of parallelism,
what's the feedback side?
>> Absolutely. What is the feedback side of this?
Now, another classic problem that some SQL Server workloads face,
which is you set
DOP configuration at the server level, or at the database level,
and then depending on whether a query goes parallel, or not,
it will typically reach that degree of parallelism.
However, we know that some queries do better
with less when they execute in less processors,
some queries actually worked better when they have
more processors being attached to the execution of that query,
for example, an analytical query.
What we're able to do here is
starting from that initial degree
of parallelism, that's a default,
whatever you set will detect whether
your queries are running with optimal parallelism,
and we derive that from a certain number of
signals that will go into further detail in the documentation,
and then we're able to see
if it's not using parallelism efficiently,
we can start dropping the graph parallelism for
every single query that enters that space
until it reaches a point where
it's using parallelism more efficiently.
In essence, this means that you have
more efficient use of parallelism in your server,
that means that you can run your workloads in
a much smoother way, and they become more scalable overall.
>> Awesome, very cool.
Another awesome capability a
very quiet, I can't wait to test that.
The other one I wanted to ask now
is in the space of Memory Grant Feedback.
Can you tell us about this new update here?
>> What I'll bring in, so Memory Grant Feedback resolved a lot of
issues with customer workloads that were misusing memory.
That was introduced in SQL Server
2017, and then we completed the scope of the applicability of
that scenario in SQL Server 2019 when we needed to apply to
Row Mode workloads as well so it
applies to every single query potentially.
However, one major piece of feedback we always got is,
if I need to failover,
if I need to restart my SQL Server instance,
all those lessons are gone,
all the feedback that was accrued,
and all the changes, and tweaks that
were done to my queries are just gone.
It has to re-learn again.
Typically during that running phase,
you're not running, let say at
an optimal condition from a memory user's perspective.
What we're bringing in is the ability
to actually persist that feedback,
persist the Memory Grants Feedback values, and lessons.
That means that when you failover,
when you restart, and the query comes back up,
it will reuse the values
for optimal memory grants that were there before.
That takes away that running phase
that reset that behavior that we had before.
>> Awesome. Well, it's always
good to see how all these capabilities,
you're building them basically
based on the feedback that we get from customers.
That's always great to see.
I know there's a few other things on here.
Parameter sensitive plan is really picking my interest,
but I'll let you decide what you want to talk about next.
>> Let's have some jump to that because I have a demo on this one.
Now, parameter sensitive plans are known also by another name,
the name of the, let's say the problem they may generate,
which is parameter sniffing issues.
Now, being sensitive to parameters is something that helps
SQL Server because it uses
those parameters that are incoming for every parameterized query,
such as a stop procedure,
to actually create a query plan that should be optimal
for that specific parameter that's incoming.
However, there is another characteristic
of the SQL engine which is to
cache the first plan that's
created for these parameterized queries.
Now, what this means is if your first execution was not,
let's say the bulk of the executions
you make of a certain type of parameterized query.
That means that the first execution
as a plan it's good for that one,
but may not be good for all the bulk
of the executions that you make of that substitute.
That leads to parameter sniffing issues,
as it's commonly named in the community.
We are optimizing that space to make sure
that that's no longer a problem for the 80 percent phase.
I have them on that, do you like to see that?
>> I would love to see that.
>> Then let's jump into the PSP demo.
What I have here is SQL Server 2022.
You know that it's SQL Server 2022 because if I
do this, and execute,
you'll see that I am indeed running
in SP1 version of SQL Server 2022.
This is just hot off the presses.
I want to show you like we just mentioned,
the parameter sensitive plan scenario.
For this, I'm going to use the database property MLS.
Essentially, this is a database for a real estate company.
That real estate company has
a number of properties in their portfolio,
and they have a number of agents working for them,
and each agent has
their portfolio of properties
they are trying to sell to customers.
In this database,
there is one store procedure called property search by agent.
In essence, let's look at the definition of this store procedure.
What it does is it takes an agent ID, which is an integer,
and then it gets
all the data from
the property table regarding
all the portfolio that that agent has,
like the property ID, the agent ID,
the MLS link ID, and then the listing price,
the zip code where it's at, how many bedrooms,
how many bathrooms,
and there are other properties I'm not bringing in here.
Now, what happens is this being a parameterized query,
depending on the agent ID has a potential of
generating different plans, because depending on the agent ID,
I may have a lot more properties
being returned by the results set than with using other agent IDs.
If I look at the statistics for this plan specifically on
the agent ID column of
the property table using the CCM DV stats histogram,
I'll put the histogram,
I see there are a number of agent IDs.
Those are in this range_high_key column,
and I can see how many rows,
how many properties those agents have.
Now, I see a couple of agents here,
agents 1, and 2.
that have almost half a million properties,
and then there are a few others that have other in the dozens.
If I start scrolling down, I don't see it here,
but I have a few that are in the hundreds.
This right here is
where we're starting to
see a bit of the internals of this feature.
Because we use these histograms
to understand what is the potential in
a certain incoming parameter to potentially generate
widely different plans depending on the number
of rows that would be a return.
What I've done here on the side because
this takes a while, and I've done this before,
is I've executed that store procedure with agentId 2.
Now, agentId 2,
if we go back here,
has approximately five million rows,
that's what I meant, it is approximately five million rows.
If I look at this first execution,
I see that the plan that was generated
was a parallel clustered index scan,
there was a sort, and then this was returned.
If I look at the properties for this specific query,
I can see that it executed in roughly two minutes.
It takes quite a while.
Now, in a classic SQL Server,
what would happen is this will be
the plan that would be used for every single execution.
Even when I use parameters like agentId 9 or 11,
that I only have a handful of properties.
Doing a scan over this very large table would be very inefficient.
Now, because this feature exists PSP feedback,
when I executed the same store procedure
with the parameter ID 4 or 8,
which return a lot less rows,
I can see that the plan is different.
I can see that it's a plan that's much more
attuned to returning three rows in this case,
and to also returning three rows in this other case.
If I go to the other agentId,
it returns nine rows, and it reuses the same plan as agentId 4.
Now, what does all this means?
This means that we have simultaneously in the plan cache,
at least two plans for the same query.
They co-exist in the cache, they're active plans.
Depending on the incoming parameter
that comes in for this repeated query,
we'll use one plan or the other, and we don't have to recompile,
which was a classic technique to handle
this and that are always in the cache.
Now, the proof is in the putting as they say. Let's look here.
If I look at the cached plans,
I can see that I have two plans for the same query text.
This is exactly the same query text
here and one was used one time,
which was the first with the agentId 2,
and then I have another one that was used six times.
Remember that I have
three executions of each of these incoming parameters 4 and 8.
If you want to see further proof,
let's go further parameters.
I can actually look also at the query stats,
so performance stats for
these queries, and why it's so important to
have these active plans being simultaneously in the cache.
Because if I look here,
so the same six executions,
one execution of the other one.
I see two things.
I see one, the total worker time,
in other words, the CPU time,
one execution of the store procedure with agentId 2
took many times more CPU time
than six executions of the other plan.
When I say that these are different plans for the same query,
this is what you can see,
you have the same query hash.
The query hash is, let's say that fingerprint that
uniquely identifies this query in your server.
But they have different query plan hashes.
They have different query plans, cached,
and active to return a different result set.
I can see the last time this query
is executed with this plan so the one
that's faster returned nine rows,
the one with agentId 2 to return almost five million.
There you have it. We have
essentially for the 80 percent case solved,
this very tough case of parameter sensitivity.
>> Awesome. Pedro, this is really cool to see,
and I loved to see in a real-world example like this how
parameters sensitive plan optimization is going to work.
You've shown us so much, and it's coming in with SQL Server 2022.
I just want to thank you for coming on the show for our viewers,
I want to thank you for tuning in.
We're going to put links in the description.
You go try this out yourself apply for the Early Adopter Program.
See other information related to this.
If you like this video,
go ahead and give it a like subscribe to our channel,
leave us a comment, and we hope to see you
next time on Data Exposed.
>> Thank you, everyone.
[MUSIC]