>> Benchmarking is a very important part of
right-sizing your workloads and
analyzing the workloads that you have.
Learn more in this episode of Data Exposed: MVP Edition.
[MUSIC].
>> Hi, I'm Anna Hoffman and welcome to
this episode of Data Exposed: MVP Edition.
Today, I'm joined by Gianluca, based in Italy,
and he's going to tell us a little bit more about WorkloadTools.
Before we get into it, I just wanted
to welcome you for being on the show.
>> Thank you for having me.
>> Why don't you tell us a little bit about yourself.
>> At Data Platform MVP,
I've been working with SQL Server for a lot of time, 20 years.
I started with Version 7,
so this means I'm old.
Currently, I'm working as
a DBA at a feminists Formula One team in Italy.
>> Awesome. Well, thanks again for joining us.
As we shift into WorkloadTools,
I just want to understand first,
what is a benchmark and why is it important?
>> We can say that a benchmark is something that describes
the performance of your system in a given scenario.
If I went to your server and I took
a picture of how performance looks like, that's a benchmark.
This is useful because you can take
different benchmarks in different scenarios,
and then you can compare the two.
This is useful, example,
when you have to upgrade something in your software stack,
could be SQL Server itself or
any other parts of your software stack.
But this is also useful when
you change something in your hardware,
like the storage, or the machine that runs the workload.
This is also useful for other scenarios like
virtualization or migration to the Cloud,
or to a different data-center.
It is also interesting to
compare benchmarks when you want to rate your tuning efforts,
so you change something at
the database level or at the server level,
or something at the code, or adding indexes,
changing views, or store procedures and you want to see how that
performs compared to the baseline that you recorded initially.
>> Awesome. It sounds like benchmarking is really important and
also applicable to various number of scenarios.
Now, benchmarking seems like this is
a concept and an idea that's been around for a while.
Over the years and as we go from SQL Server to Azure SQL,
are there challenges that have arisen over time,
or has it been the same process through and through?
>> Well, the challenges have to do with the tools basically.
Both the tools that we have in our hands,
these are typically tools that come from Microsoft itself,
but also the way the process is done.
I have a slide here that I can show you about the process.
Imagine that you have your production database here.
What you traditionally do is record a workload
using a trace file or an extended events session to some files,
and then you have to wait for the capture to finish,
which has to last for a meaningful amount of time.
At the end of the capture,
you have to process those files and the results
is an analysis of
the workload that goes into an analysis database.
Then some stub files that you can use
to replay the workload against a test database.
While the replay is running against the test database,
you also capture a second set of files,
and you again have to wait for the capture to finish.
Then again, process these files
right to the analysis database again,
and then you can run your reports
on top of that database to see how
the two workloads stock against the other.
This is a bit problematic for a couple of aspects.
First of all, the disk could fill.
You have to capture a lot of files,
and this is typically very big files that capture a lot of events,
and then you have to process these files.
Usually what happens here is that some of the tools like
RML utilities or distributed replay
tend to crash when the files are too big.
In order to overcome these limitations,
I created WorkloadTools.
This is a set of
Open Source tools that take
care of all the aspects of benchmarking,
like capturing the workload, analyzing the workload,
and replaying the workload against a test database.
Also, we have two unique features in WorkloadTools,
not found in the traditional tools for Microsoft.
It can work against
an Azure SQL database or an Azure Managed Instance,
and also can do real time replays,
so you don't have to wait for the workload to finish,
you can start replaying it right away.
Let's see how this works.
You could be capturing a workload
to analyze it with WorkloadTools.
Again, we have the production database,
and then we have SQL Workload,
which is one of the tools in WorkloadTools.
Instead of having to capture
all the events before you can analyze them,
you can stream them directly using
an incremental load of Excel files or trace files,
or even using the streaming API for extended events,
and all of these events can go directly to an analysis database.
Also, all the queries get normalized.
Normalized means that all the literals
found in the queries are stripped away and replaced by
some placeholders so that queries that differ only
for the values of
the literals can be grouped and analyzed together.
Then you can use some reports.
There's a reporting tool included in WorkloadTools to
analyze and visualize the data from the analysis database.
>> How does this look like?
Here, an example of this; this is a reporting tool pointed to
the analysis database and it contains the CPU and
duration over time of the workload.
We also have an indication of the batches per second.
But an interesting part of the workload analysis is here,
we can rank the queries by
total duration or total CPU and see which ones
are those that have the highest numbers by CPU duration,
or number of executions, and so on.
We can see what is the shape of the workload to analyze it.
Other activities you can do with
WorkloadTools is saving the workload to a file.
Again, we have the production database SQL Workload
that receives all of the events.
Instead of analyzing the events,
we can save them to a file so we can use these events as
a source for, again, SQL Workloads.
We can use it to stream the events again from this file,
and repeat the analysis or even the replay.
Again, we can have a look at the results from the reporting tools.
The most important part of WorkloadTools,
the most important feature,
is how it performs replays.
Again, production database, SQL Workload in the middle
receives all the events incrementally.
Then you can start analyzing as we did previously.
We write the data to the analysis database,
but now we also do, at the same time,
the replay against the test database.
We have this time,
a second instance of SQL Workload that
receives all of the events as for the production database.
We can write these events to
the analysis database so we can now compare the two.
We don't have to wait for all the files to arrive
for the workload to be recorded fully before we can analyze it.
We can start replaying right away and analyze
both workloads from production and from test at the same time.
This allows us to run for several hours,
as I did here.
I can show you what it looks like
when the tool runs for several hours.
This time we have two series of data.
Baseline is green and benchmark is black.
This is an example running for seven hours.
We can see how this looks like.
We can maybe zoom in to have a look at
this part of the workload and see what it looks like here.
If we have a look at the queries,
now we have more columns and we have
also the difference of the two workloads.
We can see, example, the duration,
this is the difference of the total duration,
and this is a regression.
We can start tuning this workload from
this query because this is the most promising one,
the one that has the worst regression.
All these can be configured using JSON files,
and there's a couple of information that
has to be filled in JSON files to configure SQL Workload.
You have to fill in data for listeners,
where the events come from, and consumers,
so what you want to do with the events that you are capturing.
In this case, we are saving
an analysis to target analysis database.
But we could also be doing different things like
running replay or a real time replay.
This is what it looks like for a replay, for example.
This also works against Azure SQL database as we have here.
Basically, this is what we can do with WorkloadTools.
I know this is a bit complicated
and it's not easy to fit in only five minutes.
But if you go and have a look at the project and documentation,
I'm sure you will find all the answers to your questions.
>> Awesome, thanks Gianluca.
This has been really interesting to me,
someone who's not super familiar with benchmarking.
But from what I can tell,
when we compare what it used to be
like versus what it is with WorkloadTools,
you've really taken out a lot of the complexity that is there,
and also you've now enabled us for scenarios like
analyzing workloads in Azure SQL Database
and Azure SQL Managed Instance.
Now one of the questions I came up with as
I was watching your demo is,
could I use this as I'm migrating to Azure and I'm
trying to figure out what size I should
go to as far as vCores or DTUs,
can I use this tool to figure out what the size of my workload is?
>> Not directly looking at the analysis data alone.
What I suggest is to start with
the smallest setup that
you think will fit your workload and replay.
As you go on with the replay,
you can see in real time what
the performance numbers look like and you can go ahead and
increase the resources for your Azure SQL DB
or Managed Instance and
see if that is enough as you go and analyze the workload.
>> Got it. So I can leverage this tool to see,
am I right size?
If I size up, how does that affect
my performance and that sort of thing.
>> Yes, exactly.
>> Awesome, cool. Thanks so much for coming on the show.
We did put a lot of your resources in the description.
If you are interested in learning more about WorkloadTools,
be sure to check out those links in the description.
Feel free to leave us a comment to let us
know what you're doing with WorkloadTools.
We hope to see you next time on Data Exposed: MVP Edition.
[MUSIC]