>> SQL Server 2019 Big Data Clusters provides
compute pools to offload distributed query processing.
UC is here to tell us all about this today on Data Exposed.
[MUSIC]
>> Hi. Welcome to another episode of Data Exposed. I'm Jeroen.
Today, I'm joined by UC to talk about compute
pools in SQL Server 2019 Big Data Clusters.
Hi, UC. Thanks for joining the show again.
>> Sure.
>> Compute pools?
>> Yeah.
>> What are they?
>> Compute pools. They are
basically SQL Server instances in a big data cluster,
which can be used to offload your distributed query processing.
So in this picture,
we see the many components in a SQL Server Big Data Cluster.
Today, we're going to look at this compute pool over here.
So what is it? It's basically a set of
SQL Server instances which are
automatically brochured inside a big data cluster,
and they are available for doing a distributed queries.
>> Okay.
>> This is similar to the PolyBase
Scale-out Groups in SQL Server 2016.
This capability now provides you
out-of-the-box set of SQL instances,
which can do most of the distributed work for you.
>> Okay.
>> Queries can either use the compute pool or not use
the compute pool depending on the type of query.
>> What scenario would I choose for a compute pool?
>> Yeah. Great question. So let's see.
One of the common scenario is say you have two directories in
HDFS with hundreds and thousands of
files and you want to join them.
So in that scenario,
you don't want to get all the data over to your SQL Server.
>> No.
>> Which is running your application.
So that's where the compute pool helps.
So it can offload most of the work over to the HDFS
and then later pull
the necessary data to the compute pool and do the join there.
So this basically offloads them all,
the computing world to different SQL Server machines which can be
on different nodes in that big data cluster,
and use those resources.
Then the other scenarios,
you are joining data from
different data sources which are partitioned differently.
So there you have to unify that partitioning at some point,
and that's where the compute pool helps.
>> Okay.
>> So if one table is distributed by
Customer ID and another is distributed by Order ID,
and you're still joining by Customer ID,
it can do that reconciliation for you.
>> Okay.
>> So that's some of the scenarios.
You can also do things like exporting data into HDFS,
and that's some other place where the compute pool can help.
>> Okay. So the compute pool will help me to
parallelize, scale-out my [inaudible].
>> Yeah.
>> Both reading from HDFS and writing to HDFS at all?
>> Yes.
>> Cool. How does that work?
I mean, can you show us a little bit of how that works?
>> Yeah. Sure. Let's go here.
I am actually connected to a SQL Server Big Data Cluster,
and specifically the Mastered instance is shown here.
So we now have a new DMV,
which is called compute pools.
>> Okay.
>> Basically, it shows the compute pools which
are provisioned and available in the Big Data Cluster.
By default, there's only one and we show that information here.
Then you can also see
how many nodes are actually there in the compute pool.
This query actually shows,
apart from this particular SQL Server instance,
I have two compute
pool instances as shown by these highlighted rows, right?
>> Yeah.
>> There are other DMVs which you can use to basically find
information about the compute pool like how's the CPU activity,
how much memory has been allocated,
whether it's even available for the query and so on, right?
>> Right.
>> These are information which a DBA can
use to troubleshoot compute pool.
>> Sure.
>> Additionally, you can run a complex query in
SQL Server which can actually go and use the compute pool.
>> Okay.
>> So in this example,
I'm joining a local table in SQL Server with some data in HDFS,
and I also have a table in Oracle, which I'm querying.
So you can basically run a query and
the query optimizer automatically figures
out how to use the compute pool.
In this case, it's going to use the computer pool for
your HDFS table and
the rest of the data is all joined and returned.
That's an example where a compute pool
transparently works to get the results for you.
>> Cool. That's looks really good.
Basically, I can write this query.
I now can trust the compute pool will do step
in where it makes sense to optimize the performance, correct?
>> Yes.
>> Awesome. Well, thanks a lot for sharing.
>> Sure.
>> I hope this was useful.
Please like or subscribe to the video and comment.
Hope to see you next time. Thanks for watching.
[MUSIC]