德鲁伊 oltp oltp_内存中OLTP –娱乐看门狗的三个关键点

德鲁伊 oltp oltp

With the introduction of the in-memory technology, we need to think about what are the new challenges to keep the service up and running. In this article I will explore one of the three key points to monitor in order to maintain your SQL Server healthy.

随着内存技术的引入，我们需要考虑使服务保持正常运行的新挑战。 在本文中，我将探讨要监视的三个关键点之一，以保持SQL Server的健康。

Last week I spoke at my local SQL Server user group, in Lisbon. The session was nice and the good feedback received encouraged me to write about this and share information that is not too spread over the internet (so far…): Monitor environments using In-Memory OLTP.

上周，我在里斯本的本地SQL Server用户组中发言。 会议非常愉快，收到的良好反馈鼓励我写这篇文章，并分享尚未在Internet上传播的信息（到目前为止……）：使用内存中OLTP监视环境。

At the first look, nothing much different from the habitual is required, but if stop and think, this is not the same of monitoring “regular” SQL Server instances. The engine behind memory-optimized tables is not the same and the behavior of SQL Server is not the same in certain situations.

乍一看，这与惯常做法没什么大不同，但是如果停下来想一想，监视“常规” SQL Server实例就不一样了。 经过内存优化的表背后的引擎并不相同，并且在某些情况下SQL Server的行为也不相同。

When I was thinking about what to add to my presentation, I found three key points that are the weaknesses of the In-memory OLTP. Based on this, I started to test some scenarios, in order to prove that those were a weak points indeed….

在考虑添加到演示文稿中时，我发现了三个关键点，它们是内存中OLTP的弱点。 基于此，我开始测试一些场景，以证明这些确实是一个弱点……。

During this article I’m going to talk about each one of those three key points, as well as show a solution to monitor.

在本文中，我将讨论这三个关键点中的每一个，并展示一种监控解决方案。

If you are curious, my selection of key points to monitor is:

如果您很好奇，我要监视的关键点是：

• The server/OS memory.
  服务器/操作系统内存。
• Checkpoint files.
  检查点文件。
• The buckets (from hash index).
  存储桶（来自哈希索引）。

I’m starting with “the server memory”. In the following articles we will talk about the other key points.

我从“服务器内存”开始。 在以下文章中，我们将讨论其他关键点。

情况1：服务器内存 ( Case #1: Server Memory )

As we know, SQL Server uses memory to operate, like any other Windows process, even when the In-memory feature is not being used, right? By the way, talking about “Windows Processes”, have you thought what might happen when you don’t have control over the Operating System? This situation is very common, there are some companies where the DBA is just responsible for the databases, and a team of system administrators are taking care of the server and Operating System.

众所周知，即使不使用内存功能，SQL Server也会像其他Windows进程一样使用内存来进行操作，对吗？ 顺便说一句，谈论“ Windows进程”时，您是否认为如果无法控制操作系统，会发生什么情况？ 这种情况很常见，在某些公司中，DBA仅负责数据库，并且由系统管理员组成的团队负责服务器和操作系统。

What is the problem with that? Actually, have a team dedicated to each technology is good! As long as the teams are communicating with each other and share the knowledge. If everyone is limited to "their own farms", the problems shall start to happen…Talking about our case, the main concern is what will be installed into the Operating System, beside SQL Server. The more applications/processes are installed, the more is the concurrency for memory allocation.

这是什么问题？ 实际上，拥有一支致力于每种技术的团队都是好的！ 只要团队之间相互交流并共享知识即可。 如果每个人都局限于“他们自己的服务器场”，那么问题将开始发生……谈论我们的案例，主要的关注点是将在SQL Server旁边的操作系统中安装什么。 安装的应用程序/进程越多，内存分配的并发性就越高。

Some companies have a default installation, including anti-virus software, firewall, and other applications and services. Another possibility is to have the SQL Server and the application running on the same OS…something very far from any best practice.

一些公司具有默认安装，包括防病毒软件，防火墙以及其他应用程序和服务。 另一种可能性是使SQL Server和应用程序在同一操作系统上运行……这与最佳实践相去甚远。

So, what is going to happen? In the following image, we have a very simplified memory allocation image. You are free to imagine the total size of total memory available 🙂

那么，将会发生什么呢？ 在下图中，我们有一个非常简化的内存分配图。 您可以随意想象可用的总内存大小🙂

Notice that I distinguished the SQL Server used memory from the In-Memory OLTP in order to better pass my point.

注意，为了更好地表达我的观点，我将SQL Server使用的内存与“内存中的OLTP”区分开来。

We can observe a large amount of free space in memory, comparing with the other memory allocations, which is good. Anyway, after a while, SQL Server is working, the data in memory-optimized tables are being modified, new rows are being inserted… And we have this new memory allocation map:

与其他内存分配相比，我们可以观察到内存中有大量可用空间，这很好。 无论如何，过一会儿，SQL Server开始工作，内存优化表中的数据被修改，新行被插入……而我们有了这个新的内存分配图：

We can see that both “memory-optimized tables” (In-memory) and “OS Processes” are now using much more memory comparing to the previous image.

我们可以看到，“内存优化表”（内存中）和“ OS进程”现在都比以前的映像使用更多的内存。

At this stage, an alarm should be triggered in order to warn about the short free memory space available, as the probability of fill out all the memory is now very high.

在此阶段，应该触发警报，以警告可用的可用空闲空间不足，因为现在填充所有内存的可能性非常高。

We could expect this growth coming from the In-Memory component, but OS Processes grew abnormally… After some time, we took a snapshot of the memory map and this is what we got:

我们可以预期这种增长来自内存中组件，但是OS进程异常增长……一段时间后，我们对内存映射进行了快照，这就是我们得到的：

Now there’s no free memory at all! Why? Again, because of an unexpected memory use from other OS Processes. It’s not easy to reach this scenario, it depends of some factors. Also the OS will always try to free up some memory, by paging out everything that is possible.

现在根本没有可用内存！ 为什么？ 同样，由于其他OS进程意外使用了内存。 达到这种情况并不容易，这取决于一些因素。 而且，操作系统将始终通过调出所有可能的内容来尝试释放一些内存。

Another common situation is start using the In-Memory OLTP and don’t limit the memory usage. This can bring an obvious problem… It’s possible to limit the memory used by memory-optimized tables, and this can be achieved using the Resource Governor.

另一个常见情况是开始使用内存中OLTP，并且不限制内存使用量。 这可能会带来一个明显的问题……可以限制内存优化表使用的内存，这可以使用资源调控器来实现。

Going back to the “full memory” scenario, what happens here? The server will start not responding. If you are connected using RDP, you feel the first shiver when you lose you session and aren’t able to reconnect 🙂

回到“全内存”场景，这里会发生什么？ 服务器将开始不响应。 如果您使用RDP进行连接，则在失去会话并且无法重新连接时会感到第一颤抖🙂

Summarizing:

总结：

• Problem: Low available memory and concurrent processes.
  问题：可用内存和并发进程不足。
• • Free Memory percentage.
    可用内存百分比。
  • The other processes (all but SQL Server ones)
    其他进程（除SQL Server之外的所有进程）
• • Operations on memory optimized tables start to fail.
    内存优化表上的操作开始失败。
  • Server stop to respond.
    服务器停止响应。

如何监控？ ( How to monitor? )

Free Memory percentage
The use of PowerShell is perfect for this! The following script will return an error if the free memory space is less than the set threshold:

可用内存百分比
为此，使用PowerShell非常完美！ 如果可用内存空间小于设置的阈值，则以下脚本将返回错误：

Memory used by other processes
As said, sometimes we plan the server with the memory we really need, but we cannot foresee the higher memory utilization of other OS processes. This could be caused by many reasons… starting from a mistake when the amount of needed memory was defined, during the project phase, to simply have tools installed to the server that you was not expecting (surprise!!!).

其他进程使用的内存
如前所述，有时我们会使用真正需要的内存来计划服务器，但是我们无法预见其他OS进程会更高的内存利用率。 这可能是由多种原因引起的……从错误开始，就是在项目阶段定义了所需的内存量之后，就简单地将不需要的工具安装到服务器上（意外！！！）。

To monitor this, I’m using PowerShell again. This script is a derivation of the first one, and it returns an error message when the OS processes (excluding the SQL Server ones) are using a memory percentage higher than the defined by the threshold.

为了监控这一点，我再次使用PowerShell。 该脚本是第一个脚本的派生文件，当OS进程（不包括SQL Server进程）使用的内存百分比高于阈值所定义的百分比时，它将返回错误消息。

In this article, we checked one of the three key points to take care for In-Memory OLTP environments “the server memory”. Showed the possible side-effects and how to monitor, in order to avoid such case. Remember that the monitoring help us to be proactive… I’m sure that you won’t like if your pager beeps when you are relaxing in a paradisiac island, right? Avoid being reactive 🙂

在本文中，我们检查了三个关键点之一，以照顾内存中OLTP环境“服务器内存”。 显示了可能的副作用以及如何监视，以免发生这种情况。 记住，监视有助于我们积极主动……我敢肯定，当您在天堂般的岛屿上放松时，如果您的寻呼机发出哔哔声，您肯定会不满意，对吗？ 避免Reactreactive

Keep following the blog, as I will publish articles talking about the other two key points very soon! See you soon!!

请继续关注该博客，因为我很快就会发表有关其他两个关键点的文章！ 再见！！

翻译自: https://www.sqlshack.com/memory-oltp-three-key-points-entertain-watchdog/

德鲁伊 oltp oltp