>> Learn how to improve performance of
bulk loading data using
Query Store hints this week on Data Exposed.
[MUSIC]
>> Hi, I'm Anna Hoffman
and welcome to this episode of Data Exposed.
Today I'm joined by Denzil,
a Program Manager on the Azure SQL team.
Denzil, thanks so much for joining us today.
Can you start by telling us a little bit about what you do?
>> Sure. I'm a Program Manager in the Hyperscale team.
I work with customers to determine what's the roadmap,
look at compete and
features that we're going to release and make sure
they solve the business scenarios the customers want us to solve.
>> Awesome. Cool. We are happy to have you on the show today.
Today we're going to be talking about Query Store hints,
specifically how you can use them to optimize your data loading.
Can you maybe start us off by
telling us a little bit more about this scenario?
>> Sure. Data loading is a common scenario and now with
Hyperscale supporting larger sizes of databases,
there are customers who use ETL to load data.
Loading data is unique in many ways.
Optimizing data load depends on a bunch of factors.
Today we're going to look at
a specific scenario of memory grants and where
Query Store hints can help us in scenarios that we
cannot change our ETL process yet we need to optimize it.
>> Got it. Then just a quick background for
folks like myself that might not know a ton about memory grant,
can you give us some more information on what those actually are?
>> Sure. When the query executes,
it needs to request some memory that it
reserves for joins and sorts and query execution.
This is not memory for
the data pages themselves, which is buffer pool.
This grant is affected by parallelism,
how many CPUs and what parallel plan you have,
the resources in the system.
It's set to a percentage which
by default is 25 percent of your default workload group.
The reason the reservation is done is you don't want
the query to run out of memory during execution,
so we reserve upfront.
When there are many queries that require large grants,
these wait until there's available memory on the system,
and that's what we're going to look at today.
>> Awesome. Cool. We're also going to be
using something called Query Store hints,
which is actually,
it is pretty new for Azure SQL Database, is that right?
>> That's correct.
>> Awesome.
>> Query Store hints isn't there on on-premise SQL Servers yet,
it's on Azure SQL first.
In this scenario, we are going to actually load data via Spark.
We have an eight-node cluster in Spark
and I'm loading from Data Lake.
You'll see a bunch of files that I'm loading.
I'm loading about 9.6 gigs in total.
The first frame I've already read in
this dataset so as to not calculate wait time,
and I'm loading into a table which is a column store table.
For column store, we optimize the batch load so
that the load goes into compressed row groups directly.
The max size of a row group is around a million,
so that's what we're giving as a batch size.
Then we're just appending to the table.
For column store, we don't want to specify
a tab lock unlike when you load to a heap.
These are the only options that are relevant,
and then I'm using Spark to actually kick off my load.
There I'm kicking off my load into the table.
This is an eighth node Spark cluster,
each with four cores.
You have a total of 32 tasks which are loading.
As you can see here, there are 32 tasks which are loading data.
These files are partitioned by
the tasks and each of them are loading data.
Let's come back to
the SQL Server side of the house and see how this looks.
I'm loading into this table called Customer Ratings.
You can see it's a pretty wide table.
I have 100 columns or so,
which is not uncommon in the data mart scenario.
I have a column store index here,
which is the clustered column store.
The base data is actually compressed into row groups and segments,
so it's stored by column.
If I look at the running requests,
and this is just a store procedure I wrote on
top of DM exec requests.
The key part is we're going to look at a lot of bulk inserts.
We'll see 32 of these bulk inserts here.
More interestingly, let's give it
a little bit of time and look at the wait types.
We see quite a few of these resource semaphore wait types.
As you can see, this is the wait time.
This time if I run it again,
it keeps increasing because there's no memory available to grant.
This is the resource semaphore wait
time I was talking about on the slide.
How do we look at these grants,
or what are these grants,
and how do we look at how much
is requested and how much is granted?
We have two DMVs here,
one is this dm_exec_query_memory_grants.
If we run this,
we see a session ID,
the degree of parallelism,
and we see these couple columns.
You see a request time and a grant time.
The ones with the grant time is populated had been granted,
the ones which are null are waiting.
Then you have a requested memory, granted memory,
and then you have a used memory,
which I'll show you in a second,
so whatever is requested is granted in this case.
But we can see from the used memory,
much less is used than has actually granted.
You have about a three-gig grant and just about 280 MB used.
This grant is based on an estimation heuristics,
and there are particular schemas where
estimation may not be accurate and this is one of them.
Then if we look at the semaphore itself and
this pool ID is the workload group for default user queries,
I see that 12 of these requests have been
granted and 20 of these are waiting for a grant.
You get other heuristics of what's the available memory and what's
the granted memory and what's the used memory for that pool,
but this is the important number which shows you
all the threads which are waiting on a memory grant.
Similarly, you can look at widths
that snapshots and I'll show dashboard in a second.
These are diffs between two snapshots.
I'm running it again.
Maybe the other queries have already got past that grant.
But we can look at the dashboard to
see if there was a widths on a grant or not.
This is telegraph on monitoring SQL Server.
I can see in my width stats here.
I have this resource semaphore.
This cumulative wait time per seconds.
There's nine seconds of wait time per elapsed second.
Which indicates there's a wait.
Queries are being throttled on that.
How do I identify these queries?
I'm going to actually just look at bulking.
There's two ways to do it.
One is this more comprehensive query which looks at
run-time stats and orders it by the query.
Max memory used which is recorded in Query Store.
I can look at that and I see that the statement is "Insert bulk".
Well, insert bulk is not
really a statement that you can issue from SQL.
Usually the statement's bulk insert,
but insert bulk is issued by any application that is
actually calling the SQL bulk copy APIs.
You don't have direct control over the statement.
It is the API which actually
generates this "insert bulk" statement.
As you can see, the average query memory used is quite high.
The important part is I need this query ID.
That's all I actually need.
In this case, it's 32.
What will I do with this 32?
I am actually going to use a hint which I otherwise
could use on "Select queries and such."
Then force it via this hints to this query ID which is 32.
I force this hint,
and then I can look at hints from this "Hints view" and see that
this query ID 32 now has this hint forced.
Now in the interest of time, we did finish,
it was about five minutes and such.
Now that we've applied the Query Store hint,
let's rerun this again
and look at the same set of diagnostics that we did before.
We'll look at requests.
If you look at waits, those resource semaphore
waits that we were looking at before are now gone.
You don't see them anymore.
Because we've lowered the max grant that each query can get.
Similarly, looking at other grant DMVs,
all the queries in question had been granted.
The grant itself is much lower.
It was 3.5 gigs or so,
it's now 500 megs.
Then the used is much closer.
There was enough available memory to grant
all the 32 bulk inserts the memory that they required.
There are no waiters.
All 32 of these had been granted and there are no waiters anymore.
Similarly, if we look at wait stats,
there won't be any waits on resource semaphores.
That just disappears because there aren't any waiters.
One more thing I wanted to identify is via the query plan.
When you have a query plan,
there always is a way to look at memory grants.
This is not the query plan for the bulk insert,
but you can always look at a query plan and
see the memory grant from a query plan perspective as well.
That's another diagnostic besides Query Store and the DMVs to help
identify the queries that are granting a lot of memory.
Last but not the least,
some of the results,
if you look at the difference in results and I'll
switch back to a dashboard that shows this.
The load time now reduces from what
was closer to anywhere from 5-7 minutes,
depending on the run and amount of waiters.
Closer to 2.5 minutes or less from a load time perspective.
So that's a phenomenal increase.
More importantly, even besides the load,
you see usage of resources fundamentally better.
Low generation rate was only 21 megs
per second and goes to 49 megs per second.
CPU goes up because now
you don't have waiters that are more threads
actually utilizing the CPU to compress
the row groups and then of your resource semaphore.
Here was a sample run that I ran before
drops from a weight of 4000 seconds to zero seconds.
Literally to cement this case,
I have a dashboard that compares these two runs.
You will see this is the duration of the waits.
You see these are CPU average,
CPU is about 20 percent.
But as it goes much higher here and much shorter duration.
From the waits perspective,
you see the same thing.
The first part has a resource semaphore waits,
the second part has nothing.
We started bottlenecking on CPU here a little bit.
But you can always scale up if CPU is your bottleneck.
That's an advantage of Query Store hints.
I didn't have to change my app.
I could still apply the hints
and fundamentally improve performance of my data loads.
>> Wow, this is really cool.
That was a really cool demo and I
also learned a lot of different things.
But this has been really useful.
Thanks so much. As we're just getting started with this,
do you have any tips or gotchas that people should be aware of?
>> Look, we're exploring query store hints for sure.
In many of the cases where you have
third party applications where you need to change a query
and a change in query results
in the application vendor having to generate a patch,
it's a quick way for a DBA to
mitigate an issue that happens in
production in a short duration of time.
Query store is key.
Identifying queries and troubleshooting Query Store should
be something all data engineers are very familiar with.
Then hyperscale also enables
this data loading scenarios because
irrespective of compute size, in this case,
I had just 16 core hyperscale database and I loaded
the 6.6 gigs of data
in roughly over two minutes in column store format.
Hyperscale also has this log generation rate not
tied to the size of compute.
It's a great way to move
workloads that are data intensive to hyperscale tier.
>> Awesome. Cool. Well, thanks so
much again for coming on the show.
To our viewers, thanks for joining us.
We will put some links in
the description for you to go learn more and
catch our other videos where we talked about Query Store hints.
Thanks so much for joining us today and we hope
to see you next time on data exposed.
>> Thank you, Anna.
[MUSIC]