bpe编码
This third part of the BPE series shows the basics of Buffer Pool Extension and know how to implement and troubleshoot it.
BPE系列的第三部分介绍了缓冲池扩展的基础知识，并且知道如何实现和解决它。
During all the Buffer Pool Extension (BPE) series we have been explaining base concepts related to this new technology, as well as its benefits. We understood how the Buffer Pool works and the main benefits of implement the Buffer Pool Extension together with In-Memory technology. I recommend you to read the previous articles, if you are not familiarized with those concepts, so everything into this article will make more sense 🙂
在所有的缓冲池扩展（BPE）系列中，我们一直在解释与该新技术有关的基本概念及其优点。 我们了解了缓冲池的工作原理，以及与内存技术一起实施缓冲池扩展的主要好处。 我建议您阅读以前的文章，如果您不熟悉这些概念，那么本文中的所有内容都会更有意义🙂
We can describe the Buffer Pool Extension as “another cache level”, and in fact it is another level of cache. Microsoft describes it as “A caching architecture that can take advantage of present and future low-cost memory drives”.
我们可以将缓冲池扩展描述为“另一个缓存级别”，实际上它是另一个缓存级别。 Microsoft将其描述为“一种可以利用当前和将来的低成本内存驱动器的缓存体系结构”。
Observing the image, we can relate the speed with the role of each component. TheBuffer Pooluses the RAM memory, since it needs the faster possible response. The RAM memory is a volatile kind of memory, so as soon as the system turns off we lose all the data stored.
Thedatabase filesare stored in the non-volatile storage, normally mechanical spindles, where the storage capacity matters more than the speed. In the end of the day, its role is store as much data as it can. We have some kinds of storage available, some with a good performance, others with a higher storage capacity, but the Solid-State Drives, SSD, are evolving more and more, being more reliable, offering more capacity and, maybe the best of all, becoming affordable.
观察图像，我们可以将速度与每个组件的作用相关联。缓冲池使用RAM内存，因为它需要更快的响应速度。 RAM内存是易失性的一种内存，因此，一旦系统关闭，我们就会丢失所有存储的数据。
数据库文件存储在非易失性存储器（通常为机械主轴）中，存储容量比速度重要得多。 归根结底，它的作用是存储尽可能多的数据。 我们有一些可用的存储类型，一些具有良好的性能，另一些具有更高的存储容量，但是固态驱动器SSD正在不断发展，越来越可靠，提供更多的容量，也许是最好的。 ，变得负担得起。
For who don’t understand the “fame” of Solid-State Drives, it is a kind of non-volatile storage device without moving parts. SSDs are based on semiconductor integrated circuits, using NAND-based flash memory. This the main characteristic that differs SSDs from magnetic systems, as HDs, or optical, as CDs and DVDs. This kind of disk is more resistant to shock, have a better latency and lower access time. Another advantage in not have moving components, as well as no spinning disks, is that it produces less noise (or I can dare to say zero noise), less heat energy is issued, which consequently consumes less electricity.
对于不了解固态驱动器“名声”的人来说，它是一种不带移动部件的非易失性存储设备。 SSD使用基于NAND的闪存基于半导体集成电路。 这是SSD不同于磁性系统（如HD）或光学系统（如CD和DVD）的主要特征。 这种磁盘具有更好的抗震性，更好的延迟和更短的访问时间。 没有活动部件以及没有旋转盘的另一个优点是，它产生的噪音更少（或者我敢说零噪音），发出的热能更少，因此消耗的电量更少。
All those factors, make SSDs faster than the conventional storage. Of course there are constraints, unfortunately SSDs are not only based on good things…. At least so far. Well, talk about SSDs is not the main interest here… All of this was a way to explain that SSDs are much closer to the memory speed than mechanical disks. But even being much faster, the memory is unbeatable.
所有这些因素使SSD的速度比传统存储更快。 当然存在限制，不幸的是，SSD不仅基于美好的事物……。 至少到目前为止。 好吧，在这里谈论固态硬盘并不是主要的兴趣……所有这些都是一种解释固态硬盘比机械磁盘更接近内存速度的方式。 但是，即使速度更快，内存也无与伦比。
Taking advantage of SSD characteristics, Microsoft decided to create anextension of the Buffer Pool, based on that kind of storage devices, avoiding this way the access to slower disks in order to copy pages, in a low memory scenario.
利用SSD的特性，Microsoft决定在这种存储设备的基础上创建缓冲池的扩展，避免在内存不足的情况下以这种方式访问较慢的磁盘以复制页面。
特征特征 ( Feature characteristics )
The Buffer Pool Extension was introduced on SQL Server . Its object is create a hot-area based on evicted pages from the Buffer Pool, using solid-state drives to extend the Buffer Pool. We will talk in details how this flow works.
缓冲池扩展是在SQL Server 上引入的。其对象是使用固态驱动器扩展缓冲池，基于从缓冲池中逐出的页面创建热点区域。 我们将详细讨论此流程如何工作。
The Buffer Pool Extension is only supported on 64-bit systems, but the good news is that we don’t need an Enterprise Edition to enable the feature!! In a product where the nice features are more and more requiring the Enterprise Edition this is an exception, so if you plan to deploy (or already have) a Standard Edition of SQL Server , this is a good news, you can use the Buffer Pool Extension.
缓冲池扩展仅在64位系统上受支持，但是好消息是我们不需要企业版即可启用该功能！ 在功能越来越强大的产品要求企业版的情况下，这是一个例外，因此，如果您计划部署（或已经拥有）SQL Server 的标准版，这是个好消息，则可以使用缓冲区池扩展。
As the Buffer Pool Extension introduces another level of cache, avoiding evicted pages to be “lost”, theOLTP workload is improved.
To better explain this, let’s pretend that a user ran an ad-hoc query that moved a good number of pages into the Buffer Pool. All those pages will remain there, until SQL Server decides that a set of, or all those pages, are not interesting and are just taking the precious memory space assigned to the Buffer Pool. On that moment, the Buffer Manager will evict the selected pages. Nevertheless, SQL Server cannot predict what a user is going to do… So what if the same query runs again? All the evicted pages need to be fetched from the disk…and this is an expensive operation. By enabling the Buffer Pool Extension, this will not happen. Instead of just evict the page, the Buffer Pool Extension will analyze if the page is something interesting, and retain it in the SDD. This way, if the same case happens, we avoid to access a slow disk, and just copy the requested page from the SSD to the memory. This is the basic concept of the Buffer Pool Extension: create an intermediate between the very fast memory and the very lazy storage.
由于缓冲池扩展引入了另一级缓存，避免了被收回的页面“丢失”，因此OLTP工作量得到了改善。
为了更好地解释这一点，我们假设用户运行了一个临时查询，该查询将大量页面移入了缓冲池。 所有这些页面都将保留在那里，直到SQL Server决定一组页面或所有这些页面不再令人感兴趣，并且仅占用分配给缓冲池的宝贵内存空间。 在那一刻，缓冲区管理器将逐出选定的页面。 但是，SQL Server无法预测用户将要做什么……那么如果再次运行同一查询该怎么办？ 所有被逐出的页面都需要从磁盘中获取……这是一项昂贵的操作。 通过启用缓冲池扩展，将不会发生这种情况。 缓冲池扩展将分析页面是否有趣，而不是仅退出页面，并将其保留在SDD中。 这样，如果发生相同的情况，我们将避免访问速度较慢的磁盘，而只是将请求的页面从SSD复制到内存中。 这是缓冲池扩展的基本概念：在非常快的内存和非常懒惰的存储之间创建一个中间层。
By implementing the Buffer Pool Extension,no application changes are needed, as it’s transparent. The Buffer Pool Extension is projected to be encapsulated in a way that all the SQL Server components don’t notice that it’s enabled. Just the Buffer Manager interacts with the SDD, in order to transfer pages to and from the Buffer Pool Extension.
通过实现缓冲池扩展，不需要更改应用程序，因为它是透明的。 缓冲池扩展计划以所有SQL Server组件都不会注意到已启用的方式进行封装。 只是缓冲区管理器与SDD交互，以便在缓冲池扩展之间来回传输页面。
Another important advantage is that enabling the Buffer Pool Extensionwill not take your data integrity in risk. The Buffer Pool Extension only deals with Clean Pages (remember the article about the Buffer Pool?) and this means that in case of disk failure, no data will be lost, as we have a persisted copy of all the pages that are stored into the SSD. In case of a disk failure, the Buffer Pool Extension feature will be disabled and SQL Server will keep working as usual, this way the BPE don’t represent an additional point of failure andwon’t affect your instance’s availability.
另一个重要的优点是，启用缓冲池扩展不会使您的数据完整性受到威胁。 缓冲池扩展仅处理干净页面（还记得有关缓冲池的文章吗？），这意味着在磁盘故障的情况下，不会丢失任何数据，因为我们拥有存储在该页面中的所有页面的持久副本。 SSD。 万一磁盘出现故障，缓冲池扩展功能将被禁用，SQL Server会照常运行，这样BPE不会再出现其他故障，也不会影响实例的可用性。
Summarizing the Buffer pool benefits:
总结缓冲池的好处：
Improves the OLTP performance.提高OLTP性能。 No application changes are required.无需更改应用程序。 Easy to setup and maintain.易于设置和维护。 Deals with clean pages only – no data loss possibility.仅处理干净的页面-不会丢失数据。
如何处理BPE？ ( How to deal with BPE? )
There’s no user interface to enable this feature, or change the Buffer Pool Extension file path or size. At least, the syntax is very simple and generic:
没有启用此功能或更改缓冲池扩展文件路径或大小的用户界面。 至少，语法非常简单和通用：
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION{ON( FILENAME = 'os_file_path_and_name' , SIZE = [ KB | MB | GB ] ) | OFF }
From this syntax we can enable, disable and change the BPE configuration. And the best: no restart is required, all the changes take effect immediately.
通过这种语法，我们可以启用，禁用和更改BPE配置。 最好的做法是：无需重新启动，所有更改都会立即生效。
Here goes an example onhow to enable the BPEbased in a disk “F” with 30 Gb available:
下面是一个示例，说明如何在具有30 Gb可用空间的磁盘“ F”中启用BPE：
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION{ON (FILENAME='F:\Folder\BPEFile.bpe',SIZE=30GB)}
To disable the BPE, simply execute the following code:
要禁用BPE，只需执行以下代码：
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFFGO
Easy, hum?
容易吗？
And now… how can I change the file path and/or its size? This is simple, just disable the BPE feature and define it again!
而现在……我该如何更改文件路径和/或其大小？ 这很简单，只需禁用BPE功能并再次定义它！
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFFGOALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION{ON (FILENAME='F:\Folder\BPEFile_new.bpe',SIZE=60GB)}
一些备注 ( Some Remarks )
A recommendation from Microsoft is that the BPE file should be sized by values between 4 and 10 times the size of the physical available memory, but of course it depends of many factors…
Oh, remember that if you have an instance with a high amount of writes, the BPE may have no effects. But again, it depends…
Microsoft的建议是，BPE文件的大小应为物理可用内存大小的4到10倍之间，但是当然，它取决于许多因素……
哦，请记住，如果您的实例具有大量写入操作，则BPE可能不会起作用。 但同样，这取决于...
故障排除 ( Troubleshooting )
With this new feature, a new DMV was introduced and new columns were added to an existing one, in order to reflect this new feature. New Extended Events and also performance counters were added. Following you can see the complete list:
通过此新功能，引入了新的DMV，并将新列添加到现有列中，以反映此新功能。 添加了新的扩展事件和性能计数器。 您可以在下面看到完整的列表：
DMVs:
DMV：
sys.dm_os_buffer_pool_extension_configurationsys.dm_os_buffer_pool_extension_configuration sys.dm_os_buffer_descriptorssys.dm_os_buffer_descriptors
XEvents:
XEvents：
sqlserver.buffer_pool_extension_pages_writtensqlserver.buffer_pool_extension_pages_writer sqlserver.buffer_pool_extension_pages_readsqlserver.buffer_pool_extension_pages_read sqlserver.buffer_pool_extension_pages_evictedsqlserver.buffer_pool_extension_pages_evicted sqlserver.buffer_pool_page_threshold_recalculatedsqlserver.buffer_pool_page_threshold_recalculated
Performance Counters:
性能计数器：
Extension page writes/sec扩展页面写入/秒 Extension page reads/sec扩展页读取/秒 Extension outstanding IO counter扩展出色的IO计数器 Extension page evictions/sec扩展页面逐出量/秒 Extension allocated pages扩展分配的页面 Extension free pages无扩展页面 Extension page unreferenced time扩展页面未引用时间 Extension in use as percentage on buffer pool level使用扩展作为缓冲池级别的百分比
The following query, shows information regarding the pages in the Buffer Pool and Buffer Pool Extension, and help to understand if your BPE is being used:
以下查询显示有关缓冲池和缓冲池扩展中页面的信息，并有助于了解是否正在使用您的BPE：
SELECT(CASE WHEN ([is_modified] = 1 AND ([is_in_bpool_extension] IS NULL OR [is_in_bpool_extension] = 0)) THEN N'Dirty' WHEN ([is_modified] = 0 AND ([is_in_bpool_extension] IS NULL OR [is_in_bpool_extension] = 0)) THEN N'Clean' WHEN([is_modified] = 0 AND [is_in_bpool_extension] = 1) THEN N'BPE' END) AS N'Page State',(CASE WHEN ([database_id] = 32767) THEN N'Resource Database' ELSE DB_NAME ([database_id]) END) AS N'Database Name',COUNT (*) AS N'Page Count'FROM sys.dm_os_buffer_descriptorsGROUP BY [database_id], [is_modified], [is_in_bpool_extension]ORDER BY [database_id], [is_modified], [is_in_bpool_extension];GO
Another piece of the puzzle was delivered! This third part of the series showed the basics of Buffer Pool Extension and know how to implement and troubleshoot it. The next, and last, part will dive into the internals of this feature, so if you are curious to understand how it works, I recommend the reading 🙂
另一个难题已经解决了！ 本系列的第三部分介绍了缓冲池扩展的基础知识，并且知道如何实现和解决它。 下一部分，也是最后一部分，将深入研究此功能的内部，因此，如果您想了解它的工作原理，建议阅读🙂
翻译自: /buffer-pool-extension-bpe-implementing-another-level-cache/
bpe编码
