共用方式為

規劃在 SQL Server 中採用記憶體內部 OLTP 功能

  • 發行項

適用於：SQL ServerAzure SQL 資料庫Azure SQL 受控執行個體

這篇文章說明 SQL 伺服器記憶體內部功能的採用方式對商務系統的其他方面有何影響。

注意

A. 採用記憶體內部 OLTP 功能

下列小節將討論您計劃採用及實作記憶體內部功能時，您必須考慮的因素。

A.1 必要條件

使用記憶體內部功能的一項必要條件，可能需要 SQL 產品的版本或服務層。 如需這項先決條件及其他，請參閱︰

A.2 預測使用中的記憶體數量

您的系統是否有足夠的使用中記憶體，可以支援新的記憶體最佳化資料表？

Microsoft SQL Server

記憶體最佳化資料表，其中包含 200 GB 的資料，需分配超過 200 GB 的使用中記憶體以支援其運作。 實作包含大量資料的記憶體最佳化資料表之前，您必須預測可能需要新增至伺服器電腦的額外使用中記憶體數量。 如需估計指引，請參閱︰

類似的指引適用於 Azure SQL 受控執行個體：

Azure SQL Database

對於裝載於 Azure SQL Database 雲端服務的資料庫，您所選的服務層會影響資料庫允許使用的使用中記憶體數量。 您應該規劃使用警示來監視資料庫的記憶體使用量。 如需詳細資料，請參閱：

記憶體最佳化資料表變數

宣告為記憶體最佳化的資料表變數有時候會比位於 tempdb 資料庫的傳統 #TempTable 更適合。 資料表變數可以提升效能，而不必使用大量的使用中記憶體。

A.3 資料表必須離線，才能轉換成記憶體最佳化

某些 ALTER TABLE 功能適用於記憶體最佳化資料表。 但您無法發出 ALTER TABLE 陳述式將以磁碟為基礎的資料表轉換成記憶體最佳化資料表。 相反地，您必須使用一組更手動化的步驟。 下面是您可以將以磁碟為基礎的資料表轉換成記憶體最佳化的各種方式。

手動撰寫指令碼

將以磁碟為基礎的資料表轉換成記憶體最佳化資料表的方法之一，是自行撰寫所需的 Transact-SQL 步驟。

  1. 暫停應用程式活動。

  2. 進行完整備份。

  3. 重新命名以磁碟為基礎的資料表。

  4. 發出 CREATE TABLE 陳述式來建立新的記憶體最佳化資料表。

  5. INSERT INTO (插入) 記憶體最佳化資料表，並同時對以磁碟為基礎的資料表使用 SELECT FROM。

  6. 刪除以磁碟為基礎的資料表。

  7. 進行另一個完整備份。

  8. 繼續應用程式活動。

記憶體最佳化顧問

「記憶體最佳化建議程式」工具可以產生指令碼，以協助實作將以磁碟為基礎的資料表轉換成記憶體最佳化資料表。 此工具會在安裝 SQL Server Data Tools (SSDT) 時一起安裝。

.dacpac 檔案

您可以使用由 SSDT 管理的 .dacpac 檔案就地更新資料庫。 在 SSDT 中，您可以指定編碼在 .dacpac 檔案中的架構變更。

您在型別為資料庫的 Visual Studio 專案中使用 .dacpac 檔案。

A.4 判斷 In-Memory OLTP 功能是否適合您應用程式的指南

如需記憶體內部 OLTP 功能是否可以改善特定應用程式效能的指引，請參閱：

B. 不支援的功能

特定記憶體內部 OLTP 案例中不支援的功能說明於：

下列小節將強調一些更重要的不支援功能。

B.1 資料庫快照集

在給定資料庫中第一次建立任何記憶體最佳化資料表或模組之後，無法再取得資料庫的任何 快照集 。 特定的原因在於︰

  • 第一個記憶體最佳化的項目，使得完全不可能從記憶體最佳化檔案群組卸除最後一個檔案；且
  • 任何在記憶體最佳化 FILEGROUP 中有檔案的資料庫都不支援快照。

通常，快照對於快速測試迭代非常方便。

B.2 跨資料庫查詢

記憶體最佳化資料表不支援 跨資料庫 的交易。 您無法在同時存取記憶體最佳化資料表的相同交易或相同查詢中存取另一個資料庫。

資料表變數並非交易式。 因此， 記憶體最佳化資料表變數 可用於跨資料庫查詢。

B.3 READPAST 資料表提示

沒有查詢可以將 READPAST 表格提示 套用到任何記憶體最佳化資料表。

READPAST 提示在某些情境下非常有用，例如當多個會話需要存取和修改同一組少量資料列時，如處理佇列時。

B.4 RowVersion、Sequence

  • 無法在記憶體最佳化資料表中將任何資料行標記為 RowVersion。

  • SEQUENCE 不能與記憶體最佳化資料表中的限制搭配使用。 例如，您無法建立使用 NEXT VALUE FOR 子句的預設條件約束。 SEQUENCE 可以與 INSERT 和 UPDATE 陳述式搭配使用。

C. 管理維護

本節描述使用記憶體最佳化資料表時的資料庫管理差異。

C.1 識別種子重設、遞增 > 1

DBCC CHECKIDENT，無法用於記憶體最佳化資料表以重設 IDENTITY 欄位。

遞增值受限於記憶體最佳化資料表的 IDENTITY 欄位中必須為 1。

C.2 DBCC CHECKDB 無法驗證記憶體最佳化資料表

DBCC CHECKDB 命令在其目標為記憶體最佳化資料表時，不做任何動作。 以下步驟是解決辦法︰

  1. 備份交易記錄。

  2. 將記憶體最佳化檔案群組中的檔案備份至空的裝置。 備份程序會叫用總和檢查碼驗證。

    如果發現腐敗，請進行下一步。

  3. 將記憶體最佳化資料表的資料複製到以磁碟為基礎的資料表，進行暫時的儲存。

  4. 還原記憶體最佳化檔案群組的檔案。

  5. 將臨時儲存在以磁碟為基礎的資料表中的資料插入至記憶體最佳化資料表。

  6. 刪除暫時保存資料的基於磁碟的資料表。

D. 效能

本節描述記憶體最佳化資料表在什麼情況下其優異效能可能無法發揮到完整潛力。

D.1 索引考量

資料表相關的陳述式 CREATE TABLE 和 ALTER TABLE 會建立和管理記憶體最佳化資料表上的所有索引。 您無法針對記憶體最佳化資料表使用 CREATE INDEX 陳述式。

當您第一次實作經記憶體最佳化的資料表時，傳統的 B 型樹狀結構、非叢集索引經常是相當直覺且簡單的選擇。 稍後，在您看到應用程式的執行方式之後，可以考慮交換另一種索引類型。

注意

文件通常會使用「B 型樹狀結構」一詞來指稱索引。 在資料列存放區索引中，資料庫引擎會實作 B+ 樹狀結構。 這不適用於列存儲索引或記憶體最佳化資料表上的索引。 如需詳細資訊，請參閱 SQL Server 和 Azure SQL 索引架構和設計指南。

有兩個特殊類型的索引需要在記憶體最佳化的資料表內容中討論：雜湊索引和資料行存放區索引。

如需記憶體最佳化資料表上的索引概觀，請參閱：

雜湊索引

要使用 ' = ' 運算子，以精確的主索引鍵值來存取一個特定資料列時，雜湊索引可能是最快速的格式。

  • 不精確的運算子，例如 ' != '、' > '，或 'BETWEEN' 會損害效能，如果搭配雜湊索引使用的話。

  • 如果索引鍵值重複率變得太高，則雜湊索引可能不是最佳的選擇。

  • 提防低估您的雜湊索引可能需要多少「存儲桶」，以避免在個別存儲桶內形成長鏈。 如需詳細資料，請參閱：

非叢集列存儲索引

記憶體最佳化資料表提供一般商務交易資料的高輸送量，這個典範我們稱為「線上交易處理」或 OLTP。 列存索引可提供高效能的彙總和類似處理，我們稱之為「分析」。 在過去，滿足 OLTP 和分析的需求最好的方法，是使用個別的資料表，並大量移動資料，且具有某種程度的資料重複。 現在，有一個更簡單的混合式解決方案︰在記憶體最佳化的資料表上建立資料行存放區索引。

  • 資料行存放區索引 可以建立在以磁碟為基礎的資料表上，甚至是作為叢集索引。 但是在記憶體最佳化資料表上，資料行存放區索引無法成為叢集索引。

  • 對於記憶體最佳化資料表，LOB 或非資料列之資料行將無法在資料表上建立資料行存放區索引。

  • 當資料表中存在資料行存放區索引時，無法在記憶體最佳化資料表上執行 ALTER TABLE 陳述式。

    • 截至 2016 年 8 月，Microsoft 有短期的計劃，要提升重新建立資料行存放庫索引的效能。

D.2 LOB 和非資料列資料行

大型物件 (LOB) 是像 varchar(max) 這類型的資料行。 在記憶體最佳化資料表上有幾個 LOB 資料行，大概不會對效能造成顯著影響。 但是盡量避免 LOB 欄位多於您的資料所需。 相同的建議也適用於行外的資料列。 如果 varchar(512) 就夠了，請勿將資料行定義為 nvarchar(3072)。

關於 LOB 和列外資料行的更多資訊可查閱於：

E. 原生程序的限制

原生編譯的 T-SQL 模組 (包括預存程序) 不支援 Transact-SQL 的特定項目。 如需支援哪些功能的詳細資訊，請參閱：

若 Transact-SQL 模組使用不支援的功能並需進行原生編譯的移轉，請參閱考量事項：

除了 Transact-SQL 特定項目上的限制，針對原生編譯的 T-SQL 模組所支援的查詢運算子也有一些限制。 由於這些限制，原生編譯的預存程序並不適用於處理大型資料集的分析查詢。

原生程序中不可使用平行處理

平行處理不能成為原生程序的任何查詢計劃的一部分。 原生程序一律為單一執行緒。

聯結類型

雜湊聯結或合併聯結都不可以是原生程序的任何查詢計劃的一部分。 使用了巢狀的迴圈聯結。

無雜湊彙總

原生程序的查詢計劃需要彙總階段時，只能使用資料流彙總。 在原生程序的查詢計劃中不支援雜湊聚合。

  • 雜湊彙總在需要彙總大量資料列時較為適合。

F. 應用程式設計：交易和重試邏輯

牽涉到記憶體最佳化資料表的交易可能會依賴另一個牽涉到相同資料表的交易。 如果相依交易計數到達允許的最大值，所有相依交易都會失敗。

在 SQL Server 2016 中：

  • 允許的最大值是八個相依交易。 八也是任何特定交易可依賴的交易數限制。
  • 錯誤號碼是 41839。 (在 SQL Server 2014 中的錯誤號碼是 41301。)

您可以讓您的 Transact-SQL 指令碼更能應付可能的交易錯誤，方法是在指令碼新增「重試邏輯」。 當 UPDATE 和 DELETE 操作很頻繁，或是記憶體最佳化的資料表被另一個資料表中的外鍵所參考時，重試邏輯更可能有幫助。 如需詳細資料，請參閱：

相關內容