在 Azure SQL 資料庫使用記憶體內部 OLTP 改善應用程式效能
適用於:Azure SQL 資料庫
記憶體內部 OLTP 可用來改善交易處理、資料擷取和暫時性資料案例的效能,且不會提升資料庫或彈性集區的服務物件。
- 進階版 (DTU) 與業務關鍵 (vCore) 服務層級中的資料庫和彈性集區支援記憶體內部 OLTP。
- 超大規模資料庫服務層級支援記憶體內部 OLTP 物件的子集,但不包含記憶體最佳化資料表。 如需詳細資訊,請參閱超大規模資料庫限制。
請依照下列步驟,在現有的資料庫中開始使用記憶體內部 OLTP。
步驟 1:確定您使用的是進階層或業務關鍵層資料庫
如果下列查詢結果為 1
,則記憶體內部 OLTP 受支援 (非 0
):
SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsXTPSupported');
XTP 代表 極端交易處理,這是記憶體內部 OLTP 功能的非正式名稱。
步驟 2:識別要移轉至記憶體內部 OLTP 的物件
SQL Server Management Studio (SSMS) 包含交易效能分析概觀報告,讓您在具有作用中工作負載的資料庫執行該報告。 報告會識別要移轉至記憶體內部 OLTP 的候選資料表和預存程序。
若要在 SSMS 中產生報告:
- 在 [物件總管] 中,以滑鼠右鍵按一下您的資料庫節點。
- 選選取 [報告]>[標準報表]>[交易效能分析概觀]。
如需評定記憶體內部 OLTP 優點的詳細資訊,請參與判斷是否應將資料表或預存程序移植至記憶體內部 OLTP。
步驟 3:建立可比較的測試資料庫
假設報表指出,您的資料庫具有可受益於轉換成經記憶體最佳化的資料表的資料表。 建議您先進行測試,以透過測試來確認指示。
您需要生產資料庫的測試複本。 測試資料庫應與您的生產資料庫位於相同的服務層級。
若要簡化測試,請調校測試資料庫,如下所示:
使用 SQL Server Management Studio (SSMS) 連線到測試資料庫。
若要避免在查詢中使用
WITH (SNAPSHOT)
選項,請設定目前資料庫的MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT
選項,如下列 T-SQL 陳述式所示:ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
步驟 4:移轉資料表
您必須建立並填入您想要測試之資料表的記憶體最佳化複本。 您可以使用以下各項建立:
- SSMS 中的記憶體最佳化精靈。
- 使用 T-SQL 命令。
SSMS 中的記憶體最佳化精靈
若要使用此移轉選項:
使用 SSMS 連接到測試資料庫。
在 [物件總管] 中,以滑鼠右鍵按一下資料表,然後選取 [記憶體最佳化顧問]。
隨即會顯示 [資料表記憶體最佳化顧問] 精靈。
在精靈中,選取 [移轉驗證] (或 [下一步] 按鈕),查看資料表是否有經記憶體最佳化的資料表不支援的任何功能。 如需詳細資訊,請參閱
- 記憶體最佳化顧問中的記憶體最佳化檢查清單。
- 記憶體內部 OLTP 不支援 Transact-SQL 建構。
- 移轉至記憶體內部 OLTP。
如果資料表沒有不支援的功能,顧問可以為您執行實際的結構描述和資料移轉。
手動 T-SQL
若要使用此移轉選項:
- 使用 SSMS 連線到您的測試資料庫。
- 取得資料表以及其限制式和索引子的完整 T-SQL 指令碼。
- 在 SSMS 中,以滑鼠右鍵按一下您的資料表節點。
- 選取 [將資料表撰寫為]>[建立至]>[新增查詢視窗]。
- 在指令碼視窗中,將
WITH (MEMORY_OPTIMIZED = ON)
新增至CREATE TABLE
陳述式。 如需詳細資訊,請參閱記憶體最佳化資料表的語法。 - 如果有 CLUSTERED 索引,請將其變更為 NONCLUSTERED。
- 使用 sp_rename 重新命名現有的資料表。
- 執行編輯的
CREATE TABLE
指令碼,建立資料表的全新記憶體最佳化複本。 - 使用
INSERT...SELECT * INTO
將資料複製到經記憶體最佳化的資料表:INSERT INTO [<new_memory_optimized_table>] SELECT * FROM [<old_disk_based_table>];
步驟 5 (選用):移轉預存程序
記憶體內部 OLTP 也支援原生編譯的預存程序,可改善 T-SQL 效能。
原生編譯預存程序的考量
原生編譯預存程序在其 T-SQL WITH
子句必須具有下列選項:
- NATIVE_COMPILATION:這表示程序中的 Transact-SQL 陳述式都會編譯成原生程式代碼,以有效率地執行。
- SCHEMABINDING:表示在預存程序參照的資料表不能以任何會影響預存程序的方式變更定義,除非刪除預存程序。
原生編譯模組必須使用一個 ATOMIC 區塊來進行交易管理。 沒有明確 BEGIN TRANSACTION
或 ROLLBACK TRANSACTION
陳述式的用法。 您的程式碼可以使用 THROW 陳述式終止 ATOMIC 區塊,例如當程式碼偵測到違反商務規則時。
建立原生編譯預存程序的規則
建立原生編譯預存程序的 T-SQL 類似於下列範本:
CREATE PROCEDURE schemaname.procedurename
@param1 type1, ...
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH
(
TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N'<desired sys.syslanuages.sysname value>'
)
...
END;
- 針對
TRANSACTION_ISOLATION_LEVEL
,SNAPSHOT
是原生編譯預存程序最常見的值。 不過,還支援其他值的子集:REPEATABLE READ
SERIALIZABLE
LANGUAGE
值必須存在於name
資料行的sys.syslanguages
檢視中。 例如:N'us_english'
。
如何移轉預存程序以使用原生編譯
移轉步驟如下:
- 取得一般 (轉譯) 預存程序的
CREATE PROCEDURE
指令碼。 - 重寫其標頭,以符合先前的範本。
- 判定預存程序 T-SQL 程式碼是否使用原生編譯預存程序不支援的任何功能。 視需要實作因應措施。 如需詳細資訊,請參閱原生編譯預存程序的移轉問題。
- 使用 sp_rename 重新命名舊的預存程序,或將其捨棄。
- 執行您所編輯的
CREATE PROCEDURE
T-SQL 指令碼。
步驟 6:在測試中執行工作負載
在測試資料庫中執行工作負載,其類似於在生產資料庫中執行的工作負載。 這應該會顯示針對資料表和預存程序使用記憶體內部 OLTP 而實現的效能提升。
工作負載的主要屬性包括:
- 同時連線的數目。
- 讀取/寫入比率。
若要量身打造並執行測試工作負載,請考慮使用工具 RML 公用程式群組中的 ostress.exe
工具。 如需詳細資訊,請參閱 Azure SQL 資料庫中的記憶體內部範例。
若要將網路延遲降至最低,請在資料庫所在的相同 Azure 區域中執行 ostress.exe
。
步驟 7:實作後監視
請考量在生產環境中監視記憶體內部 OLTP 實作的效能影響: