德鲁伊 oltp oltp_内存中OLTP –更快变得更简单！

德鲁伊 oltp oltp

In-memory OLTP is a revolutionary tool introduced on SQL Server 2014. On SQL Server 2016 this will be even better, with a broader surface of supported tools. Check this article to know the news.

内存中的OLTP是SQL Server 2014上引入的一种革命性工具。在SQL Server 2016上，它会变得更好，并具有广泛的受支持工具。 查看本文以了解新闻。

During the Microsoft Ignite conference, held in the beginning of May, we got the nice news regarding the new SQL Server 2016. By the way, Microsoft already released the SQL Server 2016 CTP2, which is awesome for SQL Server addicted people, like me :). You can find more details here.

在5月初举行的Microsoft Ignite会议期间，我们获得了有关新SQL Server 2016的好消息。顺便说一句，Microsoft已经发布了SQL Server 2016 CTP2，对于像我这样SQL Server上瘾者来说真是棒极了： ）。 您可以在此处找到更多详细信息。

One of the features that I am eager to put my hands on, is the improved In-Memory OLTP. Still in the Microsoft Ignite, Sunil Argawal and Kevin Farlee delivered a session where the main new improvements of the In-Memory OLTP in SQL Server 2016 were announced. In this article I’m going to pass each one of those improvements, however if you prefer, here is the link for the full Ignite session.

我渴望使用的功能之一是改进的In-Memory OLTP。 仍然在Microsoft Ignite中，Sunil Argawal和Kevin Farlee举行了会议，宣布了SQL Server 2016中的内存中OLTP的主要新改进。 在本文中，我将逐一介绍这些改进，但是，如果您愿意，这里是完整的Ignite会话的链接。

SQL Server 2014上的内存中OLTP ( In-memory OLTP on SQL Server 2014 )

In the past few months I’ve been delivering sessions, and even pre-con, about In-Memory OLTP and most of the people are very excited with the final results, but in other hands, concerned because of the current limitations.

在过去的几个月中，我一直在进行有关内存中OLTP的会议，甚至进行预演，大多数人对最终结果感到非常兴奋，但另一方面，由于当前的局限性，我对此感到担忧。

The true, is that this is a revolutionary feature of SQL Server, and we are just in the seeing the beginning of a very complete engine.

的确，这是SQL Server的一项革命性功能，而我们只是在看到一个非常完整的引擎的开始。

Nowadays, it is not easy to fully-migrate existing applications to In-Memory OLTP, all because of its limitations. Just to refresh our minds, here are some of the main ones:

如今，将现有应用程序完全迁移到内存中OLTP并不容易，这都是其局限性。 只是为了刷新我们的思想，以下是一些主要思想：

• Total size for durable tables: 250 GB.
  耐用桌的总大小：250 GB。
• Removal of In-Memory FG not allowed.
  不允许删除内存中的FG。
• No table changes.
  表格没有变化。
• • Including add new indexes.
    包括添加新索引。
  • This is a problem when you set bad value for the BUCET_COUNT.
    当您为BUCET_COUNT设置错误的值时，这是一个问题。
• Row size limit is 8060 bytes.
  行大小限制为8060字节。
• • Transact-SQL Constructs Not Supported by In-Memory OLTP内存中OLTP不支持的Transact-SQL构造

* To have better understanding about most of the limitations, I recommend you to watch this good compilation, made by Brent Ozar: sp_Blitz Result: Hekaton Tables In Use.

*为了更好地了解大多数限制，我建议您观看由Brent Ozar编写的出色编译： sp_Blitz结果：Hekaton Tables Use 。

Even with the existing limitations, we can find scenarios where the In-Memory OLTP is very useful and there are big companies/applications using this in production with success!

即使存在现有限制，我们也可以找到内存中OLTP非常有用的场景，并且有大公司/应用程序在生产中成功使用它！

For database architects, the In-Memory OLTP feature came as an option to improve performance of critical environments. Bellow you have some examples of its utility:

对于数据库架构师而言，内存中OLTP功能是提高关键环境性能的一种选择。 在下面，您可以找到其实用程序的一些示例：

• • Better together with Columnstore indexes.
    与Columnstore索引一起使用效果更好。
• • E.g: Website sessions table.
    例如：网站会话表。
• TempDB alternative.
  TempDB替代。
• • For highly concurrent system.
    对于高度并发的系统。
• Performance Critical OLTP systems.
  性能至关重要的OLTP系统。

SQL Server 2016的内存中OLTP ( In-memory OLTP on SQL Server 2016 )

There are few articles about In-Memory OLTP out there, so I think that we can go ahead and see the improvements made on SQL Server 2016!

很少有关于内存中OLTP的文章，所以我认为我们可以继续进行并查看SQL Server 2016上所做的改进！

The Microsoft team did a really good job and the evolution from the previous version to this new one is notable! I’m anxious to have the SQL Server 2016 officially released in order to have this trick up my sleeve 🙂

微软团队做得非常好，从以前的版本到这个新版本的发展是值得注意的！ 我很想让SQL Server 2016正式发布以解决这个问题trick

内存中表支持的总大小（每个数据库） ( Total supported size for In-Memory Tables (per database) )

SQL Server 2014
The size of durable tables in a database could reach up to 256 GB.

SQL Server 2014
数据库中持久表的大小最多可以达到256 GB。

SQL Server 2016
From SQL Server 2016 in-memory durable tables would be able to reach the very good value of 2 TB!

SQL Server 2016
从SQL Server 2016内存中的持久性表可以达到2 TB的超值！

垃圾收集检查点文件 ( Garbage Collection of Checkpoint Files )

SQL Server 2014
The Garbage Collector process is not efficient enough, causing an excessive disk usage in some situations. We are talking about the Checkpoint files (data and delta files) that are stored in the disk in order to keep the data of durable tables. Those files are stored in a container (memory optimized Filegroup) based in FileStream, and this is the problem.

SQL Server 2014
垃圾收集器处理效率不够高，在某些情况下会导致磁盘使用过多。 我们正在谈论存储在磁盘中的Checkpoint文件（数据和增量文件），以便保留持久表的数据。 这些文件存储在基于FileStream的容器（内存优化的文件组）中，这就是问题所在。

SQL Server 2016
On SQL Server 2016 the storage management is decoupled from FileStream, allowing the unused file to be re-used/cleared immediately after they are de-referenced from transaction log.

SQL Server 2016
在SQL Server 2016上，存储管理与FileStream分离，从而允许在从事务日志中取消引用未使用的文件后立即重新使用/清除这些文件。

安全 ( Security )

SQL Server 2014
One of the In-Memory OLTP adoption blockers was the security. Not because this is unsafe, but because TDE is not supported. Because of this, the question to answer is: “For my application, is the performance most important than the security?” If not, the In-memory OLTP is not the way to go, unfortunately.

SQL Server 2014
内存中OLTP采用阻止程序之一是安全性。 不是因为这不安全，而是因为不支持TDE。 因此，要回答的问题是：“对于我的应用程序，性能比安全性最重要吗？” 否则，不幸的是，内存中OLTP并非可行之路。

SQL Server 2016
More points for the feature now, because Transparent Data Encryption (TDE) will be supported on SQL Server 2016! Now you can be fast, with security. Formula-1 Style 😉

SQL Server 2016
现在该功能有更多要点，因为SQL Server 2016将支持透明数据加密（TDE）！ 现在，有了安全性，您可以变得更快。 一级方程式赛车😉

存储扩展 ( Storage Scaling )

SQL Server 2014
The more processing power, the more log records are created, and this information should go to the checkpoint files in an efficient way.

SQL Server 2014
处理能力越强，创建的日志记录就越多，并且此信息应以有效的方式传递给检查点文件。

The flow from the transaction log to the checkpoint files is not scalable at certain point, but in the current version we can have only one offline checkpoint thread and that’s why Microsoft recommends the use of up to 2 socket and 64 cores, and no more.

从事务日志到检查点文件的流在某些时候是不可扩展的，但是在当前版本中，我们只能有一个脱机检查点线程，这就是Microsoft建议不使用最多2个套接字和64个内核的原因。

SQL Server 2016
From SQL Server 2016 this won’t be a problem anymore! The limitation was extinguished and we will be able to have one offline checkpoint thread per container.

SQL Server 2016
从SQL Server 2016开始，这将不再是问题！ 限制已消除，每个容器将可以有一个脱机检查点线程。

开发/维护挑战 ( Development/Maintainability Challenge )

SQL Server 2014
No modifications are allowed, which makes the process of tuning very difficult…
Not only this, simple tasks as respond to shifting data patterns or application modifications could be a nightmare.

SQL Server 2014
不允许修改，这使得调整过程非常困难…
不仅如此，响应不断变化的数据模式或应用程序修改的简单任务可能是一场噩梦。

SQL Server 2016
Finally the ALTER is now supported!! Now you can not only enjoy the power of an ALTER TABLE command, as well as ALTER PROC and sp_recompile!
Another concern is the option to change the HASH index bucket_count, and this will be possible through an index REBUILD.

SQL Server 2016
最后，现在支持ALTER ！！ 现在，您不仅可以享受ALTER TABLE命令的强大功能，还可以享受ALTER PROC和sp_recompile的强大功能！
另一个问题是可以更改HASH索引bucket_count的选项，并且可以通过索引REBUILD来实现。

This is a great news, but the sp_rename is won’t be supported still. Oh, and in order to alter a table you will need to have enough space to allocate two times the table size, as this is an offline operation.

这是个好消息，但仍不支持sp_rename。 哦，要更改表，您将需要有足够的空间来分配表大小的两倍，因为这是脱机操作。

平行性 ( Parallelism )

SQL Server 2014
The in-memory OLTP feature is optimized for OLTP workloads, and because of this parallel plans are not used, so the degree of parallelism is always one.

SQL Server 2014
内存中OLTP功能针对OLTP工作负载进行了优化，并且由于未使用此并行计划，因此并行度始终是一个。

This brings a problem when joining a memory optimized table (MOT) with a columnstore, as the parallelism will be killed and the great benefit of the columnstore will be lost.

将内存优化表（MOT）与列存储连接在一起时，这会带来问题，因为并行性将被杀死，并且列存储的巨大优势将丢失。

SQL Server 2016
Parallel plans are now supported! Now you can take the benefit of columnstore while joining with MOT. Real-time analytics are now possible, in the speed of light! It’s the revolution!!

SQL Server 2016
现在支持并行计划！ 现在，您可以在加入MOT的同时利用columnstore的优势。 现在就可以以光速实现实时分析 ！ 这是革命！！

更多改进 ( More improvements )

Now it is easier to convert existing applications! Before we were limited to BIN2 collation… Now the full range of collations is supported!

现在，转换现有应用程序变得更加容易！ 在我们局限于BIN2排序规则之前，现在支持所有排序规则！

And more! In-memory OLTP will support DML Triggers (AFTER triggers, natively compiled) and some other T-SQL that weren’t supported before, as following:

和更多！ 内存中的OLTP将支持DML触发器（AFTER触发器，本机编译）和以前不支持的其他一些T-SQL，如下所示：

• {LEFT|RIGHT} OUTER JOIN
  {LEFT | RIGHT}外加入
• Disjunction (OR, NOT)
  析取（OR，NOT）
• UNION [ALL]
  联盟[全部]
• SELECT DISTINCT
  选择地区
• Subqueries (EXISTS, IN, scalar)
  子查询（EXISTS，IN，标量）
• FOREIGN KEY
  外键
• CHECK
  检查
• UNIQUE constraints and indexes
  唯一约束和索引
• Nested Stored procedures (EXECUTE)
  嵌套存储过程（执行）
• Natively compiled scalar UDFs
  本机编译的标量UDF
• Indexes on NULLable columns
  可为空的列上的索引

To close this, the following “visual” tools will be available:

为此，将提供以下“可视”工具：

• Migration Assistant for Stored Procedure.
  存储过程的迁移助手。
• Best Practices Analyzer support.
  最佳实践分析器支持。
• Table Designer supporter in SSMS.
  SSMS中的表设计器支持者。

Isn’t awesome?? Yes, it is. Microsoft showed in this new version of SQL Server that In-Memory came to stay and brought to us most of the essential tools that we were expecting. The fact of integrate in-memory and columnstore indexes is a huge evolution as well.

很棒吗？ 是的。 Microsoft在此新版本SQL Server中表明，In-Memory可以保留下来，并为我们带来了我们期望的大多数基本工具。 集成内存索引和列存储索引的事实也是一个巨大的进步。

Remember, SQL Server 2016 is only in CTP2 and will be released next year, so maybe we can expect more good news!

请记住，SQL Server 2016仅在CTP2中使用，并将于明年发布，因此也许我们可以期待更多好消息！

翻译自: https://www.sqlshack.com/in-memory-oltp-the-faster-is-now-simpler/

德鲁伊 oltp oltp