>> Do you compress your backups?
If you do, you're going to want to tune in
to this episode where we talk all
about the new capabilities in SQL Server 2022.
This Week on Data Exposed.
[MUSIC]
Hi,
I'm Anna Hoffman and welcome to this episode of
Data Exposed today I'm joined by David Bus,
who's no stranger to Data Exposed.
It's great to have you on the show, David.
>> Thank you.
>> We're going to be talking about SQL Server 2022 integrated
acceleration and offloading with Intel quick assist technology.
Can you tell me who needs us? What is it?
>> Okay. If you are backing
up and compressing your databases today,
especially in a mission critical environment,
you definitely should care about this,
we know that we're compressing our backups already
because we need to save on space,
we need to save capacity.
The problem is, is that choice puts us
often in a spot where we're choosing
between customer workloads and
our business continuity and disaster recovery strategy.
Especially if we're pushing up against that compute,
which we know we all are because we want to
take the most out of the resources that we have,
the most out of the systems that we have,
we end up in this spot.
Now there's a bunch of third party solutions out there today.
Software-based solutions, they tend to drive more compute.
>> Okay.
>> They're not integrated into the SQL Server engine,
it causes really a support scenario
because you can't manage it the
way you would really like to manage it.
The answer here is hardware offloading and acceleration.
That's what we have in SQL Server 2022.
>> Cool.
>> It's a built in acceleration technology.
>> How does it work?
>> I'm walking you a little bit through the process here as well.
We have an SP configure option,
the hardware offload enablement.
This would turn off all accelerators,
it toggles it on or off.
By default, it's often this is what you would need to enable it.
Then the next step is the choice on hardware or software mode.
This part I think is really
interesting because if I'm choosing hardware men,
I'm choosing to offload to this external device.
I'm choosing to protect the host system.
Not necessarily faster, but protecting
the host system could be faster depends on the device.
Software mode is going to be leveraging
the Intel drivers and software on that local system,
on that host systems compute.
Do I protect the system with hardware offloading?
Or do I opt for what software can give me on the base machine?
whether it be a vm or bare metal.
>> Sure.
>> Here's software mode,
and that's what you would need to do.
Now at this point, you're going to restart
your SQL Server service because we have to
load those drivers into the SQL Server process space.
This dynamic management view, I absolute love,
this dynamic system server accelerator status.
This will tell you-all about the accelerator that is enabled,
the driver version, the reasoning.
It's really good for troubleshoot.
>> Cool.
>> This is how you would use the default
MS express if you wanted to specify the algorithm.
You notice it's just an addendum to
the compression based backup database statement.
This is how you'd use QAT Deflate.
It's that simple. If you did nothing at all,
and you just brought your scripts from 2017 or 2019,
we're not going to break anything.
Your backups are just going to run with the MS Express.
If you want to specify you need to say
algorithm QAT Deflate. Want to see demo?
>> Yeah. Let's look.
>> Again, what we see here and again,
hardware offload enablement is an advanced option.
I'm going to run this.
Now here's that choice, hardware or software.
I'm going to go hardware mode here.
We are saying that we were using the QAT accelerator.
When we run this,
we're going to have to have that next step,
as we mentioned, we have to restart the SQL Server service.
I'm going to run this here.
I'm going to restart the SQL Server service.
I'm doing this to load
those drivers into the SQL Server process space.
Once this is done,
then what I can do is use
that dynamic management view says DM server accelerators status.
What you see here is the accelerator,
the description of that accelerator, the mode,
the reason why you're in that mode,
this is what's so great for troubleshooting.
Did you see the hardware?
What library, what driver version am I using?
Some from a troubleshooting perspective.
This couldn't be, really couldn't be better.
It's exactly what we want.
By the way we document all of
the mode description reasons. Another way of troubleshooting.
I love doing this SP Readerrorlog
and I'll just look for anything until
it happens to be the accelerator that comes with SQL Server 2022.
Just to get an idea of what is the storage acceleration library,
what driver do we have available and so forth.
What I'm going to do now is I'm going to do
a backup database with no compute overhead at all.
This is just going to give me a baseline of what's going on.
I'm going to make a note of
that baseline because this is going to be important,
baking a cake a little bit.
I've got the clock going, I like doing that.
I'm going to take the message that we have here and notice 86 s,
roughly 3,000 mb/s with no compute overhead at all.
We're going to take this note
and make this documentation
here because I'm going to go back and compare to this.
Again, I'm going to do
another MS expressed backup. Here's the thing though.
I'm using a sysinternal tool,
CPU stress to push this machine almost to the edge.
>> Got it.
>> Nothing going on yet,
let's kick off these workloads and kick them both off.
I'm going to put the CPU right at 94 percent.
This is so common.
I've got so many customers in this spot.
They want to take the most of their systems,
the problem is with the default,
Ms Express compression algorithm.
Here's the challenge. I'm going to push
this not at the edge, over the edge.
Not only is my backup suffering,
my SQL Server workloads are suffering.
This is what I mean, customers choosing
business continuity or
disaster recovery versus customer workloads.
I have that challenge all the time.
How long is this backup going to take?
>> Does it take longer?
>> It doesn't take that much longer.
It's the CPU that's the problem.
>> The other workload.
>> Right. It's so 87 seconds 2,916 mb/s.
Again, we're going to make a note of that.
But of course, we haven't seen the QAT deflate yet.
That's where it gets pretty interesting.
We're going to same backup command to the only difference here is
we're using the QAT Deflate algorithm.
I'm still at that 94 percent.
>> Because you still have this workload running.
>> Yeah, the other workloads never stopped.
In fact, I'll even bring it up here just so you can see,
we're still cooking pretty well here, 94 percent utilization.
We're going to see what happens next.
Again, I'm kicking this off now.
Notice that I only go to 97 percent.
It's only three percentage points higher than what we were before.
My SQL Server workloads are running.
>> They're fine.
>> My backups are actually going to complete a little
faster and I'm even going to get better compression.
I have a better capacity savings.
But take a look at this.
This is what's interesting here.
I'm going to drop this node
in and we're going to compare all three.
So that was 76 seconds,
I think around 3,300 mb/s.
The Ms Express took 87 seconds 2,916 mb/s.
That was with compute overhead,
the QAT Deflate took 76 seconds, 3,353 mb/s.
Here's the thing. It outperformed
the Ms Express without any compute.
>> Wow.
>> Right. The Intel QAT was able to
outperform the Ms Express even with all that overhead.
You also get better capacity settings.
>> Nice. This is going to be music
to a lot of folks' ears, I'm thinking.
>> Absolutely if you're compressing your backups
and you're running against compute challenges because of that,
hardware offloading is perfect for customers.
>> How do people get started with this?
>> I would recommend going out to
the SQL Server 2022, what's new page?
There's an integrated acceleration hardware offloading
and then go to the Intel
quick assist technology landing page so
you can learn all about the technology, what it can do for you.
>> Cool, awesome. Well, David,
thanks so much for showing us this,
this is really cool. I really enjoyed it.
>> Thank you.
>> I learned a lot. I think our viewers
probably learned a lot for our viewers,
be sure to check out this technology.
We'll put links in the description for you to
learn more if you like this episode,
go ahead, give it a like,
leave us a comment and let us know what you think and we hope
to see you next time on Data Exposed. [MUSIC]