bpe编码
You already understand the benefits of the BPE and how to deal with the feature, now it’s time to better understand how it works.
您已经了解了BPE的好处以及如何使用该功能,现在是时候更好地了解它的工作原理了。
Here we are, the last article of this Buffer Pool Extension series. We came from the explanation of what is the Buffer Pool, made an introduction of the new In-Memory technology and saw on what the Buffer Pool Extension can help in keep a stable environment, and finally, the part 3 showed how to implement the Buffer Pool Extension in your SQL Server 2014 system.
这是本缓冲池扩展系列的最后一篇文章。 我们从什么是缓冲池的解释开始,介绍了新的内存技术,并了解了缓冲池扩展在保持稳定环境方面有哪些帮助,最后,第3部分展示了如何实现缓冲SQL Server 2014系统中的池扩展。
Now, on this last part, we will add more details on how the Buffer Pool Extension works, and see the behavior of SQL Server is when this feature is enabled.
现在,在最后一部分中,我们将添加有关缓冲池扩展如何工作的更多详细信息,并查看启用此功能后SQL Server的行为。
To refresh what we already saw in the other articles, I’ll add a few points as a reminder:
为了刷新我们在其他文章中已经看到的内容,我将提醒您一些注意事项:
- Buffer Pool Extension is a SQL server 2014 new feature. 缓冲池扩展是SQL Server 2014的新功能。
- This feature is supported also in the Standard Edition of SQL Server. 标准版SQL Server也支持此功能。
- Buffer Pool Extension is only supported in 64 bit servers. 缓冲池扩展仅在64位服务器中受支持。
- You can enable and disable the feature without interfere in the instance’s availability – No restart is required. 您可以启用和禁用该功能,而不会影响实例的可用性–无需重新启动。
- Buffer Pool Extension is a transparent solution – No application change is needed. 缓冲池扩展是一个透明的解决方案–无需更改应用程序。
- Buffer Pool Extension deals with Clean Pages only – No possibility of data loss. 缓冲池扩展仅处理干净页面–不会丢失数据。
- The objective is to improve the OLTP like workload systems, anyway systems with a very high number of writes may not take advantage of this feature. 目的是改进像OLTP这样的工作负载系统,无论如何,具有大量写入操作的系统可能无法利用此功能。
- Enable the Buffer Pool Extension in systems with the In-Memory working is a good practice, as both technologies are complimentary. 在内存中工作的系统中启用缓冲池扩展是一个好习惯,因为这两种技术是互补的。
- The Buffer Pool Extension is nothing more than a file, placed in the fastest disk possible. Anyway, if you have the option to add more memory, better go for it! 缓冲池扩展仅是一个文件,放在最快的磁盘中。 无论如何,如果您可以选择添加更多内存,那就更好了!
I listed the most important points, but if you are reading this article without reading the previous ones, I recommend to check the third part, where I showed how to enable, change and disable this feature as well as the options to monitor and troubleshoot the Buffer Pool Extension using DMVs, xEvents, and Performance Counters.
我列出了最重要的要点,但是如果您在阅读本文时没有阅读前几篇,我建议您查看第三部分,其中显示了如何启用,更改和禁用此功能,以及用于监视和排除故障的选项。使用DMV,xEvent和性能计数器的缓冲池扩展。
这个怎么运作? ( How it works? )
Now that we have all the basic information, we can dive more into details… The objective of the Buffer Pool Extension is to create a group of the most used pages. I categorized this group in two parts: The hot area and the warm area.
现在我们已经掌握了所有基本信息,我们可以进一步深入细节……缓冲池扩展的目的是创建一组使用最频繁的页面。 我将该组分为两部分:热点区域和温暖区域。
As you can see in the picture, the hot area is located on the memory side, where the Buffer Pool is storing its pages. On the other hand, the warm area is the SSD, where the Buffer Pool Extension is located. You can also observe that the pages are moving in cycles, from the Buffer Pool to the Buffer Pool Extension and the inverse, from the Buffer Pool Extension to the Buffer Pool. We will understand better how it works more ahead.
从图片中可以看到,热点区域位于内存一侧,缓冲池在其中存储其页面。 另一方面,最热的区域是缓冲池扩展所在的SSD。 您还可以观察到页面从缓冲池到缓冲池扩展的循环移动,从缓冲池扩展到缓冲池的循环反向移动。 我们将更好地了解它如何在未来发挥更大作用。
With the SQL Server instance being online for a certain amount of time, the almost closed ecosystems will be formed, and a group of most requested pages will be available into this, avoiding the disk access in order to fetch pages. That’s why the performance is improved. You can notice that the page going to the Buffer Pool is happy, and the other pager is not in a good mood… I represented this that way to show that the good place to be is the Buffer Pool, where the “Super Star” pages are located. Worse than being “demoted” to the Buffer Pool Extension, is to be simply evicted, which means that the page is not even in the most popular pages.
由于SQL Server实例在一定时间内处于联机状态,因此将形成几乎封闭的生态系统,并且可以在其中使用一组请求最多的页面,从而避免了为了获取页面而进行的磁盘访问。 这就是为什么性能得到改善的原因。 您可能会注意到进入缓冲池的页面很高兴,而另一个寻呼机的心情却不佳……我这样表示是为了表明最好的地方是缓冲池,其中的“超级明星”页面位于。 比被“降级”到“缓冲池扩展”更糟糕的是,将其简单地逐出,这意味着该页面甚至不在最受欢迎的页面中。
With this explanation, we can get a lot of information already, but let’s take a look on three different cases:
通过这种解释,我们已经可以获得很多信息,但是让我们看一下三种不同的情况:
- How the Buffer Pool Extension is filled? 缓冲池扩展如何填充?
- How a page request works when the Buffer Pool Extension is enabled? 启用缓冲池扩展后,页面请求如何工作?
- What if we have duplicated pages in the Buffer Pool Extension and Buffer Pool? 如果我们在缓冲池扩展和缓冲池中复制了页面怎么办?
缓冲池扩展如何填充? ( How the Buffer Pool Extension is filled? )
All the story starts when the Buffer Pool decides to evict a page. This decision can be caused by different reasons.
当缓冲池决定逐出页面时,所有故事就开始了。 这个决定可能是由不同的原因引起的。
The page evicted by the Buffer Pool will be analyzed and, based on the certain thresholds, it may be selected to be a part of the Buffer Pool Extension. If it’s not interesting enough to be placed in the Buffer Pool Extension, the page will keep its way to the trash can. Once it is evicted, the only way to access the page is to go to the disk to get it.
将对由缓冲池逐出的页面进行分析,并根据某些阈值将其选择为缓冲池扩展的一部分。 如果没有足够的兴趣将其放入“缓冲池扩展”中,则该页面将继续进入垃圾箱。 一旦驱逐它,访问该页面的唯一方法是转到磁盘以获取它。
There’s a nuance here: the Buffer Pool Extension may be full. If you remember, we need to set a size to enable the feature. In this case, the Buffer Pool Extension will use a Replacement Policy to evict pages in order to open space for other, and more interesting, pages coming from the Buffer Pool.
这里有一个细微差别:缓冲池扩展可能已满。 如果您还记得,我们需要设置一个大小以启用该功能。 在这种情况下,缓冲池扩展将使用替换策略驱逐页面,以便为来自缓冲池的其他(更有趣的)页面打开空间。
页面请求如何工作? ( How a page request works? )
With the Buffer Pool Extension enabled, the page request flow slightly changes.
启用缓冲池扩展后,页面请求流将略有变化。
Let’s say that some user ran a query, and all the needed pages are already in the Buffer Pool. What happens in this case is very simple: the Buffer Pool returns the page handle and we are done.
假设某个用户运行了一个查询,并且所有需要的页面已经在缓冲池中。 在这种情况下发生的事情非常简单:缓冲池返回页面句柄,我们完成了。
Let’s now imagine that some of the requested pages is not in the Buffer Pool. Having the Buffer Pool Extension enabled, the Buffer Pool will ask the Buffer Pool Extension for this page. If the page is present at the Buffer Pool Extension, a copy of the page is made to the Buffer Pool and the page handle is returned to the requester. Notice that on that moment, the “same” page is located both in the Buffer Pool and its Extension.
现在让我们想象一些请求的页面不在缓冲池中。 启用缓冲池扩展后,缓冲池将向缓冲池扩展询问此页面。 如果页面存在于缓冲池扩展中,则将页面副本复制到缓冲池中,并将页面句柄返回给请求者。 请注意,那一刻,“相同”页面位于缓冲池及其扩展中。
To complete the flow, let’s pretend that the page is not in the Buffer Pool Extension also. Now the worst case scenario: the page should be fetched from the disk. Why it’s bad? Because disk access is very expensive, comparing to the memory and also SSD. At this point, the page is fetched from the disk and copied to the Buffer Pool, which returns the page handle.
为了完成流程,我们假设该页面也不在缓冲池扩展中。 现在,最坏的情况是:应该从磁盘中提取页面。 为什么不好? 因为磁盘访问非常昂贵,所以与内存和SSD相比。 此时,将从磁盘中获取页面并将其复制到缓冲池,该缓冲池返回页面句柄。
如果修改了重复的页面怎么办? ( What if a duplicated page is modified? )
Finally, the last case. Do you remember when a page copy, from the Buffer Pool Extension to the Buffer Pool was made? The page was on both ends, and the BPE copy is a serious candidate to be evicted. Anyway, while we have two copies of the pages in the cache, the page can be modified. What happens with the copy in the Buffer Pool Extensions? It’s invalidated and the page is evicted.
最后,最后一种情况。 您还记得从缓冲池扩展到缓冲池的页面复制吗? 该页面的两端,并且BPE副本是一个值得逐出的认真候选人。 无论如何,虽然我们在缓存中有两个页面副本,但是可以修改页面。 缓冲池扩展中的副本会如何处理? 无效,页面被逐出。
We’ve reached the end of this series, I hope it helped you to better understand the purpose and benefits of the Buffer Pool Extension. This is a great adding to the SQL Server and I’m sure that it’s going to be very useful in the future. Thank you for reading and I hope to “see you” in another article.
我们已经到了本系列的结尾,希望它能帮助您更好地了解缓冲池扩展的目的和好处。 这对SQL Server来说是一个很好的补充,我相信它在将来会非常有用。 感谢您的阅读,我希望在另一篇文章中“再见”。
翻译自: https://www.sqlshack.com/buffer-pool-extension-bpe-works/
bpe编码