Up next in 10
Performance Improvements in SQL Server 2019 by Frank Gill || SQL Server Virtual Conference
Oct 30, 2023
Released in November 2019, SQL Server 2019 contains several improvements and new features that improve reliability and performance. First, Accelerated Database Recovery (ADR) completely redesigns the SQL Server database engine recovery process. This allows the time spent recovering from a crash or rolling back a transaction to be greatly reduced. ADR also prevents the transaction log from growing during a long-running transaction. Second, metadata for temporary tables can be stored in in-memory tables. For tempdb-heavy workloads, this removes a bottleneck and provides an improvement to performance. Finally, SQL Server 2019 supports the use of persistent-memory (PMEM) devices. PMEM can be used to persist the transaction log buffer, effectively removing LOGWRITE waits. Join me for this session to learn about these new features in SQL Server 2019 and how they can make your workload run faster.
About Speaker:
Frank Gill is a Senior Data Engineer at Concurrency With 20 years of IT experience, the first 8 as a mainframe programmer, he has developed a love of all things internal. Over the past several years, he has worked extensively with SQL Server solution in Azure, including Managed Instance. When not administering databases or geeking out on internals, Frank volunteers at the Art Institute of Chicago and reads voraciously.
Conference Website: https://www.2020twenty.net/sql-server-virtual-conference/
C# Corner - Community of Software and Data Developers
https://www.c-sharpcorner.com
#SQL #sqlserver #sqlserver2019 #conference
Show More Show Less View Video Transcript
0:00
I am going to talk about performance improvements in SQL Server 2019
0:06
And just a little bit about me. I've been working in IT for 21 years
0:11
13 of it, the last 13 of it, have been working with SQL Server. I'm currently a senior data engineer at a consulting company called Concurrency
0:21
In addition to SQL Server, I'm learning more about Azure every day. And I'm really interested in SQL Server internals
0:27
And I'm going to get somewhat deep into internals in this presentation
0:32
When I'm not working with SQL Server and other tools in the Microsoft data platform, I love to read
0:38
I volunteer at the Art Institute of Chicago here in Chicago, and I hang out with my cat and my wife
0:43
I should probably put her on the slide. But my contact information is here
0:48
You can reach out to me on Twitter via email and the slides and scripts for this presentation will be available at my blog
0:58
And then Simon, I want to touch base with you after this and just figure out if there's a place I can put them for the conference itself
1:06
But just the topics we're going to cover. I'm going to talk about the transaction log and how the transaction log is architected in SQL Server
1:13
And then I'm going to talk about the crash recovery process, both in versions prior to 2019, and then how that crash recovery process has changed with a new feature in 2019
1:27
And then I'm going to talk about putting TempDB metadata in memory, which is another feature that is available in 2019 and can help improve performance
1:36
And then finally, I'm going to talk about persistent memory. And that is a hardware-specific feature that SQL Server 2019 has been built to take advantage of. And I'll talk about how you can use that
1:50
So, quickly, I'm going to talk about transactions. So, any relational database management system uses transactions to encapsulate units of work in the database
2:04
and in SQL Server, every transaction begins and then a transaction can commit or roll back
2:11
And in SQL Server, the default behavior is auto commit. And what that means is that if I go into Management Studio
2:19
and I say delete from table and I don't give it a where predicate
2:23
it is going to delete all of the rows and it's going to wrap that transaction in a begin transaction
2:31
And then when those deletes complete, it will commit that transaction. So SQL Server, when you create a database, you get at least two
2:42
files. You get a data file, and this is where your data is stored, and all of the information
2:47
about your database schema is stored in that data file. But then all of the transactions that are
2:54
run, and effectively anything that changes in the database, data or schema, is written into the
3:01
transaction log. And SQL Server does everything it can in memory, because it's much quicker to
3:09
access memory for both reads and writes than it is to go to disk. So those changes are written to
3:18
the log buffer in memory, and then that log buffer is flushed to disk, and it's flushed to disk
3:26
at two points. One, when you commit a transaction, it will flush that buffer to disk
3:33
or when that log buffer fills up. And so the data pages that are associated with your changes
3:40
can remain in the buffer, because like I said, it's much easier and quicker for SQL Server to go
3:48
and get those pages out of memory than it is to write them to disk and then pull them back
3:53
into memory. So quickly how the tran log works. And then just in terms of transaction log
4:01
architecture, when you create a database and that transaction log file gets created
4:08
it is divided into logical units that are called virtual log files or VLFs. And a VLF can have one
4:16
of two statuses. It can be active or it can be free. And if a VLF contains log records that SQL
4:26
Server needs for something, that VLF will be active. And you're always going to have at least
4:32
one active VLF because SQL Server needs somewhere to write those log records too. And if you're in
4:39
simple recovery mode, a VLF can be freed. If the VLF doesn't contain any log records that SQL
4:46
server needs, a checkpoint operation will free VLFs. And you can manually run a checkpoint
4:55
In Management Studio, you can just type checkpoint and hit execute, and it will run a checkpoint
5:01
Under the covers, SQL server runs a checkpoint approximately every 30 seconds
5:06
Now, if you're in full recovery, you need to run a log backup to free a VLF or free VLFs
5:13
And this is why if you are running databases in full recovery that are transactionally active, you want to be running log backups on some schedule to free up VLFs
5:27
And reasons that SQL Server may need a log record are for rollback
5:32
And I'll explain more about rollback when we get into crash recovery, but then things like high availability
5:38
So if you are running availability groups, for example, log blocks are being transferred from your primary replica to your secondary replicas
5:50
So the VLFs, the logs, log records in the VLFs may be required for high availability as well
6:00
So to give you a visual of what this looks like, this is a very simple example
6:05
I've got a transaction log file with four VLFs, and this is, for example, a newly created database
6:15
So VLF1 is active because I need somewhere to write those transaction log records
6:20
VLFs 2 through 4 are free. So at this point, I start transaction 1, and transaction 1 completes
6:29
And while transaction 1 has been running, those log records have spanned into VLF2
6:35
TRAN 2 starts and completes, and TRAN 2 has spanned into VLF 3, so I now have three active VLFs
6:46
Now TRAN 3 starts, but TRAN 3 has not committed. You can see because of the arrow, that indicates that that transaction continues to run
6:57
So at this point, I have four active VLFs and one open transaction
7:04
So at this point, if I run a log backup or a checkpoint, depending on the recovery model of my database, I free up VLFs 1 and 2
7:17
VLFs 3 and 4 are still active because TRAN 3 is currently open and SQL Server may need to roll that transaction back so it maintains those VLFs as active So at this point if I don run if I get here and I don run a log backup or checkpoint what will happen
7:40
There are two different things that can happen. The first is there's a setting when you create a database on the log file, actually on all of the files in the database called Autogrow
7:53
And what that says is when I reach the end of a file, SQL Server will ask Windows for space and grow that file by the amount specified
8:05
So if I don't have autogrow on and I hit the end of my file, what happens is a computer malfunction
8:14
Effectively, you'll start to get error messages in your error log that say
8:19
I can't write to this database because there is no space in the transaction log file
8:26
So that's bad. You don't want that to happen. I believe the error message, the error number is 9003
8:33
and it will specify the database and it will tell you what file it is that can't be written to
8:40
Now, if you do have Autogrow on, what will happen is Trant 3 will hit the end of this file
8:45
and it will add a VLF or VLFs, depending on some factors
8:54
If you're running a version of SQL Server prior to 2014, there's a fixed number based on the size of your Autogrow parameter
9:04
It's either 4, 8, or 16. and then after 2014, there's a more complicated formula that runs
9:12
and it takes into consideration the existing size of your log file
9:17
the size of your autogrow, and then it will add either 1 or 4, 8 or 16
9:24
And the reason that they've tried to reduce the number of VLFs that are added is that the more VLFs you have, the harder SQL server has to work
9:33
to process the transaction log. And that includes things like transaction log backups
9:40
It includes rollback, which I'll talk about in a second. And it also involves any sort of high availability
9:47
So if you're running an availability group and you have more VLFs
9:53
SQL Server has to do more work to process that transaction log
9:58
So that is TranLog architecture. Simon, do I have any questions at this point? Frank
10:06
we are good to go now okay alright so going to talk about
10:12
crash recovery and so I said any relational database system is going to use transactions
10:20
and two of the things that uses transactions to do and transaction log
10:24
in SQL Server's case are to maintain consistency and durability and what that means
10:30
is that if I make a change to a database. If I run a delete, and let's say the table I'm deleting from has foreign key
10:39
constraints, the foreign key constraints at the end of that delete have to be processed. And so I
10:46
have to go from one consistent state in the database to the next. Durability means that as
10:52
soon as that transaction commits, I've got to be able to have that change durable in the database
10:59
It's got to maintain no matter what happens after that point. And so to maintain consistency and durability, when the SQL Server process restarts
11:13
it scans through the transaction log for each database, and it checks for two things
11:23
And to do this, it runs through three phases. And the first phase is ysis, and that's where it checks for transactions that were written to the log
11:33
So transaction committed, the log blocks associated with that transaction were flushed to the log file on disk
11:42
but the data pages associated with that change were still in the buffer at the time of the crash
11:48
The other thing that it checks for is transactions that were open at the time of the crash
11:54
For example, I've got a delete statement that's going to delete a million rows out of my table
12:00
I get halfway through, so I delete 500,000 of those rows, and my system crashes
12:07
When SQL Server comes up, it will see that transaction that is halfway complete
12:13
And because of another feature of relational databases called atomicity, a transaction is all or nothing
12:23
So it can't have that transaction be halfway done, and it will roll those deletes back
12:30
So once the ysis is done, it will redo any transactions that have committed, have been written to the log file, but not to the data file
12:40
And it will write those pages back out to the data file, and then it will undo those uncommitted transactions
12:48
And to show you what this looks like, once again, I've got my four VLFs, and it runs that ysis from the oldest checkpoint going forward
13:02
And this will allow it to find both the transactions that committed but did not flush the disk
13:09
It does the redo from that oldest uncommitted transaction going forward and then the undo from that oldest checkpoint going backwards
13:20
And this can cause some problems if code is not written to process transactions in an optimal way
13:29
And a phone call that I've gotten a number of times in my career is it's four o'clock on a Thursday
13:36
an application area calls and says, hey, we kicked off a delete against this table at eight this
13:43
morning. Can you kill it? Because it's still running and we want to kill it because it's
13:49
causing blocking. Now, you can kill it, but what you need to tell them is that because of this undo
13:56
process, when you kill that transaction, it is going to roll that transaction back. And that
14:04
rollback can take as long, if not longer, than the process going forward because it has to process
14:11
each of those deletes individually. And this is a reason, and I've got presentations about batching
14:17
transactions. Batching transactions is a good idea. And the same thing is true if that delete is
14:24
running for eight hours and the system crashes or an availability replica fails over, that same
14:31
undue activity has to take place. So that is how it works prior to 2019. With 2019
14:41
there is a new concept called accelerated database recovery. And ADR introduces a number of new concepts The first one of these is the persisted version store And what this is is a version store that is at the database level So you turn this feature on per database And when you turn it on this version store gets created in the
15:09
database. And as you are modifying data in the database, the previous record, so I go to update
15:20
row. I go to change column in that row. Prior to that change happening, the previous version of that
15:28
row gets written into the persistent version store. And that way, if I have a long-running transaction
15:35
in the prior world, when that transaction was rolling back, anybody trying to access the row
15:43
or potentially the table that was being modified would be blocked for the duration of that rollback
15:49
Now, when that transaction rolls back, anybody trying to hit that table and pull that row back will get the version out of the persisted version store
16:02
And my rollback can continue, but other transactions can continue to process
16:08
So that is where changes to the data happen. There are a handful of non-versioned activities that go on
16:18
Things like system metadata changes or locks for data definition language or cache invalidation that can't be versioned
16:27
Those are written into an in-memory log stream called the S-log, and that activity is much less in volume than transactional activity in an OLTP system
16:41
So the amount of data that's written there is minimal, and it's also resonant in memory
16:47
So that will speed things up. And then finally, there's a cleaner process involved in this
16:53
So I'm writing these versions out to the Persistive Version Store. At some point after changes have been made and things have been flushed both to log into disk or to data, I don't need those versions anymore
17:06
So there's a cleaner process that comes through and cleans those up
17:12
So just to show you what this looks like. Once again, I've got my four VLFs
17:17
I run my ysis from the oldest checkpoint going forward, but the difference is that I have just to do my redo from that streaming log
17:30
So this is that in-memory area that is tracking non-version changes. So because volume is lower, this is in-memory, that runs very quickly
17:40
I then redo from my transaction log, but I'm only doing the redo from that oldest checkpoint going forward
17:49
And because that checkpoint runs approximately every 30 seconds, that volume of data is very low
17:56
And then finally, I have an undo from that streaming log. So once again, low volume of changes to the S-log in memory runs very quickly, and I am done
18:10
So the benefits of accelerated database recovery are faster crash recovery, faster AG failover, and faster rollback
18:21
And then an upshot here is that because SQL Server can truncate the log more quickly and more often, your transaction logs will be smaller
18:34
So in the case of that delete, let's say I'm running that delete, I am going to generate log records for all of those deletes
18:42
And while that transaction is open, the VLFs that are being written to and potentially created because of that delete can't be freed
18:53
Because ADR allows those VLFs to be freed more frequently because those log records are no longer needed for rollback
19:02
it allows that truncation to happen more quickly and more often. So the other benefit here is that this is available in standard edition
19:14
which is a big deal because this ADR changes the way that the transaction log processes
19:21
It changes the way that you can write applications and maintain your log file
19:32
And because it's available in standard edition, it's available to pretty much anybody running SQL Server
19:37
And this was announced in November of 2018, the year before 2019 released
19:44
And when I heard about it, I was asking people I knew at Microsoft, is this going to be enterprise only
19:49
And they said, we don't know. We think so, but we're not sure. And so when it was announced with the release of SQL Server 2019, they announced it was in standard edition
20:00
And I was sitting in a room in Seattle, and I looked quite like this
20:04
It was very, very happy because, as I said, it is kind of a game changer
20:10
So, any questions about ADR at this point, Simon? No, no. Okay
20:21
I am going to jump to a demo. Can you see Azure Data Studio
20:28
Yes, but maybe a little bit or at least a part. All right, let's see
20:36
How's that? Yeah, this is better. Okay, great. Okay, so I'm going to run a demonstration of Accelerated Database Recovery
20:47
And as I said, these scripts will be available to you. And what I've got here is a Create Database statement
20:54
And I've already done this, so you don't have to sit through this to create and populate
21:00
But I'm creating a database called White Sox. It has a data file and a log file
21:06
And then I am adding a file group specific for my persisted version store
21:11
And this is something that you can do, and it is recommended. And then I am creating a table that has two columns, an int column and a car 7000 column
21:22
and then I've inserted 100,000 rows into it. Those rows all have the same value except for that primary key
21:32
And then I have turned, or I've verified that accelerated database recovery is off
21:40
And as I said, this is a database level setting. So in the SysDatabases table, there is a column and my results are saved here
21:48
And you can say that that is set to zero. So that is off
21:52
So what I'm going to do now is confirm that that's off and then open a transaction
22:01
So I've explicitly coded a begin transaction, and I'm going to delete all of the rows from that table and leave that transaction open
22:10
So I'll kick this off. It should run for about 30 seconds
22:14
And then once that's done, I'm going to check to see how much space is used in my log
22:21
and then I'm going to run a checkpoint explicitly and see if that frees any space So give this another second
22:42
All right, so it deleted the 100,000 rows in approximately 31 seconds
22:47
So if I run this, it should come back pretty quickly. And you can see that 78.66% was used, and the same amount with a little bit extra was used post-checkpoint
23:03
So that checkpoint did not free any space in my logs. Now I want to roll back those deletes, and as I said, it's going to effectively generate an insert statement for every row that I deleted
23:17
So this should run in about the same amount of time
23:30
Time always seems to go longer when you're doing a demo. Okay, so 32 seconds took to roll back
23:53
So I'm now going to enable Accelerated Database Recovery for my database
23:58
This is a database level change. It doesn't require a restart of SQL Server
24:04
So I'll run that. It runs pretty much immediately. Run the same deletes. So I'm beginning my transaction, deleting all the rows
24:13
And this should run a little bit quicker, fingers crossed. And once that completes, I'll do the same check of log space usage
24:23
All right. So 11 seconds this time versus 31. I run my log space usage. You can see that prior to my checkpoint, I used 25% of my log. So I used
24:37
less, more than a third less, or two thirds less than the usage without ADR. And then my checkpoint
24:45
freed up about 18%. So effectively, I'm using, after that checkpoint, about a tenth of the log
24:53
space that I used on the front end. And this is because of the persisted version store and the
25:00
fact that SQL Server can truncate that log more quickly or reuse those VLFs more quickly
25:05
And then the really cool thing about this is I'm going to roll this back. And where before
25:13
it took about 31 seconds going forward, 33 seconds to roll back. My rollback is now done
25:20
in six milliseconds because the rollback is still going on behind the scenes
25:28
but from a SQL Server's perspective, it will be able to access those rows
25:34
from the persisted version store. So that's why I say this is a real game changer
25:39
both in terms of the way that it handles transactions, as you see here through the rollback
25:44
also for availability group failovers. It will speed that process up dramatically
25:50
So that is accelerated database recovery with the demo. Flip back over to my slides
25:58
And Simon, if you get any questions coming through, just let me know when I pause
26:04
Sure, Frank, let you know. All right. So the next thing I want to talk about is in-memory tempdb metadata tables
26:13
So if you've worked with SQL Server and you've worked with tempdb
26:16
you'll know that tempDB is one of the system databases in SQL Server
26:21
And it's used for a number of different things. You can create temporary objects there
26:26
You can use it for sort space. And everybody uses it. And it can cause contention
26:32
And one man's opinion, this is from a presentation that I saw probably about six, seven years ago
26:38
Brent Ozar talked about tempDB as SQL Server's public toilet. But you don't know what people are doing and you want to get out of there as quickly as possible
26:48
And there's a link in the slides to more description about that presentation
26:55
So there's an old contention problem in Temp TV. And that is that, as I said, when you create a database, you get a data file and a log file at minimum
27:07
And that data file contains metadata pages that track page allocation in the database
27:15
So as you're creating an object, there are pages that tell SQL Server what pages are available
27:24
And in a regular user database, these allocation pages aren't a bottleneck
27:31
because when you create a table in a user database, you aren't generally doing a lot to it
27:38
You create a schema, and that schema is fairly static. You may add a column, you may change a data type, but that table is going to remain in your database
27:49
in TempDB because you're creating and deleting temporary objects on a regular basis
27:56
those pages can act as a bottleneck. And so the old contention solution is to add data files to TempDB
28:05
And the general rule of thumb is to start with eight. And then if you still have problems, look at adjusting that number up or down
28:14
And what that does is increases the number of those metadata pages, and it allows SQL Server to effectively parallelize that activity
28:25
So that's great, but that pushes that contention down the road a little bit
28:32
And that new contention is that as you are creating and deleting those objects, there are system tables that store metadata about those objects
28:44
And once again, this is usually a problem because those objects are static in a user database
28:49
but because the tempdb tables are constantly created and deleted, you can get a bottleneck
28:54
And the problem here is page latch weights. And once I kick off my demo, I'll explain a little bit more about what page latch weights are
29:04
But the solution to this problem is to put those tempdb system tables into memory
29:11
And what this does is it eliminates latch contention. And I'll explain how it does that using in-memory while I'm running the demo, which I will jump to now
29:24
All right. So for this demo, I have a second database called Chicago White Sox
29:33
And all Chicago White Sox contains is a stored procedure called Let's Go Sox
29:39
and what that stored procedure does is it creates a temp table
29:44
So it's going to create that temp table, and I'm going to run this a bunch of times
29:49
I want to validate that the in-memory optimization for tempDB is disabled
29:56
and to do that, I am going to run a... I'm sitting on this command file, and this is going to disable the optimization, and this is an instance level setting
30:11
So when you change this, you have to stop and start SQL Server
30:16
You've got to restart the service, and this command file will do that. I'm running this in a container, so it's made the change
30:23
It's going to stop my container, and then it will start it. And you can do this on a regular instance of SQL Server
30:31
You just need to modify this script to stop and start your SQL Server service
30:38
So this will take a second. And I was having some issues with this demo this morning
30:47
So I'm going to give this a shot. And if it doesn't work the way I expect it to, I have all of the results saved
30:54
All right. So SQL Server has stopped and started. and I'm going to attempt to run this
31:02
This is a utility from Microsoft called O-Stress, and this is available for free download
31:09
And what this does is it connects to my instance, and it's going to execute the store procedure, Let's Go Socks
31:18
which is going to create that temp table repeatedly, and it's going to stand up 20 connections that will run that procedure
31:26
2,000 times. So that is 400,000 for 40,000. It's going to execute that procedure 40,000 times
31:35
And fingers crossed, this works the way I want it to. And it does. So while that is running
31:42
I'm going to execute this query. And what this query is going to show me
31:46
is all of the occurrences of page latch weights. So if we scroll down in here
31:57
you can see that I have approximately 20 page latch weights. And this is page latch shared and page latch exclusive
32:07
And these are all on database ID 2, which is the tempDB database in the first file
32:14
which is my one data file in TempDB because I haven't optimized this
32:21
And they're all hitting page 116. So flip back over to my slides
32:28
And this is how latching works. So I've got my data file with my data pages in it
32:34
And this is where the data in TempDB resides. And when I go to update one of those pages, it reads it into the buffer cache
32:43
and while it is in the buffer cache, it takes an exclusive latch on that page
32:50
So if you're familiar with locking, locking is to protect the data pages themselves
32:57
Latching is to protect that page in memory. So it prevents anything else from hitting that page in memory while that exclusive latch is held
33:07
So I make my update, that exclusive latch gets released, and the update completes
33:15
Now, because those metadata pages that I talked about are accessed for each creation or deletion of a temp table
33:22
and each of those accesses requires a latch, an exclusive latch, things are going to run into contention because they're going to be trying to hit those same pages
33:33
So I'll flip back over, and we are now complete with the O stress
33:43
And let's see. It took a minute and 50 seconds to run
33:50
So what I'm going to do now is I am going to enable tempDB optimization
33:57
I run this command file. Once again, it's going to turn that setting on and it will stop and start the service
34:06
Once that finishes, I'm going to execute O stress again with the same number of users and the same number of sessions
34:18
And while that's running, I will talk you through how the in-memory processing works
34:23
OK, so this is where I'm going to cross my fingers because this is the part that was failing earlier
34:34
And it is failing now and I have to figure out why it is failing but I going to stop that from running And what you would have seen had that run successfully and you can trust that I don have anything up my sleeve
34:51
When I ran this query again to see what page latch weights I had, I didn't get any results back
34:58
And the reason for that is that there are no pages to latch in memory
35:04
What I would have seen, though, is that this query will pull back a list of memory optimized objects
35:12
And these are all objects in tempDB that are now in memory
35:18
And this syschema object is the metadata table that is written to when temp tables are created and then deleted
35:28
And so you would have seen these numbers continue to go up. So just to explain how that works or how SQL Server does this is instead of pages, when you have a table in memory, instead of that data residing in pages, you are now dealing with individual rows
35:47
So each row in the table is written into memory. And that row has a structure with a row header and then the row data associated with that page
35:57
and that row header contains a number of different pieces of data
36:03
The two most important ones are the start timestamp and the end timestamp
36:08
You also have the statement ID of the statement that generated the row
36:12
and then information about the indexes in that row. But what happens is I do an insert into an in-memory table
36:22
My start timestamp is the time of that insert. and then for a row that has been inserted but not modified
36:31
my end timestamp will be high values. And in this case, my statement ID is one
36:38
So then if I update that row that has been inserted, my end timestamp for my inserted row gets switched to the start timestamp of the update
36:49
and then the end timestamp of my update is that high value
36:54
And what this allows SQL Server to do is if somebody comes in to access this row with a timestamp of 60, because 60 is between 50 and 90, it's going to hit this row
37:09
If somebody comes in with a timestamp of 120, because 120 is greater than 90, it's going to hit this row
37:16
And you'll hear this referred to as optimistic concurrency. In the standard world of SQL Server, where things get locked and latched, that is known as pessimistic concurrency
37:29
So this is how a SQL Server is able to do that
37:33
And I don't have the numbers because I wasn't able to run O-Stress a second time
37:40
But O-Stress, the process will run more quickly with this enabled. So this is the second feature
37:50
You've got ADR, you've got in-memory temp-tb metadata. So those are the first two features I wanted to talk about
37:58
And the last thing I want to talk about is persistent memory
38:02
And this is a hardware change or a hardware improvement. And SQL Server 2019 is now set up to take advantage of it
38:13
and just kind of at a general level, before persistent memory, RAM is transient
38:22
So you have memory on your server. Anything that's written into that memory
38:27
when the server restarts, that will all be lost. And this is why the transaction log buffer
38:35
is flushed to disk on every commit because the log buffer lives in RAM
38:39
If it doesn't flush it out and the server restarts, you're going to potentially lose transactional data, which you can't have happen
38:49
And because of the SQL Server, when it goes to flush log buffer to disk, it's got to wait until that flush completes
38:58
So the confirmation has to come back. And this allows that redo and undo process to take place
39:05
And just to show you what this looks like. So I've got my data file
39:08
I've got my log file. I read a page from my data file into my buffer cache
39:14
I make an update to that page and the log records associated with that transaction are written to the log buffer On commit those log blocks are flushed out to disk and then the transaction commits completely And then at some point a lazy writer process
39:35
comes through and writes that data page back to the data file and disk. With persistent memory
39:43
what persistent memory is, is this new technology that is effectively RAM with a battery on it
39:50
And so when your system shuts down, that battery allows the RAM to persist what was in there
39:58
And so the data that's stored in RAM can survive a restart. And this provides a couple of different opportunities for performance enhancements
40:06
The first of them is the persistent log buffer cache. So if you put your log buffer and you can tell SQL Server, I've got one of these persistent memory sticks in my server
40:18
put my log file there, my log buffer there, that log flush is no longer necessary on commit
40:25
It can happen in the background just like the flush of the data pages does
40:31
So then that persistent buffer is processed with the log file on a restart
40:36
So if you remember back to the crash recovery slides, it basically just appends the log buffer onto the disk-based log file that it's processing
40:47
and runs through that recovery process. And the way that this looks or how this looks is I've got my data file
40:55
I read a page from disk into my buffer cache. I run my update, and my update's written to the log buffer
41:02
but I don't have to, I can commit the transaction, and those log records don't have to flush out because of the fact
41:11
that this log buffer is going to persist with persistent memory. Then there's a lazy writer process that will later flush those log blocks to disk
41:21
and then a lazy writer process that will flush that data page back to the data file
41:27
The advantages of this are because log buffers aren't flushed to disk on commits
41:33
it will speed transactional processing. Basically, commits can happen as effectively fast as they do
41:39
You don't have to wait for that recovery. and the sort of practical result of those log buffer flushes
41:49
is if you've ever seen log write weights as a weight type for a SQL Server database
41:55
that's what a log write weight is. It is waiting for that flush, the acknowledgement of that flush to come back
42:04
So that is the first advantage of the persistent log buffer cache
42:10
The second one is what is called the hybrid buffer pool. And I believe it was SQL Server 2012
42:17
they introduced a feature called buffer pool extension. And we talked about the buffer pool
42:24
where these data pages are being written. That has a finite size
42:30
You can only put so much RAM in your server. So if I have a two terabyte database
42:34
and I only have one terabyte of RAM in my server, I can't read all of my data into the Buffer Pool at the same time
42:43
And what Buffer Pool Extension allowed you to do was to install an SSD drive
42:48
a fast SSD drive in your server, and you could tell SQL Server
42:52
okay, use the SSD drive as an extension of my memory. Now, that's not as fast as memory by a long shot
43:01
but it's still faster than having to go to a traditional disk and pull data back into memory
43:08
So what hybrid buffer pool does is it extends that idea and puts the buffer pool extension onto this persistent memory
43:18
So it gives you the advantage of BPE. It allows you to extend the size of that RAM without the IO overhead of the SSD
43:28
A couple of things to note here. PMEM is not as fast as traditional memory. So if your entire database will fit into RAM, so I've got one terabyte of RAM on my server and my database is only 500 gig in size, this is not going to buy you a performance improvement
43:51
But if you're in the case where you can't put as much RAM as databases on your instance, this can be a performance improvement
44:01
And the final thing I talk about in terms of PMEM is what is called Enlightened I Now this is a Linux feature And as you probably know SQL Server will now run on Linux and this can only be enabled in Linux And what this allows you to do is place your data and log
44:21
files directly on this persistent memory. And in Linux, this allows you to bypass the file system
44:29
and the storage stack when you are accessing data. So it's actually just going straight to memory
44:36
And so it will speed things up considerably. Once again, because PMEM is not as fast as traditional RAM, this won't buy you anything if your database fits into memory
44:51
But these are all things that you can do to leverage these persistent memory modules
44:59
So with that, I will explain that I don't have a persistent memory demo
45:04
And this screenshot is a couple of weeks old, but the reason why is this is a page from CDW
45:12
And this is for a single Intel Optane persistent DDR stick. So this is 512 gigabytes in size
45:22
And it, as of about two weeks ago, was just over $19,000
45:28
So this is not a hobbyist's hardware. This is something for a very kind of high-end OLTP system where milliseconds and microseconds count in terms of transactional processing
45:43
But if you are in that kind of an environment, this is something that you may want to look at putting into your servers to speed up the processing of your databases
45:56
So that brings me to the end. Just to review what we've covered, talked about the transaction log and transaction log architecture and how accelerated database recovery changes the crash recovery process
46:10
Talked about in-memory TempDB metadata and how that reduces contention that remains after you expand the number of TempDB data files you have to get around that metadata page contention
46:24
And then finally, persistent memory, how it's used for the persistent log buffer cache, for hybrid buffer pool, and enlightened IO
46:35
And then I've got a number of resources. The slides and scripts are on my GitHub, which is github.com slash ScreebyDBA, and they are in a folder called, a repo called Minnesota Presentation, because I did this initially for SQL Server User Group in Minnesota
46:52
but they have been updated. So what you are seeing is what is up there now
46:59
These are Tiger Team materials and these are a series of scripts
47:05
And there are slides as well from the Tiger Team, which is the SQL engineering team at Microsoft
47:11
And the demo code that I've run through has been modified from demo code
47:16
that was built by the Tiger Team. And so those original scripts
47:20
along with a lot of other information is up here. If you're interested in SQL Server internals
47:28
this goes through the internals of new features in 2019. And there's also a lot of really good information
47:34
if you're interested in other Azure data features, machine learning being a major one
47:43
There's a lot of great information up here from Buck Woody. This is the Microsoft Doc about hybrid buffer pool
47:50
and then more information about accelerated database recovery from a Microsoft documentation perspective
47:58
And then if you are an internals geek like I am, this is a white paper that was written by the engineering team
48:05
that gets into the internals of accelerated database recovery and how it's actually working sort of at the log level
48:14
This refers to it as constant time recovery because it's Microsoft and we can't have one name for a feature
48:22
And then finally, a link to more detailed information about in-memory TempDB metadata
48:28
And there's a really great short presentation here from Pamela Hood, who is the program director for TempDB
48:38
And she does a really nice job of explaining it. I learned everything I know from her
48:42
So you can go to the source
#Data Management