使用 SQL Database 中的記憶體內部技術將效能最佳化Optimize performance by using In-Memory technologies in SQL Database

Azure SQL Database 中的記憶體內部技術可讓您改善應用程式的效能,還能降低您的資料庫成本。In-Memory technologies in Azure SQL Database enable you to improve performance of your application, and potentially reduce cost of your database.

使用中記憶體內部技術的時機When to use In-Memory technologies

您可以藉由使用 Azure SQL Database 中的記憶體內部技術,來達成各種工作負載的效能改善:By using In-Memory technologies in Azure SQL Database, 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 Database 擁有下列記憶體內部技術︰Azure SQL Database has 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 Database 和 SQL Server 共用相同的記憶體內部技術實作。Azure SQL Database and SQL Server share the same implementation of In-Memory technologies. 未來,這些技術的新功能會先在 Azure SQL Database 中推出,然後才在 SQL Server 中推出。Going forward, new capabilities for these technologies are released in Azure SQL Database first, before they are released in SQL Server.

記憶體內部技術的優點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.

以下是記憶體內部 OLTP 如何幫助大幅提升效能的兩個範例︰Here are two examples of how In-Memory OLTP helped to significantly improve performance:

注意

記憶體內部技術可用於進階和業務關鍵層 Azure SQL 資料庫,以及進階彈性集區。In-Memory technologies are available in Premium and Business Critical tier Azure SQL databases and Premium elastic pools.

下列影片說明 Azure SQL Database 中的記憶體內部技術所可能提升的效能。The following video explains potential performance gains with In-Memory technologies in Azure SQL Database. 請記住,能提升多少效能永遠取決於許多因素,包括工作負載和資料的性質、資料庫的存取模式等等。Remember that the performance gain that you see always depends on many factors, including the nature of the workload and data, access pattern of the database, and so on.

本文從各個面向說明 Azure SQL Database 特有的記憶體內部 OLTP 和資料行存放區索引,還包含範例:This article describes aspects of In-Memory OLTP and columnstore indexes that are specific to Azure SQL Database 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.
  • 您將看到兩個範例,其分別示範如何在 Azure SQL Database 中使用記憶體內部 OLTP 以及資料行存放區索引。You'll see two samples that illustrate the use of In-Memory OLTP, as well as columnstore indexes in Azure SQL Database.

如需詳細資訊,請參閱:For more information, 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:可讓 T-SQL 擁有更快效能的記憶體內部 OLTP 技術 (可協助您開始著手的另一篇文章)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)

技術的相關深入介紹影片︰In-depth videos about the technologies:

您可以透過程式設計的方式,來了解給定資料庫是否支援記憶體內部 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 Standard/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 Database 服務中管理記憶體,我們有使用者資料配額的概念。Because you manage memory directly in the SQL Database service, we have the concept of a quota for user data. 這個概念稱為「記憶體內部 OLAP 儲存體」 。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. 請參閱以 DTU 為基礎的資源限制 - 單一資料庫以 DTU 為基礎的資源限制 - 彈性集區以虛擬核心為基礎的資源限制 - 單一資料庫以虛擬核心為基礎的資源限制 - 彈性集區See DTU-based resource limits - single database, DTU-based resource limits - elastic pools,vCore-based resource limits - single databases and vCore-based resource limits - elastic pools.

下列項目計入記憶體內部 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:

  • 將資料庫的 Max-eDTUMaxvCore 設定為低於整個集區的 eDTU 或虛擬核心計數。Configure 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 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 the Standard or Basic tier.

在將資料庫降級至標準層或基本層時,請移除所有記憶體最佳化資料表和資料表類型,以及所有原生編譯的 T-SQL 模組。Before you downgrade the database to Standard/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 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. 叢集資料行存放區模型也可以快速擷取大量資料 (大量載入),因為超過 100K 資料列的大型批次資料會在儲存於磁碟之前先進行壓縮。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. Azure SQL Database 查詢最佳化工具會根據查詢,動態選擇資料列存放區格式或資料行存放區格式。Azure SQL Database 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 為基礎的購買模型以虛擬核心為基礎的購買模型 一文。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 big 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.

注意

受控執行個體支援所有層中的資料行存放區索引。Managed Instance supports ColumnStore indexes in all tiers.

後續步驟Next steps

其他資源Additional resources

更深入的資訊Deeper information

應用程式設計Application design

工具Tools