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

  • 發行項

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體

本文說明在 SQL Server 中採用記憶體內部功能的方式會影響商務系統的其他層面。

注意

  • 如需 Azure SQL 資料庫 中記憶體內部數據的特定詳細資訊，請參閱在 Azure SQL 資料庫 中使用記憶體內部技術優化效能和部落格：Azure SQL 資料庫 中的記憶體內部 OLTP。
  • 如需 Azure SQL 受控執行個體 中記憶體內部數據專屬的詳細資訊，請參閱在 Azure 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. DROP (卸除) 以磁碟為基礎的資料表。

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

  8. 繼續應用程式活動。

記憶體最佳化 Advisor

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

.dacpac 檔案

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

請在類型為「資料庫」的 Visual Studio 專案內容中使用 .dacpac 檔案

A.4 記憶體內部 OLTP 功能是否適合您的應用程式的指引

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

B. 不支援的功能

某些記憶體內部 OLTP 案例不支援的功能說明如下：

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

B.1 資料庫快照集

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

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

一般而言，快照集可以方便快速測試反覆運算。

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. 將您暫時儲存在以磁碟為基礎的資料表中的資料 INSERT INTO (插入) 記憶體最佳化資料表。

  6. 卸除暫時保存數據的磁碟數據表。

D. 效能

本節描述記憶體最佳化資料表在哪些情況下能保有優異效能的完整潛力。

D.1 索引考量

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

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

注意

SQL Server 文件通常會使用「B 型樹狀結構」一詞來指稱索引。 在資料列存放區索引中，SQL Server 會實作 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 呼叫很頻繁時，或是另一個資料表中的外部索引鍵參考了記憶體最佳化的資料表時，重試邏輯更可能有幫助。 如需詳細資料，請參閱：

相關內容