通过使用 Azure SQL 数据库和 Azure SQL 托管实例的内存技术来优化性能Optimize performance by using in-memory technologies in Azure SQL Database and Azure SQL Managed Instance

适用于:是 Azure SQL 数据库 是Azure SQL 托管实例 APPLIES TO: yesAzure SQL Database yesAzure SQL Managed Instance

使用内存中技术可以提高应用程序的性能,并有可能降低数据库的成本。In-memory technologies enable you to improve performance of your application, and potentially reduce cost of your database.

何时使用内存中技术When to use in-memory technologies

使用内存中技术,可以对各种工作负载实现性能改进:By using in-memory technologies, you can achieve performance improvements with various workloads:

  • 事务(联机事务处理 (OLTP)),其中的大多数请求将读取或更新少量的数据(例如 CRUD 操作)。Transactional (online transactional processing (OLTP)) where most of the requests read or update smaller set of data (for example, CRUD operations).
  • 分析(联机分析处理 (OLAP)),其中的大多数查询出于报告目的而执行复杂的计算,特定数量的查询会将数据加载和追加到现有的表中(称为“批量加载”)或者从表中删除数据。Analytic (online analytical processing (OLAP)) where most of the queries have complex calculations for the reporting purposes, with a certain number of queries that load and append data to the existing tables (so called bulk-load), or delete the data from the tables.
  • 混合(混合事务/分析处理 (HTAP)),其中针对相同的数据集同时执行 OLTP 和 OLAP 查询。Mixed (hybrid transaction/analytical processing (HTAP)) where both OLTP and OLAP queries are executed on the same set of data.

内存中技术使用查询的本机编译,或者底层硬件上提供的批处理和 SIMD 指令等高级处理来保留应在内存中处理的数据,可以提高这些工作负荷的性能。In-memory technologies can improve performance of these workloads by keeping the data that should be processed into the memory, using native compilation of the queries, or advanced processing such as batch processing and SIMD instructions that are available on the underlying hardware.

概述Overview

Azure SQL 数据库和 Azure SQL 托管实例具有以下内存中技术:Azure SQL Database and Azure SQL Managed Instance have the following in-memory technologies:

  • 内存中 OLTP 可以增加每秒处理的事务数,并降低事务处理的延迟。In-Memory OLTP increases number of transactions per second and reduces latency for transaction processing. 可受益于内存中 OLTP 的情况包括:高吞吐量事务处理(例如贸易和游戏)、从事件或 IoT 设备引入数据、缓存、数据加载以及临时表和表变量等情况。Scenarios that benefit from In-Memory OLTP are: high-throughput transaction processing such as trading and gaming, data ingestion from events or IoT devices, caching, data load, and temporary table and table variable scenarios.
  • 聚集列存储索引,可减少存储占用(高达 10 倍)并提高报告和分析查询的性能。Clustered columnstore indexes reduce your storage footprint (up to 10 times) and improve performance for reporting and analytics queries. 将其与数据集市中的事实数据表结合使用,可在数据库中容纳更多数据并提升性能。You can use it with fact tables in your data marts to fit more data in your database and improve performance. 此外,将其与操作数据库中的历史数据结合使用,可存档并查询高达 10 倍的额外数据。Also, you can use it with historical data in your operational database to archive and be able to query up to 10 times more data.
  • 用于 HTAP 的非聚集列存储索引通过直接查询操作数据库来帮助获取对业务的实时见解,无需运行开销不菲的提取、转换和加载 (ETL) 过程,也无需等待填充数据仓库。Nonclustered columnstore indexes for HTAP help you to gain real-time insights into your business through querying the operational database directly, without the need to run an expensive extract, transform, and load (ETL) process and wait for the data warehouse to be populated. 使用非聚集列存储索引可以快速对 OLTP 数据库执行分析查询,同时减少对操作工作负荷的影响。Nonclustered columnstore indexes allow fast execution of analytics queries on the OLTP database, while reducing the impact on the operational workload.
  • 使用适用于 HTAP 的内存优化聚集列存储索引可以针对相同的数据快速执行事务处理和并发运行分析查询。 Memory-optimized clustered columnstore indexes for HTAP enables you to perform fast transaction processing, and to concurrently run analytics queries very quickly on the same data.

列存储索引和内存中 OLTP 分别在 2012 年和 2014 年加入 SQL Server 产品。Both columnstore indexes and In-Memory OLTP have been part of the SQL Server product since 2012 and 2014, respectively. Azure SQL 数据库、Azure SQL 托管实例和 SQL Server 共享内存中技术的相同实现。Azure SQL Database, Azure SQL Managed Instance, and SQL Server share the same implementation of in-memory technologies.

内存中技术的优势Benefits of in-memory technology

由于查询和事务处理的效率提升,内存中技术还可降低成本。Because of the more efficient query and transaction processing, in-memory technologies also help you to reduce cost. 用户通常不需要升级数据库的定价层即可实现性能提升。You typically don't need to upgrade the pricing tier of the database to achieve performance gains. 在某些情况下,即使是降低定价层,使用内存中技术也能提升性能。In some cases, you might even be able reduce the pricing tier, while still seeing performance improvements with in-memory technologies.

备注

“高级”和“业务关键”层级也提供内存中技术。In-memory technologies are available in the Premium and Business Critical tiers.

本文全面介绍特定于 Azure SQL 数据库和 Azure SQL 托管实例的内存中 OLTP 和列存储索引,并提供相关示例:This article describes aspects of In-Memory OLTP and columnstore indexes that are specific to Azure SQL Database and Azure SQL Managed Instance, and also includes samples:

  • 介绍这些技术对存储和数据大小限制的影响。You'll see the impact of these technologies on storage and data size limits.
  • 介绍如何控制使用这些技术的数据库在不同定价层之间的移动。You'll see how to manage the movement of databases that use these technologies between the different pricing tiers.
  • 介绍两个示例,演示如何使用内存中 OLTP 和列存储索引。You'll see two samples that illustrate the use of In-Memory OLTP, as well as columnstore indexes.

有关 SQL Server 的内存中 OLTP 的详细信息,请参阅:For more information about in-memory in SQL Server, see:

内存中 OLTPIn-Memory OLTP

内存中 OLTP 技术将所有数据保留在内存中,可以提供极快的数据访问操作。In-Memory OLTP technology provides extremely fast data access operations by keeping all data in memory. 它还使用专用索引、查询本机编译和无闩锁数据访问来提高 OLTP 工作负荷的性能。It also uses specialized indexes, native compilation of queries, and latch-free data-access to improve performance of the OLTP workload. 可通过两种方式来组织内存中 OLTP 数据:There are two ways to organize your In-Memory OLTP data:

  • 内存优化的行存储格式:每个行是一个独立的内存对象。Memory-optimized rowstore format where every row is a separate memory object. 这是针对高性能 OLTP 工作负荷优化的经典内存中 OLTP 格式。This is a classic In-Memory OLTP format optimized for high-performance OLTP workloads. 在内存优化的行存储格式中可以使用两种类型的内存优化表:There are two types of memory-optimized tables that can be used in the memory-optimized rowstore format:

    • 持久性表 (SCHEMA_AND_DATA):服务器重启后会保留内存中的行。Durable tables (SCHEMA_AND_DATA) where the rows placed in memory are preserved after server restart. 此类表的行为类似于传统的行存储表,同时具有内存中优化的附加优势。This type of tables behaves like a traditional rowstore table with the additional benefits of in-memory optimizations.
    • 非持久性表 (SCHEMA_ONLY):重启后不保留行。Non-durable tables (SCHEMA_ONLY) where the rows are not-preserved after restart. 此类表适用于临时数据(例如,取代临时表),或者需要快速加载其中的数据,然后将数据移到某个持久性表(称为临时表)的表。This type of table is designed for temporary data (for example, replacement of temp tables), or tables where you need to quickly load data before you move it to some persisted table (so called staging tables).
  • 内存优化的列存储格式:其中的数据以纵栏表的格式进行组织。Memory-optimized columnstore format where data is organized in a columnar format. 此结构适用于 HTAP 方案,其中,需要针对运行 OLTP 工作负荷的同一数据结构运行分析查询。This structure is designed for HTAP scenarios where you need to run analytic queries on the same data structure where your OLTP workload is running.

备注

内存中 OLTP 技术适用于完全驻留在内存中的数据结构。In-Memory OLTP technology is designed for the data structures that can fully reside in memory. 由于无法将内存中数据卸载到磁盘,因此请确保使用具有足够内存的数据库。Since the In-memory data cannot be offloaded to disk, make sure that you are using database that has enough memory. 有关更多详细信息,请参阅内存中 OLTP 的数据大小和存储上限See Data size and storage cap for In-Memory OLTP for more details.

有关内存中 OLTP 的快速入门:快速入门 1:通过内存中 OLTP 技术加速 T-SQL 性能(另一篇文章也可以帮助你入门)A quick primer on In-Memory OLTP: Quickstart 1: In-Memory OLTP Technologies for Faster T-SQL Performance (another article to help you get started)

可通过编程方式了解给定的数据库是否支持内存中 OLTP。There is a programmatic way to understand whether a given database supports In-Memory OLTP. 可执行以下 Transact-SQL 查询:You can execute the following Transact-SQL query:

SELECT DatabasePropertyEx(DB_NAME(), 'IsXTPSupported');

如果查询返回 1,则此数据库支持内存中 OLTP。If the query returns 1, In-Memory OLTP is supported in this database. 以下查询确定了将数据库降级为“常规用途”/“标准”/“基本版本”层级前需要删除的所有对象:The following queries identify all objects that need to be removed before a database can be downgraded to General Purpose, Standard, or Basic:

SELECT * FROM sys.tables WHERE is_memory_optimized=1
SELECT * FROM sys.table_types WHERE is_memory_optimized=1
SELECT * FROM sys.sql_modules WHERE uses_native_compilation=1

内存中 OLTP 的数据大小和存储上限Data size and storage cap for In-Memory OLTP

内存中 OLTP 包括用于存储用户数据的内存优化表。In-Memory OLTP includes memory-optimized tables, which are used for storing user data. 这些表必需在内存可容纳的范围内。These tables are required to fit in memory. 由于内存是直接在 SQL 数据库中管理的,因此我们提出了用户数据配额的概念。Because you manage memory directly in SQL Database, we have the concept of a quota for user data. 这种概念称为“内存中 OLTP 存储”。This idea is referred to as In-Memory OLTP storage.

每个受支持的单一数据库定价层和每个弹性池定价层都包括一定量的内存中 OLTP 存储。Each supported single database pricing tier and each elastic pool pricing tier includes a certain amount of In-Memory OLTP storage.

以下各项计入内存中 OLTP 存储上限:The following items count toward your In-Memory OLTP storage cap:

  • 内存优化表中的活动用户数据行和表变量。Active user data rows in memory-optimized tables and table variables. 请注意,旧行版本不计入上限。Note that old row versions don't count toward the cap.
  • 内存优化表中的索引。Indexes on memory-optimized tables.
  • ALTER TABLE 操作的运营开销。Operational overhead of ALTER TABLE operations.

如果达到上限,将会出现超出配额错误,且无法再插入或更新数据。If you hit the cap, you receive an out-of-quota error, and you are no longer able to insert or update data. 若要解决此错误,可删除数据或提升数据库或池的定价层。To mitigate this error, delete data or increase the pricing tier of the database or pool.

有关监视内存中 OLTP 存储利用率及配置即将达到上限时的警报的详细信息,请参阅监视内存中存储For details about monitoring In-Memory OLTP storage utilization and configuring alerts when you almost hit the cap, see Monitor in-memory storage.

关于弹性池About elastic pools

使用弹性池时,池中的所有数据库共享内存中 OLTP 存储。With elastic pools, the In-Memory OLTP storage is shared across all databases in the pool. 因此一个数据库中的使用量可能对其他数据库造成影响。Therefore, the usage in one database can potentially affect other databases. 对此,有两个缓解方法:Two mitigations for this are:

  • 为低于池的 eDTU 或 vCore 计数的数据库整体配置 Max-eDTUMaxvCoreConfigure a Max-eDTU or MaxvCore for databases that is lower than the eDTU or vCore count for the pool as a whole. 此最大值将池中任意数据库中的内存中 OLTP 存储利用率限制为与 eDTU 计数对应的大小。This maximum caps the In-Memory OLTP storage utilization, in any database in the pool, to the size that corresponds to the eDTU count.
  • 配置大于 0 的 Min-eDTUMinvCoreConfigure a Min-eDTU or MinvCore that is greater than 0. 此最小值可保证池中的每个数据库都有与配置的 Min-eDTUvCore 对应的可用内存中 OLTP 存储量。This minimum guarantees that each database in the pool has the amount of available In-Memory OLTP storage that corresponds to the configured Min-eDTU or vCore.

更改使用内存中 OLTP 技术的数据库的服务层级Changing service tiers of databases that use In-Memory OLTP technologies

始终可以将数据库或实例升级到更高的层,例如,从“常规用途”层升级到“业务关键”层(或者从“标准”层升级到“高级”层)。You can always upgrade your database or instance to a higher tier, such as from General Purpose to Business Critical (or Standard to Premium). 可用的功能和资源只会增加。The available functionality and resources only increase.

但是,将层降级可能会对数据库造成负面影响。But downgrading the tier can negatively impact your database. 如果数据库包含内存中 OLTP 对象,则从“业务关键”层降级到“常规用途”层(或者从“高级”层降级到“标准”或“基本”层)时,影响就尤为明显。The impact is especially apparent when you downgrade from Business Critical to General Purpose (or Premium to Standard or Basic) when your database contains In-Memory OLTP objects. 降级后,内存优化表不可用(即使它们保持可见)。Memory-optimized tables are unavailable after the downgrade (even if they remain visible). 降低弹性池的定价层或将使用内存中技术的数据库移动到“常规用途”、“标准”或“基本”弹性池中时,也应考虑这些问题。The same considerations apply when you're lowering the pricing tier of an elastic pool, or moving a database with in-memory technologies, into a General Purpose, Standard, or Basic elastic pool.

重要

“常规用途”、“标准”或“基本”层不支持内存中 OLTP。In-Memory OLTP isn't supported in the General Purpose, Standard or Basic tier. 因此,不能将包含任何内存中 OLTP 对象的数据库移动到这些层之一。Therefore, it isn't possible to move a database that has any In-Memory OLTP objects to one of these tiers.

将数据库降级为“常规用途”/“标准”/“基本”层级之前,请删除所有内存优化表和表类型,以及所有本机编译的 T-SQL 模块。Before you downgrade the database to General Purpose, Standard, or Basic, remove all memory-optimized tables and table types, as well as all natively compiled T-SQL modules.

在“业务关键”层中缩减资源:内存优化表中的数据必须能够装入与数据库层或托管实例关联的或者在弹性池中可用的内存中 OLTP 存储中。Scaling-down resources in Business Critical tier: Data in memory-optimized tables must fit within the In-Memory OLTP storage that is associated with the tier of the database or the managed instance, or it is available in the elastic pool. 如果尝试缩减层或将数据库移到可用内存中 OLTP 存储不足的池,操作将会失败。If you try to scale-down the tier or move the database into a pool that doesn't have enough available In-Memory OLTP storage, the operation fails.

内存中列存储In-memory columnstore

使用内存中列存储技术可在表中存储和查询大量数据。In-memory columnstore technology is enabling you to store and query a large amount of data in the tables. 列存储技术使用基于列的数据存储格式和批查询处理,与传统的行导向型存储相比,可将 OLAP 工作负荷中的查询性能提升 10 倍。Columnstore technology uses column-based data storage format and batch query processing to achieve gain up to 10 times the query performance in OLAP workloads over traditional row-oriented storage. 此外,与处理非压缩数据相比,处理压缩数据可将性能提升 10 倍。You can also achieve gains up to 10 times the data compression over the uncompressed data size. 可以使用两种类型的列存储模型来组织数据:There are two types of columnstore models that you can use to organize your data:

  • 聚集列存储:表中的所有数据以纵栏表的格式进行组织。Clustered columnstore where all data in the table is organized in the columnar format. 在此模型中,表中的所有行以纵栏表的格式进行定位,该格式可以高度压缩数据,并可让你快速执行分析查询,以及针对表生成报告。In this model, all rows in the table are placed in columnar format that highly compresses the data and enables you to execute fast analytical queries and reports on the table. 根据数据的性质,数据大小可以减少 10 到 100 倍。Depending on the nature of your data, the size of your data might be decreased 10x-100x. 使用聚集列存储模型还可以快速引入大量数据(批量加载),因为超过 10 万行的大型数据批在存储到磁盘之前会经过压缩。Clustered columnstore model also enables fast ingestion of large amount of data (bulk-load) since large batches of data greater than 100K rows are compressed before they are stored on disk. 此模型非常适合经典数据仓库方案。This model is a good choice for the classic data warehouse scenarios.
  • 非聚集列存储:其中的数据存储在传统的行存储表中,有一个采用列存储格式的索引用于执行分析查询。Non-clustered columnstore where the data is stored in traditional rowstore table and there is an index in the columnstore format that is used for the analytical queries. 使用此模型可以实现混合事务分析处理 (HTAP):可以针对事务工作负荷运行高性能的实时分析。This model enables Hybrid Transactional-Analytic Processing (HTAP): the ability to run performant real-time analytics on a transactional workload. 针对优化的行存储表执行 OLTP 查询以访问少量的行,同时,针对更适合用于扫描和分析的列存储索引执行 OLAP 查询。OLTP queries are executed on rowstore table that is optimized for accessing a small set of rows, while OLAP queries are executed on columnstore index that is better choice for scans and analytics. 查询优化器根据查询动态选择行存储或列存储格式。The query optimizer dynamically chooses rowstore or columnstore format based on the query. 非聚集列存储索引无法减少数据大小,因为原始数据集按原样保留在原始行存储表中。Non-clustered columnstore indexes don't decrease the size of the data since original data-set is kept in the original rowstore table without any change. 但是,其他列存储索引的大小应该比同等的 B 树索引要小几个量级。However, the size of additional columnstore index should be in order of magnitude smaller than the equivalent B-tree index.

备注

内存中列存储技术仅在内存中保留处理时所需的数据,不能装入内存的数据将存储在磁盘上。In-memory columnstore technology keeps only the data that is needed for processing in the memory, while the data that cannot fit into the memory is stored on-disk. 因此,内存中列存储结构中的数据量可能会超出可用的内存量。Therefore, the amount of data in in-memory columnstore structures can exceed the amount of available memory.

有关该技术的深入介绍视频:In-depth video about the technology:

列存储索引的数据大小和存储Data size and storage for columnstore indexes

列存储索引不需要在内存可容纳的范围内。Columnstore indexes aren't required to fit in memory. 因此,索引大小的唯一上限是最大整体数据库大小,此大小在基于 DTU 的购买模型基于 vCore 的购买模型两篇文章中有述。Therefore, the only cap on the size of the indexes is the maximum overall database size, which is documented in the DTU-based purchasing model and vCore-based purchasing model articles.

使用聚集列存储索引时,对基础表存储使用列式压缩。When you use clustered columnstore indexes, columnar compression is used for the base table storage. 这种压缩可显著减少用户数据的存储占用,意味着数据库中可容纳更多数据。This compression can significantly reduce the storage footprint of your user data, which means that you can fit more data in the database. 使用纵栏表存档压缩可进一步提高压缩率。And the compression can be further increased with columnar archival compression. 可实现的压缩量取决于数据的性质,但 10 倍压缩并不少见。The amount of compression that you can achieve depends on the nature of the data, but 10 times the compression is not uncommon.

例如,如果数据库的最大大小为 1 TB,则使用列存储索引实现 10 倍压缩时,该数据库中可容纳总共 10 TB 的用户数据。For example, if you have a database with a maximum size of 1 terabyte (TB) and you achieve 10 times the compression by using columnstore indexes, you can fit a total of 10 TB of user data in the database.

使用非聚集列存储索引时,仍以传统行存储格式存储基表。When you use nonclustered columnstore indexes, the base table is still stored in the traditional rowstore format. 因此,节省的存储空间小于使用聚集列存储索引节省的空间。Therefore, the storage savings aren't as significant as with clustered columnstore indexes. 但是,如果使用单个列存储索引取代众多传统非聚集索引,则仍可整体减少表的存储占用。However, if you're replacing a number of traditional nonclustered indexes with a single columnstore index, you can still see an overall savings in the storage footprint for the table.

更改包含列存储索引的数据库的服务层级Changing service tiers of databases containing Columnstore indexes

如果目标层低于 S3,可能无法将单一数据库降级到“基本”或“标准”层。Downgrading single database to Basic or Standard might not be possible if your target tier is below S3. 只有“业务关键”/“高级”定价层、“标准”层、“S3”及更高的层支持列存储索引,“基本”层则不支持。Columnstore indexes are supported only on the Business Critical/Premium pricing tier and on the Standard tier, S3 and above, and not on the Basic tier. 将数据库降级到不受支持的层或级别时后,列存储索引不可用。When you downgrade your database to an unsupported tier or level, your columnstore index becomes unavailable. 系统会保留列存储索引,但永远不会利用索引。The system maintains your columnstore index, but it never leverages the index. 如果后来又升级回到受支持的层或级别,列存储索引立即可供再次利用。If you later upgrade back to a supported tier or level, your columnstore index is immediately ready to be leveraged again.

如果有聚集列存储索引,则降级后,整个表不可用。If you have a clustered columnstore index, the whole table becomes unavailable after the downgrade. 因此,我们建议在将数据库降级到不受支持的层或级别前,先删除所有聚集列存储索引。Therefore we recommend that you drop all clustered columnstore indexes before you downgrade your database to an unsupported tier or level.

备注

SQL 托管实例支持所有层中的列存储索引。SQL Managed Instance supports Columnstore indexes in all tiers.

后续步骤Next steps

其他资源Additional resources

深入信息Deeper information

应用程序设计Application design

工具Tools