内存中 OLTP 和内存优化In-Memory OLTP and Memory-Optimization

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

内存中 OLTPIn-Memory OLTP 可显著改善事务处理、数据引入和数据加载的性能以及暂时数据方案。can significantly improve the performance of transaction processing, data ingestion and data load, and transient data scenarios. 若要跳转到快速测试自己的内存优化表和本机编译的存储过程所需的基本代码和知识,请参阅To jump into the basic code and knowledge you need to quickly test your own memory-optimized table and natively compiled stored procedure, see

我们向 YouTube 上传了说明 SQL Server 上的内存中 OLTP 并演示性能优势的 17 分钟视频We have uploaded to YouTube a 17-minute video explaining In-Memory OLTP on SQL Server, and demonstrating the performance benefits.

深入了解内存中 OLTP 的详细概述以及查看显示技术中性能优势的方案:For a more detailed overview of In-Memory OLTP and a review of scenarios that see performance benefits from the technology:

请注意, 内存中 OLTPIn-Memory OLTP 是用于提高事务处理性能的 SQL ServerSQL Server 技术。Note that 内存中 OLTPIn-Memory OLTP is the SQL ServerSQL Server technology for improving performance of transaction processing. 有关提高报告和分析查询性能的 SQL ServerSQL Server 技术,请参阅 列存储索引指南For the SQL ServerSQL Server technology that improves reporting and analytical query performance see Columnstore Indexes Guide.

已对 SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x),以及 Azure SQL DatabaseAzure SQL Database 中的内存中 OLTP 进行了多项改进。Several improvements have been made to In-Memory OLTP in SQL Server 2016 (13.x)SQL Server 2016 (13.x) and SQL Server 2017 (14.x)SQL Server 2017 (14.x), as well as in Azure SQL DatabaseAzure SQL Database. 增加了 Transact-SQL 外围应用,以使其更易于迁移数据库应用程序。The Transact-SQL surface area has been increased to make it easier to migrate database applications. 添加了对内存优化表和本机编译的存储过程执行 ALTER 操作的支持,以使其更易于维护应用程序。Support for performing ALTER operations for memory-optimized tables and natively compiled stored procedures has been added, to make it easier to maintain applications.

备注

进行试用Try it out

内存中 OLTP 在高级层和业务关键层 Azure SQL 数据库和弹性池中可用。In-Memory OLTP is available in Premium and Business Critical tier Azure SQL databases and elastic pools. 若要在 Azure SQL 数据库中开始使用内存中 OLTP 以及列存储,请参阅 Optimize Performance using In-Memory Technologies in SQL Database(在 SQL 数据库中使用内存中技术优化性能)。To get started with In-Memory OLTP, as well as Columnstore in Azure SQL Database, see Optimize Performance using In-Memory Technologies in SQL Database.

在本节中In this section

本节包括下列主题:This section provides includes the following topics:

主题Topic 描述Description
快速入门 1:可提高 Transact SQL 性能的内存中 OLTP 技术Quick Start 1: In-Memory OLTP Technologies for Faster Transact-SQL Performance 深入探讨内存中 OLTPDelve right into In-Memory OLTP
概述和使用方案Overview and Usage Scenarios 有关什么是内存中 OLTP 以及什么是显示性能优势的方案的概述。Overview of what In-Memory OLTP is, and what are the scenarios that see performance benefits.
使用内存优化表的要求Requirements for Using Memory-Optimized Tables 讨论使用内存优化的表的硬件和软件要求及指导原则。Discusses hardware and software requirements and guidelines for using memory-optimized tables.
内存中 OLTP 代码示例In-Memory OLTP Code Samples 包含说明如何创建和使用内存优化的表的代码示例。Contains code samples that show how to create and use a memory-optimized table.
Memory-Optimized TablesMemory-Optimized Tables 介绍内存优化的表。Introduces memory-optimized tables.
内存优化表变量Memory-Optimized Table Variables 一个代码示例,其中展示如何使用内存优化的表变量代替传统的表变量以减少 tempdb 的使用次数。Code example showing how to use a memory-optimized table variable instead of a traditional table variable to reduce tempdb use.
内存优化表上的索引Indexes on Memory-Optimized Tables 介绍内存优化索引。Introduces memory-optimized indexes.
本机编译的存储过程Natively Compiled Stored Procedures 介绍本机编译的存储过程。Introduces natively compiled stored procedures.
管理内存中 OLTP 的内存Managing Memory for In-Memory OLTP 了解和管理系统中的内存用量。Understanding and managing memory usage on your system.
创建和管理用于内存优化对象的存储Creating and Managing Storage for Memory-Optimized Objects 论述数据和差异文件,其中存储有关内存优化的表中事务的信息。Discusses data and delta files, which store information about transactions in memory-optimized tables.
内存优化表的备份、还原和恢复Backup, Restore, and Recovery of Memory-Optimized Tables 讨论内存优化表的备份、还原和恢复。Discusses backup, restore, and recovery for memory-optimized tables.
对内存中 OLTP 的 Transact-SQL 支持Transact-SQL Support for In-Memory OLTP 讨论 Transact-SQLTransact-SQL内存中 OLTPIn-Memory OLTP的支持。Discusses Transact-SQLTransact-SQL support for 内存中 OLTPIn-Memory OLTP.
对内存中 OLTP 数据库的高可用性支持High Availability Support for In-Memory OLTP databases 讨论 内存中 OLTPIn-Memory OLTP中的可用性组和故障转移群集。Discusses availability groups and failover clustering in 内存中 OLTPIn-Memory OLTP.
对内存中 OLTP 的 SQL Server 支持SQL Server Support for In-Memory OLTP 列出支持内存优化表的新增和更新的语法和功能。Lists new and updated syntax and features supporting memory-optimized tables.
迁移到内存中 OLTPMigrating to In-Memory OLTP 论述如何将基于磁盘的表迁移到内存优化的表。Discusses how to migrate disk-based tables to memory-optimized tables.
   

此部分提供其他网站的链接,这些网站包含有关 SQL Server 上的内存中 OLTP 的信息。This section provides links to other websites that contain information about In-Memory OLTP on SQL Server.

已编制索引的 17分钟视频17 minute video, indexed

  • 视频标题: SQL Server 2016 中的内存中 OLTPVideo title: In-Memory OLTP in SQL Server 2016
  • 发布日期: 2019-03-10Published date: 2019-03-10
  • 持续时间: 17:32Duration: 17:32
  • SQL Server 高级项目经理 Jos de BruijnHosted by: Jos de Bruijn, Senior Program Manager on SQL Server

可以下载演示Demo can be downloaded

在时间标记 08:09 处,视频会运行两次演示。At the time mark 08:09, the video runs a demonstration twice. 可以通过以下链接下载视频中使用的可运行性能演示:You can download the runnable performance demo that is used in the video, from the following link:

视频中所示的常规步骤如下:The general steps seen in the video are as follows:

  1. 演示首先使用一个常规表来运行。First the demo is run with a regular table.
  2. 接下来,我们会看到如何通过在 SQL Server Management Studio (SSMS) 中单击几次来创建和填充表的内存优化版本。Next we see a memory-optimized edition of the table being created and populated by a few clicks in SQL Server Management Studio (SSMS.exe).
  3. 随后演示使用内存优化表重新运行。Then the demo is rerun with the memory-optimized table. 可测量到速度大幅提高。An enormous speed improvement is measured.

视频中每节的索引Index to each section in the video

时间标记链接Time mark link 节标题Section title
A. 00:00A.  00:00 开头。The beginning.

B. 00:56B.  00:56

客户为何应关注内存中 OLTP。Why customers should care about In-Memory OLTP.
    01:03    01:03 新式硬件需要数据库系统的新式体系结构。Modern hardware requires modern architecture of database system.
    02:10    02:10 所生成数据的分解;操作需要是即时的(低延迟)。Explosion in data being generated; operations need to be instant (low latency).
    03:19    03:19 降低 TCO — 使用拥有的资源完成更多工作。Reduce TCO - do more with the resources you have.

C. 03:33C.  03:33

什么是内存中 OLTP。What In-Memory OLTP is.
使用内存优化技术优化了性能。Performance optimized using memory-optimized technology.
    05:03    05:03 事务处理速度提高多达 30 倍。Up to 30X faster transaction processing.
    05:22    05:22 完全持久 — 数据可在发生服务器故障时保留下来。Fully durable - data survives server failures.
    06:15    06:15 完整集成在 SQL Server 中。Fully integrated in SQL Server. 因此无需学习新语言或工具。Thus no new languages or tools to learn.
    07:22    07:22 在 SQL Server 2014 中首次发布,但是在 2016 中进行了重大改进。First released in SQL Server 2014, but major improvements in 2016.
    07:58    07:58 也在 Azure SQL 数据库中可用(云中)。Available in Azure SQL Database too (in the cloud).

D. 08:09D.  08:09

性能演示。Performance demonstration.
使用一个常规表运行演示。Run the demo with a regular table.
    09:11    09:11 SSMS 上下文菜单:“报表” >“事务性能分析” SSMS context menu: Reports > Transaction Performance Analysis
    10:38    10:38 SSMS 上下文菜单:内存优化顾问SSMS context menu: Memory Optimization Advisor
    通过常规表实际创建内存优化表,以及迁移数据。    Actually create a memory-optimized table from a regular table, plus migrate the data.
    11:28    11:28 重新运行演示,速度提高 45 倍。Rerun the demo, see 45X speed improvement.

E. 12:17E.  12:17

在 SQL Server 2016 中更易于使用内存中 OLTP(与 2014 相比)。Easier to use In-Memory OLTP in SQL Server 2016 (compared to 2014).
    12:43    12:43 简化了分析以帮助进行应用迁移。Simplified analysis to help with app migration.
    13:03    13:03 通过改进的 Transact-SQL 语言支持(例如,具有外键和触发器)降低了应用迁移的复杂性。Reduced complexity of app migration through increased Transact-SQL language support (for example, with foreign keys and triggers).
    13:56    13:56 提高了可管理性。Improved manageability.
    例如,更改架构和索引、统计信息的自动更新。    For example, change schema and indexes, auto-update of statistics.

F. 14:46F.  14:46

改进了可伸缩性。Improved scalability.
    15:12    15:12 大型内存优化表(每个数据库多达 2TB)。Large memory-optimized tables (up to 2TB per database).
    15:34    15:34 更好地缩放。Even better scaling.
    16:41    16:41 使用已拥有的资源完成更多工作!Do more with the resources you already have!

G. 16:53G.  16:53

最终注释。Final comments. (17:32 结束。)(Ends at 17:32.)
   

另请参阅See also

数据库功能Database Features