>> SQL Server 2022 has a lot of
new capabilities and that does not
exclude built-in query intelligence.
Learn all about the new capabilities,
see a cool demo this week on Data Exposed.
[MUSIC]
>> Hi. I'm Anna Hoffman.
Welcome to this episode of Data Exposed.
We're jumping right back into
the SQL Server 2022 capabilities series with Bob Ward.
Bob, thanks for joining us again.
>> Again. Yeah.
>> Today we're going to be talking about
built-in query intelligence.
I got to start with, what does that even mean?
>> This sounds like a [inaudible],
it's real technology, Anna.
Remember our wheel of power.
We're back to the wheel of power where
the second part of the wheel power.
Let me tell exactly what this means,
going to be working on this technology for a while.
So query tuning and performance
analysis for SQL Server is really expensive and time-consuming,
MSC is a customer of ours that experts in
this area that was looking for
new innovations to make themselves better.
The Query Store is something that's been
a great tool for customers for a while.
We're enhancing it right now in 2022,
turning it on by default for new databases.
We're supporting read replicas now to do
analysis of re-replicas queries, yay.
We're supporting saving go query hints,
light a shape plans without making application changes.
We've lit up some new scenarios
for what we call intelligent query processing.
Getting you faster with no code
changes that really what for intelligence,
that means, in some cases,
using the Query Store.
So the Query Store is a great thing
on its own that we're enhancing in 2022.
But I'd like to show you the story of what we call IQP next gen.
So if you upgrade to SQL Server 2022,
regardless of your compat level,
doesn't matter where you are from previous versions,
we're going to light of some new functionality,
new percentile function,
something called optimized plan forcing,
gets you faster for compilation of your queries.
But if you're on dbcompat 140,
you get this plus will
enhance a concept called memory grant feedback.
We've had that in the server before,
but now we've improved the algorithm by default,
and now we're going to persist it finally.
We're going to use the query stores as a way
to store the feedback,
so that if your plan caches evicted or the server restarts,
will still get to use that great functionality.
This is all about self-healing,
just getting faster by looking at feedback in the system.
Therefore dbcompat 160,
we're going to do parameters
sensitive plant optimization that's
going to come into the product as well.
Something called cardinally estimation feedback that's trying to
solve some of these CE model problems for you,
and something called degree of parallelism,
DOP feedback, so that's been one.
It's really been playing customers for a while.
All of those represent automation into the system,
built-in query intelligence where
the query processor and in some cases the Query Store,
you can see here,
are the Query Store is being used in some of
these functionalities to make things faster.
Don't forget Anna, all this great technology that we've
built in IQP from previous releases of SQL Server.
Then we have a couple of principles here.
We don't want to make anybody slow, that would be bad.
>> Right.
>> Then here's the cool thing.
All these options you see can be turned off individually.
So if you use dbcompat 160 and you
don't like perimeter sensitive plant optimization,
but you'd like the other two, you can turn that off as well.
You get a database level or a query level.
What I'd like to do is show you
specifically parameter sensitive plant optimization.
That's one that's been a pain for a long time for customers
>> Yeah. That sounds great.
>> I'll do it do like a diagram,
my Bob's crew die.
>> Okay.
>> Let's say that you built Anna a stored procedure in SQL Server,
which took a parameter value of
an integer as a query to use for like a where clause.
You use or the optimizer chooses like a Sikh plan for your query,
because that's really fast, but just a couple of rows.
But I come along and run a stored procedure,
the same one after maybe the plan
was evicted from cache for the first one and I put in a 10.
Well that's got for like a million rows.
So I need a scan and that's fast for me.
Maybe sometimes even with parallelism.
If you come back and run your stored procedure again,
you're also going to get the scale and that is bad.
That's a degradation of performance.
We call those scenarios parameters
sniffing or parameter sensitive plans.
So in 2022, so we're going to do?
Same scenario comes through here as well,
the same type of situation.
Now when you come back and run your procedure,
we're going to fork and give each execution its own plan.
Now it's not a plan per user,
it's more like a bucket or an aggregation scenario,
and we don't bloat the plan cache with too many of these things.
But now for the first time ever,
multiple plans for the same stored procedure can show up in cache.
It's really a groundbreaking type type technology.
Would you like to see this actually?
>> Yeah.
>> Let's see the tech. So I
built this demonstration. Let me show it to you.
I've taken Wide World Importers in
2022 and I'm credit a stored procedure where
I pass in a parameter value
to go get like suppliers for stock items.
So I took Wide World Importers, I create this proc.
So you can see already though,
I might be creating a scenario where
depending on the value of the supplier
is going to depend on what kind of query plan
is provided for a given query execution.
So let's see an example of that.
So I'm right here.
I'm going to supply a value of a two,
and I'm going to set on a timer so we can
see real detailed timings of the query,
and I'm going to run it and I'm going to see what
the plan looks like after I execute this.
Now it happens just in a matter of less than a second,
and it's using that index seek like we talked about,
to go after just a few rows.
If you look at the timings down here,
there's happens like in 30 milliseconds to run.
So it's very fast.
User pretty happy with this step execution.
On the right-hand side, I'm going to simulate
plan cache eviction,
run the same proc with a value of four.
Now in this particular case,
time-lapse us a little bit because this
takes awhile because for a four,
there's a bunch of rows needed.
In fact, I need a clustered index scan
using parallelism here to get back this result.
Now what if I run the first one like I showed in the diagram here?
It looks like it runs very fast.
In fact, you look at the plan here,
notice it using a scan,
but it seems like it was super quick,
but go look at the timings.
It is 10 times slower.
If you're running a workload with
a lot of these, this could be bad.
Let's see a workload example
to show you really the difference here.
I love perfmon to show these examples.
I'm using batch requests per second and processor time.
Batch requests to show you the throughput and
processor time to show you overall the impact of the system.
I've got a workload example to show
you that first parameter value.
But there were like 10 users at
one time all running the same query.
It happens in a couple of seconds,
and it's really great performance and
my throughput is great, low CPU time.
Let's run the scan now.
You can see here the scans going to finish in a few seconds,
but it's simulating the same problems before,
plan was evicted from cache,
so what I'd like to do is run the Sikh again to
say isn't going to have the same time as before.
Well, no. Remember that thing of like 10 times slower,
it gets exacerbated running 10 users at a time.
Look at the degradation in performance of
my workload and look at the huge increase in CPU.
It's because everybody's trying to scan
the index in parallel for this workload.
I'm trying to think to myself, what do I do here?
Why I always have to kill this query or
kill this workload because it's taking so long to run?
Now let's go over in Management Studio to
see why are we seeing this problem,
like what is really going on here behind the scenes?
If you see here, I'm running a count of suppliers from this table,
and you can see the skew and data below.
For supplier id of say two,
I only got like three rows,
supply idea of like four,
I have four million rows.
So you can see I need multiple plan to give
the best performance for different types of
parameter values. So how do I solve this?
I'm going to go and just set compat level to 160 for the database,
I'm going to clear the procedure caching
Query Store to reset the workload.
But I'm going to run the same tests that I did
before and observe performance and perform.
Let me restart perfmon again you can
kind of see the performance comparison between the two runs.
Let's run the Sikh query again.
It's going to happen and just like
two seconds here, like it did before.
Then I'll go run the scan as well.
Again, I'm simulating that plan cache eviction running the scan.
Now, what's going to happen is when I
run the workload the next time for the seque,
we're going to get a new plan,
we're going to go get the seque plan and I'm going to
see what I hope is the same performance.
Yeah, a couple of seconds again.
>> Right.
>> Yeah. And look at perfmon now this is
the part that really just shows it visually.
See the performance is the same as before.
It's exact same type of consistent performance I'm looking for.
So this is a kind of type technology that's really groundbreaking.
Now I want to dive in a little bit more what's
going on behind the scenes using the Query Store,
even though it's not required to use
perimeter sets of plan optimization.
I want to show you the insights behind it.
You can see I've got two plans.
I've got one for scan,
I've got one verse Seek.
But for Query Store, it almost looks like there's two queries.
We're using a concept called a variant.
What's really happening behind the scenes,
I'll show you to some queries here.
I actually do have
multiple query texts stored
in the Query Store for the same store procedure,
and I have a DNV that allows me to go look at this information.
If you can scroll over here,
you can see the queries that's appended with this new text
called plan per value with something called a query variant ID.
It has the object ID and the store procedure as well.
Both of these are in the Query Store, but here's the beauty of it.
If you keeps scrolling across little bit here, you can see,
even though there are two texts for this,
they hash to the same query hash value.
Now even though these are really
the same query and they appear twice,
I can use query hash to compare and say, "Okay,
what was the performance of this query over time?"
Here's the different plans for each of those.
>> Right.
>> I can dive into the XML plan as
well on people like to go look at that.
If you're into the XML plan,
you can go to the details and see here
the same query variant text.
You see this over to the right, right here,
that plan per value option.
You also see the object id in there as well
to see what's stored procedure this belong to.
I can run another query to go actually find out in
the Query Store what was the stored procedure related to this?
In fact, you see here the object name
shows up for both of those variants because we have
this concept called a dispatcher that's like
the parent plan as part of the store procedure overall.
Here's the store procedure. That's kind of
a glance and looking at what is
possible for perimeter sensitive plant optimization.
>> Wow, that's pretty cool, a pretty powerful.
And then I also like how you dove into some of those details.
How we can see what's really going on.
>> SQL People want to go behind the scenes and
see what's really going the insights of what happened there.
>> For sure. Yeah. Now I wanted to shift.
You mentioned this DOP thing.
Can you tell us a little bit more about that and what we're doing?
>> Yeah. You know, the pain of
MAXDOP has been around for a long time in SQL Server,
and the whole concept is that SQL Server
uses parallelism many cases for queries,
and it's not necessarily a bad thing,
but I need to control it.
Setting the right value for the database,
the instance, the query,
like how do I do that?
In fact, Anna,
did you know the more degree of parallelism for a query,
the more threads, the more CPUs required,
the less DOP, the less threads.
The problem is though it's a juggling or balancing act.
How do I get parallel efficiency?
In other words, how do I figure out the MINDOP to run
a query to get the best performance but used the less CPU?
>> Right.
>> So we have a feature called DOP feedback built into the engine.
You use dbcompat 160,
you set a database compat level,
and this is urban scenes, how it works.
The query processor will work in conjunction with
the Query Store to go and take a query if it's eligible,
and it lowers the DOP to see,
is it still the same performance but can I get less CPU overhead?
I've been able to use this technology to go from a DOP16 down to
four for a query achieved the same query performance duration,
but free up like 40 percent more CPU for master workers my system.
It's a way for us to use a feedback mechanism
to get you that parallel efficiency,
so that parallelism isn't necessarily bad in your system.
>> Wow. Bob, that's pretty powerful stuff.
>> Well that's why it's called built-in query intelligence.
I mean, it's getting fast with no code changes,
things built into the engine.
Anna, we've worked on Azure SQL together.
This is SQL Edge-to-Cloud.
It's in the query processor,
so it works on Azure SQL anywhere SQL exists.
>> Awesome. What about everything we've seen?
>> I mean, we've packed in a lot.
We've got New Query Store capabilities.
You upgrade to SQL Server
2022 any dbcompat level, you get functionality.
You go to dbcompat like 140,
you get enhanced [inaudible] great feedback.
You go to 160, you get all sorts of
things like parameter sensitive plant optimization,
see feedback and DLP feedback.
We're also bringing all those innovations
we did in previous versions,
so built-in query intelligence with Query Store, postal,
next-generation of Pilcher query processing or some of
the biggest capabilities we have in SQL Server 2022.
>> Awesome. Well Bob, once again,
great to have you on the show.
We learned a lot in the SQL Server 2022 series
so far and it's not over yet.
Viewers, if you'd like this episode,
go ahead and give it a like,
leave us a comment and let us know what you think of
the new built-in query intelligence capabilities.
Check out the links in the description for
all the ways you can get hands-on,
see the DAC, all sorts of stuff.
We hope to see you next time on Data Exposed.
[MUSIC]