記憶體最佳化 AdvisorMemory Optimization Advisor

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

交易效能分析報表 (請參閱 判斷是否應將資料表或預存程序移植到記憶體內部 OLTP) 會通知您,資料庫中哪些資料表在移植為使用記憶體內部 OLTP 時會有加分效果。Transaction Performance Analysis reports (see Determining if a Table or Stored Procedure Should Be Ported to In-Memory OLTP) informs you about which tables in your database will benefit if ported to use In-Memory OLTP. 識別您要移植為使用記憶體內部 OLTP 的資料表之後,即可使用 SQL Server Management Studio 中的記憶體最佳化建議程式,協助您將磁碟資料表遷移到記憶體最佳化資料表。After you identify a table that you would like to port to use In-Memory OLTP, you can use the memory optimization advisor in SQL Server Management Studio to help you migrate the disk-based table to a memory-optimized table.

記憶體最佳化建議程式可讓您:The memory-optimization advisor allows you to:

  • 識別記憶體最佳化資料表不支援之磁碟資料表中所使用的任何功能。Identify any features used in a disk-based table that are not supported for memory-optimized tables.

  • 將資料表和資料移轉至記憶體最佳化 (如果沒有不支援的功能)。Migrate a table and data to memory-optimized (if there are no unsupported features).

如需移轉方法的資訊,請參閱 In-Memory OLTP - 一般工作負載模式和移轉考量For information about migration methodologies, see In-Memory OLTP - Common Workload Patterns and Migration Considerations.

使用記憶體最佳化 Advisor 的逐步解說Walkthrough Using the Memory-Optimization Advisor

[物件總管] 中,以滑鼠右鍵按一下您想要轉換的資料表,然後選取 [記憶體最佳化 Advisor]In Object Explorer, right click the table you want to convert, and select Memory-Optimization Advisor. 隨即顯示 [資料表記憶體最佳化 Advisor] 的歡迎頁面。This will display the welcome page for the Table Memory Optimization Advisor.

記憶體最佳化檢查清單Memory Optimization Checklist

按一下 [資料表記憶體最佳化 Advisor] 歡迎頁面中的 [下一步] 時,即會看到記憶體最佳化檢查清單。When you click Next in the welcome page for the Table Memory Optimization Advisor, you will see the memory optimization checklist. 記憶體最佳化的資料表不支援磁碟資料表的全部功能。Memory-optimized tables do not support all the features in a disk-based table. 記憶體最佳化檢查清單會報告磁碟資料表是否使用任何與記憶體最佳化資料表不相容的功能。The memory optimization checklist reports if the disk-based table uses any features that are incompatible with a memory-optimized table. 資料表記憶體最佳化 Advisor 並不會修改磁碟資料表,以便將其移轉為使用 In-Memory OLTP。The Table Memory Optimization Advisor does not modify the disk-based table so that it can be migrated to use In-Memory OLTP. 您必須先進行變更才能繼續移轉。You must make those changes before continuing migration. 針對每個發現的不相容狀況, 資料表記憶體最佳化 Advisor 會顯示有助於修改以磁碟為基礎的資料表之相關資訊的連結。For each incompatibility found, the Table Memory Optimization Advisor displays a link to information that can help you modify your disk-based tables.

如果您想要保留這些不相容狀況的清單以便規劃移轉,請按一下 [產生報告] ,即可產生 HTML 清單。If you wish to keep a list of these incompatibilities, to plan your migration, click the Generate Report to generate a HTML list.

如果資料表沒有不相容的狀況,而且您已使用記憶體中 OLTP 連接到 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 執行個體,請按 [下一步]If your table has no incompatibilities and you are connected to a SQL Server 2014 (12.x)SQL Server 2014 (12.x) instance with In-Memory OLTP, click Next.

記憶體最佳化警告Memory Optimization Warnings

在下一頁的記憶體最佳化警告中包含一份問題清單,這些問題對資料表移轉為使用記憶體中 OLTP 並無影響,但卻可能導致其他物件 (例如預存程序或 CLR 函數) 行為失敗或產生非預期的行為。The next page, memory optimization warnings, contains a list of issues that do not prevent the table from being migrated to use In-Memory OLTP, but that may cause the behavior of other objects (such as stored procedures or CLR functions) to fail or result in unexpected behavior.

清單一開始出現的幾個警告都是參考用的,因此可能不適用於您的資料表。The first several warnings in the list are informational and may or may not apply to your table. 資料表右邊資料行中的連結會帶領您前往詳細資訊。Links in the right-hand column of the table will take you to more information.

這個警告表也會顯示您的資料表中尚未出現的潛在警告狀況。The warning table will also display potential warning conditions that are not present in your table.

可付諸行動的警告會在左邊資料行中出現一個黃色的三角形。Actionable warnings will have a yellow triangle in the left-hand column. 如果有可付諸行動的警告,您應該結束移轉、解決警告,然後重新啟動程序。If there are actionable warnings, you should exit the migration, resolve the warnings, and then restart the process. 如果您沒有解決警告,移轉的資料表可能會導致失敗。If you do not resolve the warnings, your migrated table may cause a failure.

按一下 [產生報告] 即可產生這些警告的 HTML 報告。Click Generate Report to generate an HTML report of these warnings. [下一步] 繼續進行。Click Next to proceed.

檢閱最佳化選項Review Optimization Options

下一個畫面可讓您修改選項以便移轉至 In-Memory OLTP:The next screen lets you modify options for the migration to In-Memory OLTP:

記憶體最佳化的檔案群組Memory-optimized filegroup
您的記憶體最佳化檔案群組名稱。The name for your memory-optimized filegroup. 資料庫必須具備一個記憶體最佳化的檔案群組 (其中至少包含一個檔案),才能建立記憶體最佳化的資料表。A database must have a memory-optimized filegroup with at least one file before a memory-optimized table can be created.

如果您沒有記憶體最佳化的檔案群組,即可變更預設名稱。If you do not have a memory-optimized filegroup, you can change the default name. 不可刪除記憶體最佳化的檔案群組。Memory-optimized filegroups cannot be deleted. 記憶體最佳化檔案群組的存在可能會停用某些資料庫層級功能 (例如 AUTO CLOSE 與資料庫鏡像)。The existence of a memory-optimized filegroup may disable some database-level features such as AUTO CLOSE and database mirroring.

如果資料庫中已經有記憶體最佳化的檔案群組,這個欄位就會預先填入它的名稱,而且您無法變更此欄位的值。If a database already has a memory-optimized file group, this field will be pre-populated with its name and you will not be able to change the value of this field.

邏輯檔案名稱和檔案路徑Logical file name and File path
將會包含記憶體最佳化資料表的檔案名稱。The name of the file that will contain the memory-optimized table. 資料庫必須具備一個記憶體最佳化的檔案群組 (其中至少包含一個檔案),才能建立記憶體最佳化的資料表。A database must have a memory-optimized file group with at least one file before a memory-optimized table can be created.

如果您不具備現有的記憶體最佳化檔案群組,則可在移轉程序結束時,變更要建立之檔案的預設名稱和路徑。If you do not have an existing memory-optimized file group, you can change the default name and path of the file to be created at the end of the migration process.

如果您具備現有的記憶體最佳化檔案群組,這些欄位會預先填入,而且您無法變更這些欄位的值。If you have an existing memory-optimized filegroup, these fields will be pre-populated and you will not be able to change the values.

將原始資料表重新命名為Rename the original table as
在移轉程序結束時,會使用資料表目前的名稱建立新的記憶體最佳化資料表。At the end of the migration process, a new memory-optimized table will be created with the current name of the table. 若要避免名稱衝突,您必須重新命名目前的資料表。To avoid a name conflict, the current table must be renamed. 您可以在此欄位中變更名稱。You may change that name in this field.

目前估計的記憶體成本 (MB)Estimated current memory cost (MB)
記憶體最佳化 Advisor 會根據磁碟資料表的中繼資料,評估新的記憶體最佳化資料表將取用的記憶體數量。The Memory-Optimization Advisor estimates the amount of memory the new memory-optimized table will consume based on metadata of the disk-based table. 記憶體最佳化資料表中的資料表和資料列大小中說明資料表大小的計算方式。The calculation of the table size is explained in Table and Row Size in Memory-Optimized Tables.

如果未分配足夠的記憶體,移轉程序可能會失敗。If sufficient memory is not allotted, the migration process may fail.

請將資料表的資料複製到新的記憶體最佳化資料表Also copy table data to the new memory optimized table
如果您想要將目前資料表的資料移至新的記憶體最佳化資料表,請選取此選項。Select this option if you wish to also move the data in the current table to the new memory-optimized table. 如果未選取此選項,建立新的記憶體最佳化資料表時不會有任何資料列。If this option is not selected, the new memory-optimized table will be created with no rows.

根據預設將資料表移轉為持久性資料表The table will be migrated as a durable table by default
相較於持久性記憶體最佳化資料表,記憶體中 OLTP 可以優越效能支援非持久性資料表。In-Memory OLTP supports non-durable tables with superior performance compared to durable memory-optimized tables. 不過,在重新啟動伺服器時,非持久性資料表中的資料將會遺失。However, data in a non-durable table will be lost upon server restart.

如果選取此選項,記憶體最佳化 Advisor 將會建立非持久性的資料表 (而不是持久性資料表)。If this option is selected, the Memory-Optimization Advisor will create a non-durable table instead of a durable table.

警告

只有在您了解非持久性資料表的相關資料遺失風險時,才可選取此選項。Select this option only if you understand the risk of data loss associated with non-durable tables.

[下一步] ,繼續進行。Click Next to continue.

檢閱主索引鍵轉換Review Primary Key Conversion

下一個畫面是 [檢閱主索引鍵轉換]The next screen is Review Primary Key Conversion. 記憶體最佳化 Advisor 會偵測資料表中是否有一個或多個主索引鍵,並根據主索引鍵的中繼資料填入資料行的清單。The Memory-Optimization Advisor will detect if there are one or more primary keys in the table, and populates the list of columns based on the primary key metadata. 否則,如果您想要移轉至持久性記憶體最佳化資料表,就必須建立主索引鍵。Otherwise, if you wish to migrate to a durable memory-optimized table, you must create a primary key.

如果主索引鍵不存在,而且資料表正移轉至非持久性資料表,這個畫面將不會出現。If a primary key doesn't exist and the table is being migrated to a non-durable table, this screen will not appear.

對於文字資料行 ( charncharvarcharnvarchar類型的資料行),您必須選取適當的定序。For textual columns (columns with types char, nchar, varchar, and nvarchar) you must select an appropriate collation. 記憶體中 OLTP 只支援記憶體最佳化資料表上的資料行之 BIN2 定序,而不支援附帶補充字元的定序。In-Memory OLTP only supports BIN2 collations for columns on a memory-optimized table and it does not support collations with supplementary characters. 如需支援的定序及定序變更之潛在影響的詳細資訊,請參閱< Collations and Code Pages >。See Collations and Code Pages for information on the collations supported and the potential impact of a change in collation.

您可以為主索引鍵設定下列參數:You can configure the following parameters for the primary key:

為主要索引鍵選取新的名稱Select a new name for this primary key
此資料表的主索引鍵名稱在資料庫內必須是唯一的。The primary key name for this table must be unique inside the database. 您可在此處變更主索引鍵的名稱。You may change the name of the primary key here.

選取主索引鍵的類型Select the type of this primary key
記憶體中 OLTP 支援下列兩種記憶體最佳化資料表上的索引類型:In-Memory OLTP supports two types of indexes on a memory-optimized table:

  • 非叢集雜湊索引。A NONCLUSTERED HASH index. 此索引最適合具有許多點查閱的索引。This index is best for indexes with many point lookups. 您可以在 [值區計數] 欄位中設定此索引的值區計數。You may configure the bucket count for this index in the Bucket Count field.

  • 非叢集索引。A NONCLUSTERED index. 此類型的索引最適合具有許多範圍查詢的索引。This type of index is best for indexes with many range queries. 您可以在 [排序資料行和次序] 清單中設定每個資料行的排序次序。You may configure the sort order for each column in the Sort column and order list.

若要了解主索引鍵最適合的索引類型,請參閱 雜湊索引To understand the type of index best for your primary key, see Hash Indexes.

選定主索引鍵之後,請按 [下一步]Click Next after you make your primary key choices.

檢閱索引轉換Review Index Conversion

下一頁是 [檢閱索引轉換]The next page is Review Index Conversion. 記憶體最佳化 Advisor 會偵測資料表中是否有一個或多個索引,並填入資料行與資料類型的清單。The Memory-Optimization Advisor will detect if there are one or more indexes in the table, and populates the list of columns and data type. 您可在 [檢閱索引轉換] 頁面中設定的參數,與上一個 [檢閱主索引鍵轉換] 頁面類似。The parameters you can configure in the Review Index Conversion page are similar to the previous, Review Primary Key Conversion page.

如果資料表中僅有主索引鍵,而且資料表正移轉至持久性資料表,這個畫面就不會出現。If the table only has a primary key and it's being migrated to a durable table, this screen will not appear.

決定資料表中的每個索引之後,請按 [下一步]After you make a decision for every index in your table, click Next.

確認移轉動作Verify Migration Actions

下一頁是 [確認移轉動作]The next page is Verify Migration Actions. 若要編寫移轉作業的指令碼,請按一下 [指令碼] 產生 Transact-SQLTransact-SQL 指令碼。To script the migration operation, click Script to generate a Transact-SQLTransact-SQL script. 然後您可以修改和執行指令碼。You may then modify and execute the script. 按一下 [移轉] 即可開始資料表移轉。Click Migrate to begin the table migration.

程序完成之後,請重新整理 [物件總管] 以查看新的記憶體最佳化資料表和舊的磁碟資料表。After the process is finished, refresh Object Explorer to see the new memory-optimized table and the old disk-based table. 您可保留舊資料表或隨時將其刪除。You can keep the old table or delete it at your convenience.

另請參閱See Also

移轉至 In-Memory OLTPMigrating to In-Memory OLTP