>> You've probably heard of OLTP and OLAP type of workloads.
But what actually happens in the real world and
how does Azure SQL and SQL Server help?
Learn more in this episode of Data Exposed.
[MUSIC]
Hi. I'm Anna Hoffman,
welcome to this episode of Data Exposed.
Today I'm joined once again by Silvano,
a Program Manager on the Azure Data Team.
Thanks for joining us today.
>> Thanks a lot for having me, Anna.
>> Always a pleasure. Today we're going to be talking about HTAP,
which might be new to some of you,
might be familiar to some of you,
and how it relates to Azure SQL.
But before we get into that, Silvano,
I was wondering if you could walk us through what
the different type of workloads we see people using are.
>> Yeah, absolutely. Traditionally speaking,
we've been discussing about the some of
the common types of
workloads in relational database or in general.
We are well aware of the online transactional processing or OLTP,
where our beta model is actually higher normalized in
order to optimize our data design for concurrency,
for data modification operations,
insertions and updates, and so on.
We have a very selected queries typically that are
supported by indexes for basically fast retrieval,
so to speed up applications.
We're actually enforcing the consistency through some constraints,
domain, referential integrity, and such.
On the other hand, we typically refer to
online analytical processing where we are going to support
the typically reporting and analytical processing on
very denormalized data models where we are actually
duplicating typically data in order to optimize for,
again, retrieve a lot of data operator aggregations.
Typically, we are running queries that are doing
large scans behind the scenes or ranges,
and aggregations, and such.
Those are the canonical database workloads
that everybody knows in theory.
But the reality is that a lot of
applications are actually generating mixed workloads.
Where, yes, you have a lot of
OLTP type of interactions with the database.
But at the same time,
there are also some analytical queries that are hitting
our database while this is
actually processing transactional workloads.
In most cases in real life,
our workloads belongs to this particular category.
There are techniques usually in
order to trade off between these two extremes
of OLTP and OLAP in order to get good performance.
But what we're going to talk about today is
how SQL Server first and Azure SQL database
now is going to provide specific support for running efficiently
this hybrid and mixed workloads
on top of the same exact database structure.
>> Awesome. This was a great overview, Silvano.
What I'm understanding is we have OLTP and we have OLAP.
But more commonly, we're seeing a mix of that,
and that's where this HTAP comes in.
I'd love to hear more how SQL Server
enables these HTAP type of workloads.
>> Yeah, this is interesting.
HTAP is a term that Gartner actually introduced a few years ago.
It was talking about
this hybrid transaction analytical processing as one of the key,
the main drivers for the future in relational database.
SQL Server starting from 2016 is actually introducing a bunch of
specific capabilities to help driving those HTAP workloads,
those hybrid workloads, by basically mastering in-memory type
of processing in traditional relational databases.
If we are talking about how SQL Server and
Azure SQL is enabling this real-time operational analytics,
we have to talk about some technologies like for example,
our columnar indexes that we can build on
top of a disk-based OLTP tables,
so traditional tables that are persisted on disks,
but also the ability to actually create
the same columnar indexes on top of in-memory tables.
By combining these two,
we can run this advanced and hybrid workloads
on the same data structure
and make our OLTP faster through this in-memory data access.
But at the same time also,
analytical queries that are going to benefit by this columnar
data structures and indexes that are speeding
up aggregations and analytical workloads.
By bringing the two together,
we can, for example,
eliminate, in some cases,
the need for transforming or running ETL processes on
top of our OLTP data in order to
denormalize them and create the traditional data warehouses.
In some cases, when we need
this fast and real time type of analytics,
we can effectively speed up
our performance by orders of magnitude.
In-memory OLTP tables right now,
we can run 30x faster actually,
and for analytical queries,
we have benchmark results where we can get
100x faster at analytical queries.
>> Wow, that's awesome. You've talked
to us about what this is and how SQL Server,
Azure SQL enables it.
I think this has been a great overview.
I'm sure there are some things to think about,
especially when we look at Azure SQL,
like what service tiers is available and how do I set this up?
We are going to do a few more videos.
Can you give us a sneak-peek on what you're going to show
us in the following videos and how we're going to dive deeper?
>> Absolutely. By the way, memory is expensive.
We know that in Azure SQL,
we have a bunch of
service tiers with different characteristics depending
on the level of service that those service tiers are offering.
For the in-memory OLTP tables,
we have to pick up our top service tiers.
We are talking about business critical or
premium for the DTU-based model.
In order to be able to run
this particular kind of in-memory feature,
those are the service tiers that we need,
so it's important that we pick up the right service tier in
case we need this very fast OLTP structure.
While on the other hand,
column store indexes are actually based on
pretty much all our service tiers starting from S2,
so from the standard service tier 2 up to
all the other service tiers available including Hyperscale.
It's important we have documented all this different flavors.
In our next videos,
we're going to drill down a little bit into these details.
>> Awesome. Thanks, Silvano,
and thanks for this overview.
It was a great overview.
I'm really interested to learn more about HTAP and how we
can get the most out of it
when it comes to our Azure SQL workloads,
especially since we know so commonly
these are these mixed type of workloads.
Thanks for being on the show.
For our viewers, we'll put some links in
the description to learn more,
and we hope you join us next time
when we dive deeper into this topic.
Thanks for joining us on Data Exposed.
[MUSIC]