>> Learn about the meat and potatoes in SQL Server 2022,
that's security, scalability, and
availability this week on Data Exposed.
[MUSIC]
Hi, I'm Anna Hoffman and welcome to this episode of Data Exposed.
We're back with our SQL Server 2022 series, joined by Bob Ward.
Bob, thanks so much for coming back on this show.
We're going to dive right back into it.
We're talking about security, scalability,
and availability, so we're going back into
that power slide that you had.
But I wanted to ask you, security, scalability,
availability usually aren't the meat and potatoes,
security, performance, and availability?
>> You're right Anna. It is the meat and potatoes
because it's the core engine of SQL Server.
Built-in core intelligence is what I think
more of that great core performance work.
There is performance work here,
but it's all around scale,
so I call it scalability.
It's a mouthful to say it all at one time. Exactly.
>> I would just say meat and potatoes.
>> Meat and potatoes, exactly.
Well, and I remember you just said wheel of power,
we're on that third leg of wheel of power.
We've already talked about two in other episodes,
now it's all about that meat potatoes.
Let's dive in and talk about it.
I like to think of some of
the major capabilities in these three areas.
I think of ledger like a blockchain,
we're putting inside SQL Server.
I think about hands-free, tempdb, man,
[inaudible] is going to love what I talk about this one.
I think about availability,
things like contained AGs where system
objects like agent jobs are now replicated with their AG.
I think people are doing cartwheels right
now on the audience just hearing that.
Things like a snapshot backups without a
VDI program or even some amazing work parting with
Intel using Quick Assist technology to improve
compression, new hardware offloading.
There's a lot here.
There's major capabilities in this area,
but then check this out.
I'm not going to go over all the details of this,
but a lot of people have thought maybe in
this release we're not taking care of the engine,
there's over 25 plus new engine features.
Every one of these has an amazing story
behind them that I think you'll take a look at.
If you go on our documentation,
you can see details behind every single one of these.
Perhaps one of these is a favorite on your list.
But what I'd like to do is dive into
a couple of the ones we just talked about here and
explain more what they mean and how they could
have more value in it to our customers.
It starts with ledger.
If you think about the need to have a blockchain,
it's about a decentralized authority
and typically in a decentralized system.
But those blockchain systems have problems for
performance perspective, management perspective.
So we decide to put a blockchain in SQL Server
and achieve the same thing in a centralized system.
Think about this scenario,
you create what's called a ledger
table with a simple syntax change,
you make it updateable, you get
automatic history of what changes look like.
But then inside the intron
automatically are things called a database ledger,
a series of system tables to track who made the change,
and even a hash to verify that the data hasn't been tampered with.
In addition, there's an independent way
to verify the data called a digest.
Like an independent regulator authority
could verify that the data is valid inside the ledger.
We even support a thing called an append-only ledger.
For scenarios where you have multiple parties coming in,
all trying to share the database of what's going on,
say a supply chain,
you can make sure that only inserts can happen in the system so
no DBA could come in and try to
go fake out the data with say, an update.
This is ledger for SQL Server.
The amazing story here,
it's part of Azure Database,
same technology and as we talked about in a previous episode,
it even comes on Express Edition of SQL Server
in all of our additions.
Really excited to see how this technology
is going to take off for our customers.
>> It's awesome.
>> It is pretty cool.
>> I think it is a game changer for databases.
>> Absolutely. Think about it,
in SQL Server there's
no extra buyer ad and it just comes with the product itself.
>> Exactly. I know there's
some other game changers that are pretty exciting coming out.
>> Well, Anna, so starting in SQL 70,
we have this concept called latching,
which along with tempdb caused all problems for customers.
Whether we run into latch contention,
due to just temp based workloads.
We had all these solutions, we provided to them,
a lot of it involve multiple files,
trace flags, configuration options, all these type of things.
Now starting in SQL 19,
we started making some enhancements here.
We changed the concurrency model for something called
PFS pages and added this option called
tempdb metadata optimization to
remove the hotspot for system pages.
But what remained is a couple of
other system pages called a GAM and an SGAM.
Well, guess what Anna, in SQL 2022, we got rid of that.
If you put together the combination of
the built-in enhances we've made
with tempdb metadata optimization,
now, latch contention is completely gone.
>> Wow.
>> This is like a 30-year-old problem
and now we've made it hands free for you.
>> Can we see it?
>> Well, so you want to see hands free tempdb,
so I couldn't resist this slide.
But let's see it in actual,
what it looks like. I think you're going to really like this.
Typically, you create multiple files
and I'm not suggesting you don't do that anymore.
But I went ahead and created a system in
SQL 2022 with a single file to see what I could achieve.
You can see here in Management Studio what I've got,
is I've got a single tempdb file with
eight gig and a log of 200 meg for tempdb.
But just two files, that's it.
Now here's my query, I'm going to show you.
Just look at latch contention, but here's my crude test.
I'm going to do three tests.
I'm going to do just the optimization that came from 19 with PFS,
no other optimization,
then I'm going to add on tempdb metadata optimization,
but not use the new 2022 enhancements.
Then third test is I'm just going to turn
it all on and I'm going to
observe latch waits and overall performance.
Let's go see what that looks like here and I love perfmon.
I'm going to show you batch requests per
second and I'm going to show you
a page latch wait counter to see what latches we're seeing.
This is a query you could run in your system
to see if I'm seeing latch waits,
what pages are those?
I've got these workloads and what I'm going to do
first is I'm going to run this query here.
I'm going to turn off
metadata optimization to make sure that's not on,
restart SQL Server because you're required to do that.
Now what I'm going to do is I'm going to use some
trace flags are undocumented,
but they allow me to disable the 2022 enhancements.
I'm going to run that as well.
Now, my server is ready to do test number 1.
I'm going to go ahead and start perfmon here
and let's go run a tempdb workload.
I'm just going to stress tempdb
with 25 users and see the behavior.
Now you can see here that it looks like things are okay.
Look at this red line.
What is this Anna? This is it just like 107 latch waits per
second in my system and if I run this query,
those objects are system tables.
I'm getting latch contention on system table pages in tempdb.
Now eventually this is going to finish,
you can see the throughput here a little bit.
Let me pause perfmon because we're going to compare all three
together and go back and see it took about 19 seconds,
so let's go record that number 19 seconds.
Now let's go turn on tempdb metadata optimization,
but not the 2022 enhancements.
I've got some command scripts to do all these things.
Let's turn on the optimization with this command script,
we have to restart SQL Server.
Then I'll run again this command with
these trace flags to not use 2022 enhancement.
I'm in test number 2 now
and I'm just going to run the same workload.
Use perfmon, I'll start it again.
I love that pause startup perfmon.
Run this with the same number of
workloads, let's go see what it looks like.
The throughput looks less.
Now there's less latch waits per second, that's weird.
If you look at it,
it's all GAM pages.
Now, the reason why the throughputs less is
because there are less last waits
but they take longer per latch wait.
It's a longer duration per wait with less number of them.
But the throughputs less.
>> Right.
>> You can let this thing finish
and you're going to see in a second here what this looks like.
You can see it's going to be a longer duration already.
Let me go back to the 0 stress window here and see,
it's about 28 seconds.
In our sophisticated recording model and notepad here,
I've got 28 seconds.
Clearly number 1 is still the winner,
let's just turn it all on.
By the way, all I need to do is restart SQL Server
because I've already got tempdb metadata optimization on.
I'll just turn off the trace flags which light-up 2022 by default.
Let's just Restart SQL now,
and then just run the same workload,
the exact same test and we'll start
perfmon because we're going to compare all three side-by-side.
Let's kick that in and see what happens.
You go over here, look at that,
the best performance, no latch waits.
In fact I'll run this query,
all gone. Just keep running it.
If you go back to perfmon, look at this,
the best performance, the shortest duration.
Let's pause perfmon, look at
the difference between these three thing here.
Just take a look for a second here.
Highest latched number of waits,
the medium type throughput,
lowest performance and number 3,
the best performance with no latch waits.
That's why I call it hands-free tempdb and I
can go back and look at the throughput and it's 12 seconds.
So 19, 28 and 12,
the winner is clearly SQL 2022 because it's now become hands-free.
>> Wow, that's pretty amazing.
You don't have to do anything, it just runs fast.
>> It just runs faster because I didn't change any code,
the workload was the same in all three scenarios.
I just use the built-in capabilities of 2022 with
our tempdb optimization option and there I go, hands-free tempdb.
>> Amazing.
>> That's pretty good stuff. For SQL people that have faced this,
this is really groundbreaking stuff because it saves you
time from having to deal with these type of tuning.
Pretty excited to see that land that's all in SQL 2022.
Last quick story. Anna there's
somebody in the group named Purvi Shah, she's an engineer.
She is not only one of the smartest people at Microsoft,
one of the nicest people I've ever met.
Every time we have a release, I ping Purvi.
I say "Purvi, what have you been
working on that nobody knows about?"
This is her stuff, this is her list.
These are things baked into the engine that she's built to
make SQL Server run faster, also in the Cloud.
Look at the last two.
If you're a SQL Server person and you've dealt with
virtual logfile problems or instant file initialization problems,
that now just works in SQL 2022.. You know what, quite frankly,
it's all due to Purvi because she just creates this innovation in
her head that no one thinks she can do and she does it.
I always ping her before I
release and this is the list she gave me.
Huge kudos to Purvi Shah for that lesson.
>> Awesome. That's really awesome for her
to do that, this is the cool list.
Now Bob, you've shown us a lot,
wrap it out for us.
>> Well, meat potatoes, right?
Security, scalable and availability.
Think about ledger, blockchain.
Think about hands-free tempdb.
Think about contained AGs.
Think about Purvi's list, 25 major engine enhancements.
This is not just a SQL Server product about Cloud connectivity,
this is powered by major innovations in our engine.
Because I was told by long-time mentor,
long time ago that if you don't innovate in this engine,
you don't have a product and I think we've done a great job here.
>> Awesome. Well, thanks Bob so much for coming
on again to the SQL Server 2022 series.
This is not it, We got more episodes planned out.
If you're a viewer, stay tuned,
make sure you check out the other episodes in this series.
Check the links in the description to learn more
and we hope to see you next time on Data Exposed.
[MUSIC]