nlp中bpe_缓冲池扩展（BPE）–内存中OLTP：内存挑战

nlp中bpe

In continuation of the previous post “Buffer Pool Extension (BPE) – Introduction to the Buffer Pool”, we will keep diving into the Buffer Pool Extension, this time, talking about another important factor behind this feature – the memory. No, we will not review how the memory works literally, we are talking about the great SQL Server 2014 new feature: The In-Memory OLTP, or the known HEKATON.

在上一篇文章“缓冲池扩展（BPE）–缓冲池介绍”的续篇中，我们将继续深入探讨缓冲池扩展，这是讨论此功能背后的另一个重要因素–内存。 不，我们不会回顾内存的字面工作方式，我们是在谈论SQL Server 2014的新功能：内存中OLTP或已知的HEKATON。

But … Isn’t this a series about Buffer Pool Extension? Yes it’s, anyway the Buffer Pool Extension and In-Memory technologies are complimentary. This way, let’s understand better about In-Memory OLTP and see how the Buffer Pool Extension can help instances with this feature.

但是……这不是关于缓冲池扩展的系列吗？ 是的，无论如何，缓冲池扩展和内存技术是互补的。 这样，让我们​​更好地了解内存中OLTP，并了解缓冲池扩展如何通过此功能帮助实例。

To add more context, for who never heard about the In-Memory OLTP, we will review from where this came from and its benefits.

为了增加更多背景信息，对于从未听说过内存中OLTP的用户，我们将回顾其来源和优势。

Let’s start from the hardware price: A notable memory price reduction was observed, which turned the Mb price very accessible. In the following chart we can understand this reduction pattern since the year of 2000:

让我们从硬件价格开始：观察到显着的内存价格下降，这使得Mb价格非常容易获得。 在下面的图表中，我们可以了解2000年以来的减少趋势：

Effectively, since 2008 the memory price is more stable, with values around $9 per 100 Mb. In other hands, the evolution of CPUs changed: Before we experienced a fast growth of the clock speed (MHz), nowadays this is stable. But during the same period that clock speed reduced its evolution, and the focus passed to be the number of cores. Since 2005, the relative performance of a CPU is still improving, but not in the same speed as before.

实际上，自2008年以来，内存价格更加稳定，每100 Mb约为9美元。 另一方面，CPU的发展发生了变化：在我们时钟速度（MHz）Swift增长之前，今天这种情况是稳定的。 但是在同一时期，时钟速度降低了它的发展速度，而重点转移到了内核数量上。 自2005年以来，CPU的相对性能仍在提高，但速度却没有以前提高。

Recognizing this trend, Microsoft started to build an engine optimized to work with large amount of memory and multi-core CPUs. That was the beginning of the Hekaton project, today known as In-Memory OLTP.

意识到这一趋势，Microsoft开始构建经过优化的引擎，以处理大量内存和多核CPU。 那是Hekaton项目（今天称为内存中OLTP）的开始。

Microsoft released the In-Memory OLTP on SQL Server 2014, but in fact, this has been developed for a longer time. In terms of curiosity, “Hekaton” is derived from the Greek work “ἑκατόν”, which means” Houndred”. The explanation of this is based on its objective: Reach a performance 100 times better than what we have on the regular SQL Server engine. Currently we have reached a value around 30 times faster, which is already VERY GOOD. The goal is to keep improving.

Microsoft在SQL Server 2014上发布了内存中OLTP，但实际上，它已经开发了更长的时间。 出于好奇，“希卡顿”源自希腊作品“ἑκατόν”，意为“猎犬”。 对此的解释基于其目标：性能达到比常规SQL Server引擎高100倍的性能。 目前，我们已经达到了大约30倍的价值，这已经非常好了。 目标是不断改进。

The In-Memory OLTP was integrated into the SQL Server engine, so no new licenses are needed implement this.

内存OLTP已集成到SQL Server引擎中，因此无需新许可证即可实现。

这如何真正起作用？ (How this really works?)

I’ve heard people saying that SQL Server already put “things in memory”, so “we have no advantage in having the In-Memory OLTP working”. In fact, put “things in the memory” is the natural behavior of SQL Server, and we even had the DBCC PINTABLE… But the In-Memory OLTP goes beyond this, as an entire new engine was created – looking to reach the best performance.

我听到有人说SQL Server已经将“事物存储在内存中”，因此“使内存中OLTP正常工作没有优势”。 实际上，将“事物放入内存”是SQL Server的自然行为，我们甚至拥有DBCC PINTABLE…但是内存内置OLTP超越了这一点，因为创建了一个全新的引擎–力求达到最佳性能。

A research was made, and was identified that 80% of the execution time is spent in the Storage Engine and Relational Engine.

进行了一项研究，发现80％的执行时间花费在存储引擎和关系引擎上。

Based on this, two components were created: Memory-Optimized Engine and Compiler. Those components are an important part of the new memory-optimized engine.

基于此，创建了两个组件：内存优化引擎和编译器。 这些组件是新的内存优化引擎的重要组成部分。

内存中OLTP与缓冲池扩展的关系 (The relation of In-Memory OLTP and Buffer Pool Extension)

The In-Memory OLTP itself worth an entire series, so let’s go to the point here: Buffer Pool Extension. What’s the real challenge, looking to the memory point of view, and in what the Buffer Pool Extension can help us in order to have a stable environment?

内存OLTP本身值得一整个系列学习，因此让我们转到这里：缓冲池扩展。 从内存的角度来看，真正的挑战是什么？缓冲池扩展在哪些方面可以帮助我们获得稳定的环境？

The memory utilized by the In-Memory technologies are out of the Buffer Pool scope, so it falls in the “others” category (remember the part 1?), in the SQL Server’s Virtual Address Space (VAS).

内存中技术使用的内存不在缓冲池范围内，因此它属于SQL Server的虚拟地址空间（VAS）中的“其他”类别（还记得第1部分）。

If you don’t remember or need more details about the SQL Server’s VAS, I invite you to take a look in the first article of this BPE series.

如果您不记得或需要有关SQL Server的VAS的更多详细信息，我邀请您阅读本BPE系列的第一篇文章。

Memory Optimized Tables have priority in the memory allocation, so what happens when it grows? Let’s say that it filled all the available free space:

内存优化表在内存分配中具有优先权，那么当它增长时会发生什么？ 假设它填满了所有可用空间：

From that moment, the Buffer Pool won’t have room to growth, and as a result of this, a lot of paging may start to happen resulting in a wave of bad symptoms.

从那时起，缓冲池将不再有增长的空间，因此，可能开始发生大量分页，从而导致一系列不良症状。

If we look for that scenario, we can find the first benefit and great utility for the Buffer Pool Extension. Instead of go to the disk, all the time that a page is needed, we would access the Buffer Pool Extension file, which is supposed to be placed in a fast disk. The Buffer Pool Extension won’t resolve the root problem, but it will minimize a lot the impact.

如果我们寻找这种情况，我们可以找到缓冲池扩展的第一个好处和实用工具。 我们不需要访问磁盘，而是一直需要页面，而是访问缓冲池扩展文件，该文件应该放在快速磁盘中。 缓冲池扩展无法解决根本问题，但可以最大程度地减少影响。

Going ahead, to better understand, what if the space utilized by the Memory Optimized Tables keep growing? As said, the Memory Optimized Tables have priority over the rest. The following image reflects pessimistic scenario that we can experience:

为了更好地理解，继续进行下去，如果内存优化表使用的空间持续增长该怎么办？ 如前所述，“内存优化表”的优先级高于其余表。 下图反映了我们可以经历的悲观场景：

As we can see in the image, the size of the Memory Optimized Tables is increasing and taking all the available free space. Even with no more free space to take, it continues to grow, because it has priority over the other components. As a result of this, the Buffer Pool has a minimum space to operate, making the number of “page-in” and “page-out” operations increase significantly, and consequently accessing more the disk in order to get the needed pages. As side effects of this are a general slowdown of the workloads and transactions on the Memory Optimized Tables failing because of the out-of-memory situation.

如图所示，“内存优化表”的大小正在增加，并占用了所有可用空间。 即使没有更多的可用空间，它也会继续增长，因为它比其他组件具有优先权。 结果，缓冲池具有最小的操作空间，从而使“页面输入”和“页面输出”操作的数量显着增加，并因此访问更多的磁盘以获取所需的页面。 这样做的副作用是，由于内存不足的情况，内存优化表上的工作负载和事务通常会减慢失败。

It’s possible to avoid this to happen, and we have few options:

可以避免这种情况的发生，我们有几种选择：

• Monitor your system and anticipate this situation.
  监视系统并预测这种情况。
• Adequate the memory size accordingly, having enough room to fit all that you need in the memory.
  相应地调整内存大小，并有足够的空间来容纳所需的内存。
• Use the Resource Governor in order to limit the memory consumption.
  使用资源调控器为了限制内存消耗。
• Enable the use of Buffer Pool Extension (BPE).
  启用缓冲池扩展（BPE）。

As you can notice, from the four available options, half are passive solutions, by simply monitoring and defining your server hardware properly, and the other half is using two active solutions, by using SQL Server features to interfere directly in the system in order to avoid the above scenario to happen.

如您所见，从四个可用选项中，一半是被动解决方案，只需简单地正确监视和定义服务器硬件即可；另一半是使用两种主动解决方案，即通过使用SQL Server功能直接干预系统，从而避免发生上述情况。

This article was the second part of the Buffer Pool Extension series, but in the end of the day was a “mix of things”. We introduced the In-Memory OLTP, we understood the relation of the Memory Optimized Tables with other memory components and also shown few options to avoid memory problems when having the In-Memory OLTP in utilization. One of the options here is use the Buffer Pool Extension, which together with the Resource Governor are the best option to prevent the exposed problem.

本文是“缓冲池扩展”系列的第二部分，但最终还是“一堆东西”。 我们介绍了内存中的OLTP，我们了解了内存优化表与其他内存组件的关系，并且还显示了一些选项，可以避免在使用内存中的OLTP时避免出现内存问题。 这里的选项之一是使用缓冲池扩展，它与资源调控器一起是防止暴露问题的最佳选择。

In the next article we will (finally) dive into the Buffer Pool Extension, understanding how it works and how to enable the feature. Thank you for reading!

在下一篇文章中，我们（最后）将深入研究缓冲池扩展，了解其工作方式以及如何启用该功能。 感谢您的阅读！

翻译自: https://www.sqlshack.com/buffer-pool-extension-bpe-memory-oltp-memory-challenge/

nlp中bpe