>> Learn about the amazing improvements in SQL Server 2022
related to Buffer Pool Parallel Scans this week on Data Exposed.
Hi, I'm Anna Hoffman,
and welcome to this episode of Data Exposed,
we are continuing our SQL Server 2022 series and I
have once again joining me David Pless from the SQL Server team.
David, thanks so much for coming on the show again.
>> Absolutely. Good to be here.
>> Good to have you back.
In the last episode, we talked about
storage engine capabilities in SQL Server 2022,
and in this episode, we're going to be
diving into a very specific example,
I think very exciting example.
So without further ado,
can you tell us a little bit about it?
>> Yeah, Buffer Pool Parallel Scan.
Let's talk about what currently happens,
the Current Buffer Pool Scan Operations.
In SQL Server 2019 and previous releases
operations that required scanning
the Buffer Pool could appear to be very taxing and slow,
especially on large memory machines such as
the M-Series SQL Server Virtual
Machine and Larger On-Prem SQL Server environments.
The impact can be noticed on
operations such as like creating a new database,
everything we have on the slide.
Backup restore always on filler events,
file drop operations, check DB check table,
and internal operations like checkpoints.
Customers on these larger machines,
such as systems that have more than a terabyte of memory,
may witness these long database creation operations.
In some cases, it can take several minutes to
complete even some of the more basic tasks.
Even log restart operations could be slower,
and availability group failover operations can be
impacted around impacted as well.
>> Why on larger machines do we see it can be slow?
>> So the reason for this overhead is that scanning
the Buffer Pool in SQL Server 2019 in
earlier releases is always a serial operation.
So even if you have a large machine that's not going to help you,
actually the impact would be greater and it
doesn't matter about the size of the operation.
A lot has to do with the serial operation
and the amount of memory that it's having to scan through.
You can even see the impact on creating a net new empty database,
and it made it so it seems counter-intuitive.
Why would this issue happen on larger machines?
Why would it be slower on a larger box?
It's because of the serial scan process and the amount of
memory that you have to address regarding the Buffer Pool.
In the documentation,
when it talks about a workaround,
we state that there's no way to eliminate
this issue in the documentation currently 2019 and prior,
and flushing the Buffer Pool is going to
result in some degree of performance degradation.
Any subsequent query executions
would have to reread the data from the data file.
So now you're increasing IO if you try to
implement a workaround like say flushing the buffer pool.
>> Got it, so I'm hoping you're coming on to
tell us something new and exciting in 2022.
>> Yes.
>> That's going to fix this.
>> Right. Certainly,
a problem if you have a large
machine and you're dealing with that,
so in SQL Server 2022,
the Buffer Pool Parallel Scan feature improves the performance of
these scan operations on
these larger machines by utilizing multiple CPU cores.
So customers running SQL Server 2022
may see over 20 times improvement in executions,
which were previously slower
due to these serialized skin operations,
so this parallel scan feature also improves
the Buffer Pool Scan Performance of
smaller databases residing that is on larger machines.
Again, improvement is adding
process power processing power
to scan the buffer pool more efficiently.
Smaller operations on large,
large machines will also benefit.
This is a general benefit.
So just by upgrading to SQL Server 2022,
you're going to see this benefit because it's on by default.
We've also back ported some of
the Buffer Pool Scan telemetry for support ability,
we'll get into that as well.
Being able to see long scans in the air log starting
in SQL Server 2016 ESP3 and
more details that can be captured using
various extended events like scanning,
start/complete, capturing error events plus cache operations.
All of that will be available.
In the next slide, I'll have some context on this.
Okay. As you can see,
we've added some diagnostics to improve
the support-ability experience for
both serial and Parallel Buffer Pool Scans,
the events in bold are already available and SQL Server 2016
ESP3 the other events in the debug channel
specifically support Buffer Pool events for SQL Server 2022.
But the main event to focus on
is the Buffer Pool Scan Complete event,
which is fired when the Buffer Pool Scan
takes longer than a second to complete.
That event is going to give you your elapsed time parallel tasks,
the number of scan buffers,
the command, and the operation.
Also, the new diagnostics will provide
air log messages whenever
a Buffer Pool Scan takes longer than 10 seconds to complete.
Again, the air log message has been back ported.
It is available in SQL Server 2016 ESP3.
It's a good piece to
track when you're looking to start collecting data
on just one of the benefits of migrating SQL Server 2022.
>> Just to clarify,
the board extended events are going to
be available or all the external events,
and I can start using those so I can see this is
how much time I could save if I needed to get the benefits.
>> The ones involved, you'll
have the error log you're going to have.
This was back ported to SQL Server 2016 ESP3 and up.
So you can start collecting this data to get an idea.
I mean, of course, there's a ton of benefits,
ton of improvements in SQL Server 2022.
But just for will buffer pull parallel scan help me on
this larger data warehouse machine
this larger mission-critical transactional
workload box that I'm struggling with.
This is the type of data that you'll be able to collect to get
an idea of how it will benefit you and talking about benefit.
Okay, in the example shown here,
this is a Buffer Pool warmed up with
about a terabyte of data every day.
That's going to be about 140 million buffers.
Functionally, Buffer Pools Scans are paralyzed
again by utilizing multiple cores,
so there'll be one task per eight million buffers where
a serial scan will still be used if there's
less than eight million buffers or 64 gigs.
So for all the scenarios that were tested,
we have the resulting data on the slide.
It should be noted, though, that your numbers, of course,
are going to vary based on the size
of your machine in the workloads that are run.
But we've witnessed really strong results
in private testing and in lab conditions.
This is one of those features I'm so excited about.
Again, customers are just going to notice
SQL Server just seems faster,
especially when you rely on any scenario where
you're having to scan the Buffer Pool on
a large server of a terabyte of memory or more.
Promise demo, let's get into that.
Any questions, though, before I go? Before moving in?
>> No, I think this is awesome.
I'd love to see it in action to see,
what are the operations that I'm going to see,
the performance change on?
>> Yeah, so if we did a really long video,
I could show you because some of
these operations can take a really long time,
So this is the type of box that we're talking
about two terabytes of memory or more, right?
And all those operations that we talked about before.
Again, like creating a new database backup
restore failover events.
So I want you to think about a machine like that.
We start looking at the memory.
This is why a notebook is really helpful in this example.
I'm taking a look at the memory and I notice that I
have for this two terabytes that this box has.
We have 1.8 terabytes
of this available memory devoted to the SQL Server Buffer Pool.
Okay, now.
We see this here. In this next step,
we're going to populate the buffers in
the buffer pool by loading data from a restored database.
This is the BP scan test large database
that I have restored in this box.
My goal was to populate at least 64 gigs of memory.
I ran this command,
and now that I have loaded
this data in the memory and I've verified this,
I'm just looking at the sys.dm_os performance counters.
I can see that I have about a terabyte of
memory that I have in the buffer pool.
This is about 135 million data pages.
Now that I have this restored database,
I've loaded these buffers into memory,
I'm going to start working through a
comparison serial versus a parallel scan.
In the examples below,
the first example is just so simple.
I'm creating a database, creating a table,
and I'm just loading about 100 records in,
and that should be really quick,
but I had this really large machine.
The first one is going to run in serial.
I ran the next one in parallel.
It's using a trace plug to turn it on and off.
Here's the comparison.
The first one in a serial scan runs just over 10 seconds,
the parallel scan is just over one second.
Yes, that is an improvement of almost 90 percent.
This is a very common scenario for customers that
are constantly relying on
their applications to create a new database.
How about a database per customer in a hosted environment?
You're going to want this benefit.
We have some other examples too.
What about CHECKDB and CHECKTABLE?
It's giving me some relief so I can run this
in a highly transactional mission-critical setting.
Run CHECKDB on a couple of smaller databases,
run it on that larger one,
arrange CHECKTABLE on some smaller tables.
I run this first in serial mode.
I run this again in parallel mode.
We see that the benefit here is almost 30 percent.
We're going from 37 minutes to
26 minutes with 28 percent benefit
from going from serial to parallel scan.
The next one is the
simulating like an always-on availability group fell over event.
This test database, smaller database, the larger database,
I'm going to set it offline and then online,
first in serial mode,
then in parallel mode.
The difference is going from two minutes
to just under two and a half seconds, 98 percent benefit.
>> That's huge.
>> That's huge. Customers are going to see
a huge benefit on this on by
default buffer pool parallel scan option.
Then the last example
is just one of those scenarios that just happens,
I'm adding a file,
I'm dropping a database serial to parallel.
Exact same thing, the only difference is
the trace flag that turns the option on and off.
I go from just over 25 and a half seconds
to 1.6 seconds for an improvement of 93 percent.
Those are the comparative examples side by side,
but I really quick wanted to show you some extended events.
I create this extended event.
This is the stuff that we showed on the slide.
I'm going to watch the live data,
so I click and I say,
hey, I want to watch the live data.
Now, I've taken all those examples that I previously had,
Anna, and it's just noise.
But I start with
parallel mode and then
I'm going to run that noise again in serial mode.
First is the parallel noise,
backing up, that involves dropping files,
all that stuff, CHECKDB CHECKTABLE.
Then I do it in serial mode.
To make this quick so you can really see the difference,
I did a screenshot here.
In parallel mode,
you see all the parallel tasks working hard
through the buffer pool to
make the performance much better for you.
Look at the elapsed time difference
when I switch over to serial mode.
This jumps out at you at the benefit
that you get from the single feature buffer pool parallel scan.
I can't wait for folks to start being
able to test and use this feature in SQL Server 2022.
In the meantime, when you're in SQL Server 2016 SP3 and above,
you'll be able to use some of these extended events and
you'll also be able to look for these messages like
this in your error log to
identify where this could potentially help you.
I'm really excited about this feature.
>> I'm really excited about this feature.
I think you mentioned this is going to be on by default.
>> On default.
>> This is another one of those great examples in
SQL Server where it's just going to run faster.
Things are just going to be faster
because some of the improvements.
That's what I love the most about a lot of the updates we do
is that you don't have to go learn some new thing.
We're just identifying something that is a pinpoint and fixing it,
and it's just going to be faster. Super exciting.
Thanks so much David for coming on
the show showing us all these awesome examples.
To our viewers, I'm sure you're going to be
excited about this, have questions.
Feel free to like this video.
Leave us a comment, follow along with
the SQL Server 2022 series
using the link below in the description,
and we hope to see you next time on data expose.
[MUSIC]