>> Learn about the best practices for
loading data into Azure SQL,
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 Ribeiro,
a Program Manager on the Azure SQL team.
Denzil, thanks so much for coming on the show again.
>> Thank you Anna. Always a pleasure.
>> Yeah, awesome. It's always a pleasure to have you on.
You always have the coolest demos and we always learn so much.
Today we're going to be talking
about data loading, best practices.
I know you have a lot planned.
Let's just get right into it.
I wanted to start by asking you if you had any top of
mind thoughts about data loading things we should think about.
>> Sure. Yeah, absolutely.
Folks who are used to SQL Server on-premise may have
simple recovery model and things of
that nature when you're talking about Azure SQL db.
Azure SQL db is always in full recovery model and to guarantee,
actually we do have log generation limits,
and the limits are based generally
on the service objective and tier.
In fact, the limits are queriable from a DMV,
you have the primary max log rate,
which gives you what's the limit for
the SQL db in question or Azure SQL db in question.
Now all the demos is subsequently,
I'm doing on the hyperscale tier.
Hyperscale, what's unique is the log limit is
a 100MB per second irrespective of
the compute size where there is two core,
four core, or eight core.
But note that you're not going to be able to drive a 100MB
per second log generation rate if you use just a two core,
because either the bottleneck will be CPU and what we log.
When we reach these log limits,
we will see a wave type called LOG_RATE GOVERNOR,
which shows up in weight types of SQL servers.
Since this log generation is a commodity in a way,
data compression helps maximize
this log generation rate that you can ingest.
Then other best practices
include things like for large tables if you're
talking to Window table's partition them because
maintenance becomes significantly easier.
Things like archiving a partition,
truncating a partition, rebuilding
an index for a partition, so on, so forth.
We'll see row-store versus column-store,
but remember that the choice
of whether you choose a B-tree, or row-store,
or column-store is not just based on data loading,
it's based on a workload patterns,
and if that corresponding row-store,
column-store is right for your workload.
>> These are some good tips.
I know you have some scenarios to help us
walk through because there's a lot of
different things to take into consideration,
not necessarily like the fastest loading possible.
It'll be awesome, to just hop right into the demo.
>> Absolutely. I'm going to use Spark for this demo,
though the concepts are pretty relevant to ADF,
or ADF Data Flows,
or other ETL mechanism.
They're not unique to spark,
it just is easier for me
to demo with the options clearly documented here.
The first thing we're going do is actually load into a heap.
This is a heap. On a heap, what's unique is you can specify padlock
equal to true to allow multiple parallel threads to load.
Here I'm loading actually about 6.5 gigs of data.
I'm going to launch this so that while it's running,
we can go through this scenario.
Then we have a batch size,
commit batch size of a 100K rules.
This is launched, it read the data from
the lake and it's actually running 32 parallel tasks.
While we do this,
we will actually look at a couple of things.
We look at the max log rate.
We talked about that DMV.
We'll see that for hyperscale tier it is set to 105MB per second.
If you look at the results here.
That's for the hyperscale tier.
Now we are loading into a heap and you'd
see that we have 16 or 32 parallel loads going on.
This wave type LOG_RATE GOVERNOR
actually indicates that we are hitting that limit.
There are two ways to look at it,
and we look at this from a DMV perspective.
The DMV, you can look at for log generation rate,
is this DM resource governor workload group cx?
We can take the delta log bytes
that are recorded for a particular duration.
This is just math, converting it to MB.
We'll see that over the last couple of minutes,
couple seconds if you will,
we're at 98 megs log generation rate,
which is very close to the gap.
Again, doing this from a dashboard perspective,
we'll see a couple of things.
We see that our CPU is about 40 percent.
These are [inaudible] counters charted out by
telegraph and using a Grafana dashboard.
If you see these averages of dried about the threshold,
it toggles from 95-105MB.
That's the log generation rate on a heap.
If we wait long enough,
it should complete in about two minutes.
This should be completing very shortly.
We see that this took 1.9 minutes here and
we'll summarize those results. That's a heap.
But remember that with a heap,
you still before you actually consume the data,
you likely are going to create a clustered index.
Set of indexes, because your query is on
a heap otherwise are all going to result in scans,
which is not necessarily efficient.
Let's look at a second type of index,
which is a column-store index.
This is more used for analytics.
Again, I'm going to fire this workload real quick.
Then we'll discuss parallel loading into column-store,
actually you don't want to specify tab lock equal to true.
Tab block equal true is only for a heap.
If you do, it'll serialize
each thread and they'll block each other.
The other thing is column-store data's stored as segments,
we want a batch size greater than a 102,400 if possible.
Because then the data is compressed before it's ingested.
The segments themselves actually
are compressed before it's written to the log.
Closer to a million,
anything over a 102,400 is good from a column-store perspective.
If you look at log generation rate here,
same thing like we did before.
We do not see any weight taps anymore.
You see the weight type is null,
though we have 32 bulk and so its running.
Looking at the dashboard,
we're going to see some different characteristics.
I'm going to refresh this dashboard,
first is we see on 16 core VM,
more than 16 threads inserting a CPU is close to a 100 percent.
I think I used a bigger cluster than I did in
my prior test from a Spark perspective.
But you see CPU is much higher than before.
Then I have logged bytes flushed,
which is significantly less than before.
Looking at log by flash you see peaks and valleys,
but it's not consistently 95 Mbps.
>> Anna, you had a question.
>> Now I was just saying wow, yeah.
>> Here's CPU is my bottleneck at 16 cores,
but I could invariably go to 32 cores or
80 cores and then optimize on this log generation rate.
Because even at that,
I'm not necessarily going to have sustained 100 Mbps.
This doesn't mean that time to
load is fundamentally going to improve.
In fact, the time to load will be a little slow,
but it loads into compress row groups.
You'll see directly if I look at the column store metadata,
which is this row group physical stats,
you never see uncompressed row groups.
If you had to use a batch size less than 102,400,
this would land in the Delta store part,
which is a row store part of columns too and it would be then
compressed by a background thread
as those groups fill up to a million rows.
But that ingest actually works just like a B-tree or Heap
where it's not into
compress row groups directly so you would see
a log generation rate closer to 100.
Maybe I could demo that,
but for time, I'm not going to demo that.
I will show the results of what that looks
like from the results perspective.
The third scenario we're going to look is that last scenario
where your base data is a B-tree.
So it's not a Heap, is not column store,
but it's a clustered index.
There are several variations of this.
This could be compressed, row compressed,
space compressed, or not compressed.
The first thing I'm doing is I'm going
to ingest this data and in this case,
I'm not sorting the data or anything of that sort.
I am just going to start this.
And again, because this is not column store,
I'm using 100K batch size,
100,000 rows per batch.
Then since it's not a Heap,
I'm using tab lock equal to false.
Let's look at some diagnostics from the SQL side here.
This is going to take forever because now each thread,
there are 32 threads,
is inserting conflicting ranges because I have not
sorted in the cluster key
and you see a ton of contention on locks.
We see a lot of blocking sessions.
You see lock weight types and you see the pages in question.
I am actually going to cancel this because this,
we'll be waiting for a while otherwise.
What do I do as,
if I want to actually save time
from ingesting the heap and building a clustered index?
The alternative I can do here is I can sort the data on source,
which I've already done,
and then ingest it again into the same table.
I have my clustered column store table,
and let me just make sure I do.
I am actually going to ingest this data into this.
You'll see the Spark job actually will have multiple phases.
The first part is actually each worker that
picks up rows is going to sort those rows and
there's a little bit more work on
the client side because of the sort that I've
specified here before the ingest is actually done.
You'll see a couple of jobs
here and this is not actually ingesting yet.
The first parts are actually sorting on the client side.
There is a little more work on the client side,
but it's well worth it as we look at the results.
As soon as these two stages are done,
the bulk insert will start.
This is roughly around 6.5 or 6.8 gigs of data,
around 60 million rows.
Now, we got to the ingest part where we actually
running 32 bulk inserts.
We should see request show
that we have bulk inserting data and we don't
have all that bottleneck that we saw with locks.
We look at ingest rate now.
The log generation rate is not
quite going to be what column store
was because that was compressed row groups.
It's going to be significantly higher because
it's still a clustered index.
We have not applied compression necessarily.
So you ingest rate is much more.
It's closer to a Heap.
But the advantage of this is I'm not going to have
to build the index after my initial ingest into the Heap.
Clustered index is still great for workloads that require
singleton lookups and require more of
the row format than analytical workloads.
The choice depends on the workload.
You see ingest rate is close to the cap,
and this will finish also in a couple minutes.
Of course, this had a little additional time
to sort the data on the client.
But if we didn't do that,
then we would hit all the lock contention that we did.
So we'll come back to this.
You'll see this in a little bit.
But I will flip to the results pane
of these and a few other iterations.
Hopefully this will showcase some differences,
if you will, of things that I've
demoed and some things that I've not.
Here I have source data of 60 million rows,
roughly 6.5 gigs of data.
In my test actually it was 16 parallel streams.
Now it's 32 balanced streams so
these results are a little skewed from that perspective,
I didn't realize I spun up a logic Spark cluster,
but this should give you relative differences for sure.
A couple of things you will realize that it increase
CPU when you compress because compression takes some CPU,
and there's a reduction in log generation rate when compressing.
Then when loading we have talked about.
You have the Heap, Heap is the
fastest if you look at by duration.
Everything else is slower than the Heap.
But what do you have to consider when you're loading to Heap?
Is the indexes you will create post load
actually matter because the time has to
be accounted for because
data is not ready for
your application until you create those indexes.
You see that Heap is capping
out almost at the max and hits throttling.
From a log limit perspective,
it's the fastest, has about the lowest CPU.
The second set of rows we look at is column store related loading.
One was with that batch size of a million and one was with
the batch size of less than 102,400,
which lands up in Delta store,
which is not compressed.
If you look at this row,
the log generation rate is almost close to the Heap
because it's not loading in compressed row groups.
If you look at the one with compression directly is
just 22 Mbps versus 94 Mbps.
But the CPU trade-off is higher because
it's ingesting and compressed row groups.
But the Delta is not significantly more
really and the data is ready to use.
For analytics the column store index is created.
Then the last one but not the least,
is clustered index where we have two things to consider here.
One is, with this sorted input and not.
If you don't sort the input,
all threads will block and the time is by far the largest.
You have lock contention.
CPU is very low because everyone's blocked.
But the time is, this is not really a practical scenario.
If you're using a clustered index,
then sought the data on the client,
and then you have coupled variations of
compression with no compression,
row and page compression,
where you see there's a slight difference in log generation rate
and the size of data as it lands.
Because then your querying will become less IO-intensive,
but there's a trade off in CPU.
I hope this gives you a good view of different table types and
the batch sizes and the trade-off
between additional CPU for saving in log generation rate.
You can make some decisions based on your workload and scenarios
on what type of index,
batch size, and trade-offs you can make.
>> Awesome. I mean, you've shared so many great tips with us.
Thank you so much for coming on the show.
I have one question not to put you on the spot,
but what's a mistake that you see a lot of customers make in
this space that you wish that you
could share with everyone, if there is one?
>> Yeah. There are several actually.
Partitioning is probably a least use
or a very underused feature for large billion row tables.
Partitioning has some complexity in
terms of having to maintain partitions.
But the benefits outweigh the complexity.
Talk about several billion row table.
Partitioning enables you to rebuild at partition level and do
a bunch of maintenance at
the partition level enable things like incremental statistics.
That's one thing which is very underused.
The second probably most common thing is, like I said,
folks who ingest large amounts into Heap and then create
a clustered index and don't
account for that index creation time in loads.
Those are probably two.
If I see the third one is
the column row batch size thing
and the under-use of compression in general.
Compression does help in many cases.
>> Awesome, cool. You've actually covered a lot of
those things in this video, so that's good.
We're getting it out there and hopefully this
is going to help a lot of folks
load their data in
a more efficient way so that they can get to using it.
So again, thanks so much Denzil for coming out on the show.
>> Absolutely.
>> To our viewers, thanks for watching. Give us a like.
Leave us a comment and let us know how you're loading data into
Azure SQL and we hope to see you next time on Data Exposed.
>> Thank you, Anna.
[MUSIC]