記憶體最佳化檔案群組The Memory Optimized Filegroup

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

若要建立記憶體最佳化資料表,您必須先建立記憶體最佳化檔案群組。To create memory-optimized tables, you must first create a memory-optimized filegroup. 記憶體最佳化檔案群組會保存一個或多個容器。The memory-optimized filegroup holds one or more containers. 每個容器都包含資料檔案及/或差異檔案。Each container contains data files or delta files or both.

即使 SCHEMA_ONLY 資料表中的資料列並未保存,而且記憶體最佳化資料表和原生編譯預存程序的中繼資料儲存在傳統目錄中,則 記憶體內部 OLTPIn-Memory OLTP 引擎仍然需要 SCHEMA_ONLY 記憶體最佳化資料表的記憶體最佳化檔案群組,以便針對具有記憶體最佳化資料表的資料庫提供一致體驗。Even though data rows from SCHEMA_ONLY tables are not persisted and the metadata for memory-optimized tables and natively compiled stored procedures is stored in the traditional catalogs, the 記憶體內部 OLTPIn-Memory OLTP engine still requires a memory-optimized filegroup for SCHEMA_ONLY memory-optimized tables to provide a uniform experience for databases with memory-optimized tables.

記憶體最佳化檔案群組是根據檔案資料流檔案群組,但有下列差異:The memory-optimized filegroup is based on filestream filegroup, with the following differences:

  • 每個資料庫只能建立一個記憶體最佳化檔案群組。You can only create one memory-optimized filegroup per database. 您必須明確地將檔案群組標示為包含 memory_optimized_data。You need to explicitly mark the filegroup as containing memory_optimized_data. 您可以在建立資料庫時建立檔案群組,或是稍後新增檔案群組:You can create the filegroup when you create the database or you can add it later:

    ALTER DATABASE imoltp ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA  
    
  • 您必須將一或多個容器新增至 MEMORY_OPTIMIZED_DATA 檔案群組。You need to add one or more containers to the MEMORY_OPTIMIZED_DATA filegroup. 例如:For example:

    ALTER DATABASE imoltp ADD FILE (name='imoltp_mod1', filename='c:\data\imoltp_mod1') TO FILEGROUP imoltp_mod  
    
  • 您不需要啟用檔案資料流 (啟用及設定 FILESTREAM) 也能建立記憶體最佳化檔案群組。You do not need to enable filestream (Enable and Configure FILESTREAM) to create a memory-optimized filegroup. 與檔案資料流的對應是由 記憶體內部 OLTPIn-Memory OLTP 引擎執行。The mapping to filestream is done by the 記憶體內部 OLTPIn-Memory OLTP engine.

  • 您可以將新的容器加入至記憶體最佳化檔案群組。You can add new containers to a memory-optimized filegroup. 您可能需要新的容器,才能擴充持久性記憶體最佳化資料表所需的儲存空間,以及在多個容器中分散 IO。You may need a new container to expand the storage needed for durable memory-optimized table and also to distribute IO across multiple containers.

  • 具有記憶體最佳化檔案群組的資料移動會在 AlwaysOn 可用性群組組態中最佳化。Data movement with a memory-optimized filegroup is optimized in an Always On Availability Group configuration. 不同於傳送到次要複本的檔案資料流檔案,記憶體最佳化檔案群組中的檢查點檔案 (資料和差異處) 不會傳送至次要複本。Unlike filestream files that are sent to secondary replicas, the checkpoint files (both data and delta) within the memory-optimized filegroup are not sent to secondary replicas. 資料和差異檔案會使用次要複本上的交易記錄來建構。The data and delta files are constructed using the transaction log on the secondary replica.

以下是適用於記憶體最佳化檔案群組的限制:The following limitations apply to a memory-optimized filegroup:

  • 一旦您使用記憶體最佳化檔案群組之後,就可以透過卸除資料庫予以移除。Once you use a memory-optimized filegroup, you can only remove it by dropping the database. 在實際執行環境中,您不太可能需要移除記憶體最佳化檔案群組。In a production environment, it is unlikely that you will need to remove the memory-optimized filegroup.

  • 您無法卸除非空白的容器或是將資料和差異檔案組移至記憶體最佳化檔案群組中的另一個容器。You cannot drop a non-empty container or move data and delta file pairs to another container in the memory-optimized filegroup.

設定記憶體最佳化檔案群組Configuring a Memory-Optimized Filegroup

考慮在記憶體最佳化檔案群組中建立多個容器,並將它們分散到不同的磁碟機,以便有更多的頻寬可讓資料流入記憶體中。Consider creating multiple containers in the memory-optimized filegroup and distribute them on different drives to achieve more bandwidth to stream the data into memory.

在多容器、多磁碟機的案例中,資料和差異檔案會以循環方式配置到容器中。In a multiple container, multiple drive scenario, data and delta files are allocated in a round-robin fashion into containers. 第一個資料檔案會從第一個容器配置,而差異檔案則是從下一個容器配置,依此方式重複這個配置模式。The first data file is allocated from the first container and the delta file is allocated from the next container and this allocation pattern repeats. 如果您有奇數磁碟機 (每個都對應到一個容器),此配置方案會在所有容器中平均分散資料檔案和差異檔案。This allocation scheme distributes data and delta files evenly across containers if you have an odd number of drives, each mapped to one container. 但是,如果您有偶數磁碟機 (每個都對應到容器),可能會造成儲存空間不平衡,導致資料檔案對應到奇數磁碟機而差異檔案則對應到偶數磁碟機。However, if you have an even number of drives, each mapped to a container, it can result in imbalanced storage with data files mapped to odd drives and delta files mapped to even drives. 若要在復原時取得平衡的 I/O 資料流,請考慮將資料檔案和差異檔案組放在相同的主軸/儲存體。To obtain a balanced stream of I/O on recovery, consider placing pairs of data and delta files on the same spindles/storage.

當設定儲存空間時,您提供的可用磁碟空間必須是持久性記憶體最佳化資料表大小的四倍。When configuring storage, you must provide free disk space that is four times the size of durable memory-optimized tables. 同時確認 I/O 子系統支援您工作負載所需的 IOPS。Also ensure that your I/O subsystem supports the required IOPS for your workload. 如果在指定的 IOPS 上填入資料和差異檔案組,您需要 IOPS 的 3 倍來處理儲存和合併作業。If data and delta file pairs are populated at a given IOPS, you need three times that IOPS to account for storing and merge operations. 您可以將一個或多個容器加入至記憶體最佳化檔案群組,以增加儲存容量和 IOPS。You can add storage capacity and IOPS by adding one or more containers to the memory-optimized filegroup.

警告

若已針對記憶體最佳化檔案群組設定 MAXSIZE 值,且檢查點檔案超過容器的大小上限,則資料庫的狀態會變成 SUSPECT。If a MAXSIZE value is set for the memory-optimized filegroup, and checkpoint files exceed the max size of the container, then the database will become SUSPECT.
在此情況下,請不要嘗試將資料庫設為 OFFLINE 和 ONLINE,這會造成資料庫狀態進入 RECOVERY_PENDING。In this case do not attempt to set the database OFFLINE and ONLINE, causing the database to stay in RECOVERY_PENDING state.

另請參閱See Also

建立及管理記憶體最佳化物件的儲存體Creating and Managing Storage for Memory-Optimized Objects
Database Files and Filegroups Database Files and Filegroups
ALTER DATABASE 檔案及檔案群組選項 (Transact-SQL)ALTER DATABASE File and Filegroup Options (Transact-SQL)