>> Learn about the Storage Engine 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 Pless,
Program Manager on the SQL Server team.
David, thanks so much for joining us on the show.
You are no stranger and we're so happy to have you on.
>> Glad to be back. Thank you.
>> Awesome. I'm glad to have you here again.
Today we're going to be talking about SQL Server 2022 continuing
our series that we have and we're going to be
getting into the storage engine capabilities.
I thought, as we get
started and I'm really going to let you just run with this.
But as we get started to be great to
understand where we've been as far as
storage engine capabilities goes and
where we're going with SQL Server 2022.
>> I'll touch on definitely
the storage engine history and a little more.
It's really kind of just setting the stage for everything.
In previous releases in SQL Server 2016,
you may remember it was just runs faster.
It's where we made a number of improvements that drove
performance areas such as
the improvements in the setup experience,
and we further optimized TempDB.
We also introduced Query Store,
which is one of my favorite features stores,
query tax, execution plans,
and performance metrics at the database level,
and we also brought a whole set of security capabilities,
was always encrypted and dynamic
data masking and real level security.
In 2017, it was all about choice.
In this release, you could run SQL Server on
Windows or your preferred Linux platform,
including container images with Docker.
We also started the foundational concept
of built in intelligence with adaptive query processing,
and we further improved query store.
We added the ability to
automatically tune workloads by identifying
query regression and applying
a previous healthier version of a plan.
Then in 2019, it was
all about industry leading performance and security,
regardless of where your data is stored.
We further improved upon
Intelligent Query Processing by adding Batch mode on rows store,
Row Mode Memory Grant feedback and other query
optimization improvements and it was
all tied to the database compatibility level,
so it really helped with application migrations.
Now, we redesigned the database engine recovery process,
and this is important because we're going to come back to
this introducing accelerated database recovery.
There's a lot more, of course,
I'm just touching on the key highlights.
The point is we built an improved upon this groundwork and
implemented new features in
particular around performance and scalability.
This is Inter SQL Server 2022.
We have great innovations across the pillars,
we've laid the foundation with
the previous releases of SQL Server and in SQL Server 2022,
we've improved the capability pillars of Azure
integration database and storage engine capabilities,
analytics performance with intelligent database security
and further extending to SQL.
In SQL Server 2022,
it's really Azure enabled out of the gate.
We can connect and leverage
the power of Azure for your SQL server deployments.
But in this overview, in this session,
we're really going to be focusing on
the SQL Server database engine
with new features and improvements that really
helps improve performance on
large memory servers and definitely in high concurrency scenarios.
>> It's guiding.
>> We're going to go through this and in like in order so.
These are the topics that we're going to cover here.
You may see this slide again in some point in the future,
there might be a little more added.
That's kind of a teaser there.
But really the here, we're just going to be setting
the stage on these improvements and features.
Some of these are enhancements on
existing features where others
introduce new options and net new capabilities.
We're also going to have some other sessions where we
discuss these further and demonstrate these benefits.
I'm definitely excited about these upcoming sessions.
But again, we're setting the stage for a review at this point.
Let's start with the first one.
In order to really talk about this,
we need to talk about this,
the System Database, the TempDB database.
Now first, we know that we get one TempDB per SQL Server instance.
TempDB is used by every user database for temporary storage,
as well as system internals as a workspace.
The more workload we have,
the more work we're doing,
the more pressure we're going to be putting on the TempDB.
TempDB is incredibly critical
for performance and overall stability.
It's been like a key point to try to do,
and it's been important for DBAs to
recognize this and apply best practices.
We've been making this easier
to accomplish brilliant every release,
really, starting with SQL Server 2016.
But in 2019, we added
some new memory optimized TempDB metadata capabilities,
so it's like an opt in.
We also eliminated most of
the page free space contention with the concurrent updates.
Now, in that release, the 2019 release,
we thought we're going to be able to address all
of the contentious points in 2019,
but there were a couple of the contention
points we still needed to address.
In SQL Server 2022,
we've addressed these areas by introducing
Concurrent game and SGAM updates.
Now, what are these contentions here?
Under high concurrent workloads,
we may have some grand contention
where there's many different threads trying to allocate
or delegate extents on
the same game page because these updates are serialized.
This course causes throughput to be decreased,
and the workloads that require
many updates to the game page will take longer to complete.
The type of workloads fitting this characteristic,
so you may have some of these,
a repetitive crate and drop operations,
high use of table variables,
high use of work tables due to
cursors or order buys or group buys,
or like work files due to hash plans.
Now the concurrent game update feature in SQL Server 2022
adds the concurrent game updates to
SQL Server to avoid this type of game content.
If the DBA doesn't realize this is happening, they just,
don't even necessarily know how to troubleshoot or
define this what they may see as a SQL server just faster.
It just faster than the previous release.
>> [inaudible] .
>> When we add concurrent SGAM contention,
there'll be more on that improvement, potentially.
In a future session,
that is really going to also help further improve performance.
I'm really looking forward to demoing this and I know that we
have something planned for this, like a teaser.
We'll be talking about that later.
I'm really excited about it. Shrink database with low priority,
this is one of these things if you need it.
As many of us know, Shrink Database
and Shrink file recover space by
the moving pages from the end of the file to
unoccupied space closer to the front of the file,
and once there's no more pages to be moved,
we can shrink and truncate
the empty space and release it to the OS.
Now, even though a lot of DBAs are like,
well, we don't recommend doing that. Yes, understood.
But even though we don't necessarily
recommend shrinking databases all the
time as they usually just reclaim space on usage,
customers still need to shrink database files from time to time.
The challenge is, is that using
Shrink Database would shrink file operations and lead to
concurrency issues where the shrink process can be
blocked by workloads that hold incompatible logs.
You don't see it all the time,
but for customers and in
highly transactional environments, it definitely occurs.
The Shrink database wait
at low priority feature
similar to the alter index wait at low priority feature,
addresses this concurrency problem by taking a schema,
modify lock in a wait at
low priority mode so that when
the shrink is executed in this mode,
new queries requiring locks are not
blocked by the waiting shrink operation.
They jump the queue and continue to execute,
as shared locks are non-conflicting with other shared locks.
As a result, what was previously blocked by
these long running readers will no longer block
new queries and shrink more time out in a board.
As you can see on the slide,
it was not able to acquire or
modify a lock to queries holding shared lock.
There's no impact on the server in this case.
So like, how would this benefit me?
You're going to be able to shrink more often and
have less concurrency problems as a result.
>> Got you.
>> If you need it, you need it,
and here's another one
of those if you need it, you need it.
Wait, really quick before I move on to the next topic.
At the bottom of the slide, you see
DBCC Shrink Database with wait at low priority?
Here's a command for DBCC Shrink file wait at low party,
so I had both of those examples there.
>> Nice.
>> Yes. So if you need it, you need it.
Here's another one of those XML compression.
The XML data type was introduced in SQL Server 2005 has become
a common data type for certain scenarios
to store unstructured data in SQL Server.
So the customers that use it,
they tend to rely on it.
That's what I mean by if you need it, you need it.
Legal, health care,
financial services, all have archival requirements,
they all have regulatory requirements that
intensify the storage overhead, increasing storage costs.
We have data compression,
but SQL Server did not help with
compressing XML data types as they were stored off row,
and data compression only operates within row pages.
In SQL Server 2022 XML compression will allow customers reduce
their storage footprint without any driver
updates or any other changes to applications,
and the intent of this improvement is to achieve
space capacity savings for the XML data type,
that usage, though, you may see
some IO improvements as well for some workloads.
You can compress at the table level
or at the index level independently,
and so it's going to have a really big impact for
customers that heavily leverage
the XML data type with
capacity and potential IO savings as a result.
This is one of those really nice to have features,
especially for customers that rely on XML.
>> Awesome. A quick question how would it
help with IO performance?
>> Not only are you shrinking down
the data on desk and getting that savings,
I have less that I have to pull
into the buffer pool when I'm reading.
The trade off is potential slight CPU overhead.
So you're trading add IO for CPU,
but IO tends to be where we have the most points memory in IO.
>> Cool.
>> It's certainly something to measure.
This is why I say it's more of
a capacity savings rather
than you see some potential IO improvement as well.
Let's talk about Accelerated Database Recovery.
This is a tough one, because there's a lot here.
ADR was a new feature in SQL Server 2019,
and there's too much to go into to give it complete justice.
So please look into this feature for more information,
but it's in simplicity basically,
we re-architected the recovery process
to improve the time it takes to recover a database.
In general, ADR is going to improve database startup time,
failover time, and general recovery.
This is regardless of
the active transaction time
and the number of updates that are performed.
Now again, this was a new feature in 2019.
Now to achieve these benefits,
we leveraged a persisted version store
in the user database to keep up with
the row versions instead of putting
pressure on TempDB in the transaction log.
There's also an internal cleanup process
that cleans versions that are no longer needed.
Now there's other components of
support Accelerated Database Recovery,
but I want you to take note of
the persisted version store in this cleanup process.
These are the key ones we'll focus on.
Now with that in mind, in 2022,
we further enhance Accelerated Database Recovery scalability.
The user clean up process is improved
by allowing pages to be cleaned up,
even if there're concurrency issues
due to workloads requiring table locks.
Today, if the cleaner fails to get a lock,
we maintain a list of pages
for which the cleanup could not take locks,
so we go back and clean up these pages at a later sweep.
In SQL Server 2022,
we check to see if there's
any aborted versions before ever adding to this list,
the list is reduced and as a result,
it's a much more efficient process.
We've reduced the memory footprint
for the persisted version store,
and PBS growth is much better controlled.
Finally, there's also going to be
a multi-threaded version of the cleanup at the instance level.
Today, there's only a single clean up thread per instance,
and this is going to further help
the cleanup process scale at the instance level.
In summary, ADR is
a strong solution for customers that have long running
transactions and that still need
database recovery and to be fast,
and with much improved Accelerated Database Recovery
in SQL Server 2022 to improve memory overhead,
improve capacity that was needed and to improve
scalability limitations of the single threaded version store.
If you were looking at Accelerated Database Recovery,
you've tested it, maybe you've seen some limitations.
It's a lot better than SQL Server 2022.
>> That's awesome.
>> Yes. Here's another one.
I'm really excited about this one.
This is a feature that's just going to be one of
these things that just makes things better and faster.
There we go. The buffer pool scan operation.
The buffer pool is our cash store,
that stores database pages in memory,
and in SQL Server 2019 and previous releases
operations that require scanning the buffer pool can be slow,
especially on large memory machines
such as the M-Series or NV2 series.
These are these Azure SQL virtual machines.
Its a very large memory machines and also
think about like larger on premise SQL server environments.
Customers on systems with a terabyte of memory or
more may witness really long database creation operations.
In some cases, it could be several minutes or more.
Now, the reason for this slower experience
is there's a single thread that does the work of
scanning through the buffer pool and
the impact could be noticed
on operations that we have on the slide here.
Backup and restore,
Always On failover events,
file drop operations, CHECKDB,
check cable, and so forth.
Here's the part that gets customers' attention.
Additionally, the larger the machine,
the greater the impact.
For customers, this can be
frustrating and it's counterintuitive.
Why would a bigger machine with
a lot more memory and processing be less efficient?
It's because of the serial process and
the fact that that serial process is running over so much memory.
We're addressing this in SQL Server 2022 by paralyzing
the buffer pool scan process
benefiting both small and large databases
on large memory machines,
and this feature is going to be on by default.
So customers are just going to notice the SQL Server is faster,
especially on larger boxes,
especially when their applications rely on the regular creation of
a database or regular maintenance on
mission critical transaction systems.
So we're talking about a
20 times improvement or better in many of these cases'.
>> Wow.
>> Yes, and these operations are just
going to be so much more scalable,
even on a data warehouse platform.
We're really excited about this feature.
I definitely am going to have a session here
soon where we showcase a little business,
get a little bit into the meat of the telemetry, and
actually demo this capability. So really excited about this.
I consider this one of the hero features of SQL Server 2022,
as far as the storage engine is concerned.
Definitely excited about it.
Now, I know we just touched on this,
this slide could grow,
but these are the key features that are really want to set
the overview for I know that we're going to
have some demos of these coming up.
There's a lot more coming and I'm
looking forward to future sessions. This is it.
>> Thanks so much, David.
I'm super excited.
I just learned a lot, I'm sure our viewers learned a lot,
and I love that you gave us that teaser and
like hey we're going to go deeper into this soon.
Thanks again, David, for coming on the show.
For our viewers, thanks for joining us.
We hope this was useful.
Please follow along with the SQL Server 2022 series
to see more update and if you haven't caught up, also,
catch up on older episodes and leave us a comment and
let us know what you think and we hope to see you
next time on Data Exposed.
[MUSIC]