記憶體最佳化資料表的查詢處理指南

記憶體內部 OLTP 在 SQL Server 中引入了記憶體最佳化資料表和原生編譯的預存程序。本文針對記憶體最佳化資料表和原生編譯的預存程序提供查詢處理的概觀。

本文件說明如何編譯和執行記憶體最佳化資料表上的查詢，包含：

SQL Server 中的磁碟基礎資料表查詢處理流程。
查詢最佳化；統計資料在記憶體最佳化資料表上的角色，以及對不正確的查詢計劃進行疑難排解的方針。
使用解譯的 Transact-SQL 以存取記憶體最佳化資料表。
有關記憶體最佳化資料表存取之查詢最佳化的考量。
原生編譯儲存程序的編譯與處理。
最佳化工具用於估計成本的統計資料。
修正不正確查詢計劃的方式。

範例查詢

下列範例將用來說明本文中所討論的查詢處理概念。

我們假設兩個資料表 Customer 和 Order。下列 Transact-SQL 指令碼包含這兩個資料表與相關聯索引的定義 (以磁碟為基礎 (傳統) 的格式)：

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY,  
  ContactName nvarchar (30) NOT NULL   
)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY,  
  CustomerID nchar (5) NOT NULL,  
  OrderDate date NOT NULL  
)  
GO  
CREATE INDEX IX_CustomerID ON dbo.[Order](CustomerID)  
GO  
CREATE INDEX IX_OrderDate ON dbo.[Order](OrderDate)  
GO

為了建構本文中所顯示的查詢計劃，這兩個資料表中已填入 Northwind 範例資料庫中的範例資料，您可以從 Northwind and pubs Sample Databases for SQL Server 2000(SQL Server 2000 的 Northwind 和 pubs 範例資料庫) 下載該資料庫。

請看下列查詢，其中聯結了 Customer 和 Order 這兩個資料表，並且會傳回訂單識別碼和相關聯的客戶資訊：

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID

預估的執行計畫如以下 SQL Server Management Studio 所顯示的那樣

聯結磁碟型資料表的查詢計劃。

關於這個查詢計劃：

Customer 資料表中的資料列是從叢集索引擷取，這是主要資料結構且擁有完整的資料表資料。
來自 Order 資料表的資料，使用 CustomerID 資料行上的非叢集索引擷取得來。這個索引中包含用於連接的 CustomerID 資料行，以及傳回給使用者的主鍵資料行 OrderID。從 Order 資料表傳回額外的資料行會需要查閱 Order 資料表的叢集索引。
邏輯運算子 Inner Join 是由實體運算子 Merge Join實作。其他實體聯結型態為 Nested Loops 和 Hash Join。運算子 Merge Join 會利用這兩個索引在聯結數據行 CustomerID 上排序的事實。

請考慮此查詢稍有變化，這會從 Order 資料表傳回所有數據列，而不只是 OrderID：

SELECT o.*, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID

這個查詢的預計方案如下：

雜湊聯結磁碟型資料表的查詢計劃。
以磁碟為基礎的資料表的雜湊聯結查詢計劃。

在這個查詢中，訂單資料表的資料列是使用叢集索引來擷取的。 Hash Match實體運算子現在用於 Inner Join。 Order 上的叢集索引不會在 CustomerID 上排序，因此 Merge Join 需要排序運算符，這會影響效能。請注意運算符的相對成本 Hash Match （75%），與上一個範例中的作員成本 Merge Join （46%）。優化器也會在上一個範例中考慮 Hash Match 運算符，但得出結論， Merge Join 運算元提供較佳的效能。

SQL Server 磁碟資料表的查詢處理

下圖概述 SQL Server 中隨選查詢的查詢處理流程：

SQL Server 查詢處理管線。

在此情節中：

使用者會發出查詢。
剖析器和代數轉譯器會根據使用者提交的 Transact-SQL 文字，利用邏輯運算子來建構查詢樹狀結構。
最佳化工具會建立包含實體運算子 (例如，巢狀迴圈聯結) 的最佳化查詢計劃。在最佳化之後，方案可能會儲存到方案快取中。如果計畫快取中已包含這個查詢的計畫，則會略過這個步驟。
查詢執行引擎會處理查詢計劃的解譯。
對於每個索引搜尋、索引掃描和資料表掃描運算子，執行引擎都會向 Access Methods 的個別索引和資料表結構要求資料列。
Access Methods 會從緩衝集區中的索引和資料頁面擷取資料列，並且視需要將頁面從磁碟載入至緩衝集區。

在第一個範例查詢中，執行引擎會從 Access Methods 查詢 Customer 上叢集索引中的資料列，以及 Order 上非叢集索引中的資料列。 Access Methods 會遍歷 B 樹索引結構，提取所要求的資料列。在這種情況下，由於計畫要求完整索引掃描，所有資料列都會被抓取。

對記憶體最佳化資料表進行解譯的 Transact-SQL 存取

Transact-SQL 隨選批次和預存程序，也稱為解譯的 Transact-SQL。在此「解譯」是指查詢計劃將由查詢執行引擎針對計劃中的每個運算子進行解譯。執行引擎會讀取運算子及其參數，並執行作業。

解譯的 Transact-SQL 可用來存取記憶體最佳化和磁碟為基礎的資料表。下圖說明對記憶體最佳化資料表進行解譯的 Transact-SQL 存取之查詢處理：

用於解譯的 tsql 的查詢處理管線。
對記憶體最佳化資料表進行解譯的 Transact-SQL 存取之查詢處理管線。

如圖中所示，查詢處理管線大致保持不變：

剖析器和 Algebrizer 會構建查詢樹。
最佳化工具會建立執行計畫。
查詢執行引擎會解譯執行計畫。

與傳統查詢處理管線（圖 2）的主要差異在於，記憶體最佳化資料表的資料列不會使用 Access Methods 從緩衝集區擷取。相反地，資料列是透過記憶體內部 OLTP 引擎從記憶體中的資料結構提取的。資料結構的差異造成最佳化工具在某些情況下挑選不同的計畫，如下面範例所示。

下列 Transact-SQL 指令碼包含 Order 和 Customer 資料表的記憶體最佳化版本 (使用雜湊索引)：

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY NONCLUSTERED,  
  ContactName nvarchar (30) NOT NULL   
) WITH (MEMORY_OPTIMIZED=ON)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY NONCLUSTERED,  
  CustomerID nchar (5) NOT NULL INDEX IX_CustomerID HASH(CustomerID) WITH (BUCKET_COUNT=100000),  
  OrderDate date NOT NULL INDEX IX_OrderDate HASH(OrderDate) WITH (BUCKET_COUNT=100000)  
) WITH (MEMORY_OPTIMIZED=ON)  
GO

請考慮在記憶體最佳化的資料表上執行相同的查詢：

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID

估計的計畫如下所示：

聯結記憶體最佳化資料表的查詢計劃。

請觀察與磁碟為基礎的資料表 (圖 1) 上相同查詢的下列差異：

此方案包含的是對 Customer 資料表的資料表掃描，而不是叢集索引掃描。
- 資料表定義不包含叢集索引。
- 記憶體最佳化資料表不支援叢集索引。不過，每個記憶體最佳化的資料表都必須至少有一個非叢集索引，而且記憶體最佳化資料表上的所有索引均可有效率地存取資料表中的所有資料行，不必將資料行儲存於索引中，或參考叢集的索引。
這個方案包含而非 Hash MatchMerge Join。 Order 和 Customer 資料表上的索引是雜湊索引，因此未進行排序。 Merge Join需要可降低效能的排序運算符。

原生編譯的預存程序

原生編譯的預存程序是編譯成機器碼的 Transact-SQL 預存程序，而不是由查詢執行引擎所解譯。下列指令碼會建立執行範例查詢的原生編譯預存程序 (從「範例查詢」區段)。

CREATE PROCEDURE usp_SampleJoin  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS BEGIN ATOMIC WITH   
(  TRANSACTION ISOLATION LEVEL = SNAPSHOT,  
  LANGUAGE = 'english')  
  
  SELECT o.OrderID, c.CustomerID, c.ContactName   
FROM dbo.[Order] o INNER JOIN dbo.[Customer] c   
  ON c.CustomerID = o.CustomerID  
  
END

原生編譯的預存程序會在建立時編譯，而解譯的預存程序則是在第一次執行時編譯 (編譯的一部分，尤其是解析和代數化，會在建立時發生。不過，對於解釋執行的預存程序，查詢計劃的最佳化是在第一次執行時才進行)。重新編譯的邏輯也很類似。如果伺服器重新啟動，原生編譯的預存程序就會在第一次執行程序時重新編譯。如果計畫已不在計畫快取中，已解譯的預存程序就會重新編譯。下表摘要說明原生編譯的預存程序及解譯的預存程序之編譯和重新編譯案例：

	本地編譯	解釋的
初始編譯	在建立的時候。	第一次執行時。
自動重新編譯	在資料庫或伺服器重新啟動之後，第一次執行程序時。	伺服器重新啟動時。或者，從計畫快取中驅逐，通常是基於綱要或統計數據的變更，或者由於記憶體壓力。
手動重新編譯	不支援。因應措施是刪除並重新建立預存程式。	請使用 `sp_recompile`。您可以手動清除快取中的計畫，例如透過 DBCC FREEPROCCACHE。您也可以建立預存程序 WITH RECOMPILE，而該預存程序將在每次執行時重新編譯。

編譯和查詢處理

下圖說明原生編譯預存程序的編譯程序：

預存程序的原生編譯。

這個程序描述為：

使用者向 SQL Server 發出 CREATE PROCEDURE 語句。
剖析器和 Algebrizer 會為程序建立處理流程，以及為預存程序中的 Transact-SQL 查詢建立樹狀結構。
最佳化工具會為預存程序中的所有查詢建立最佳化的查詢執行計畫。
記憶體中 OLTP 編譯器會採用具有內嵌最佳化查詢計劃的處理流程，並產生包含執行預存程序之機器碼的 DLL。
產生的 DLL 會載入記憶體中。

原生編譯預存程序的呼叫會被轉譯為在 DLL 中執行函數。

執行原生編譯預存程序。

原生編譯預存程序的引動過程描述如下：

用戶發出 EXECusp_myproc 語句。
剖析器會擷取名稱和預存程序參數。

如果已備妥語句，例如使用 sp_prep_exec，剖析器就不需要在運行時間擷取程式名稱和參數。
記憶體中 OLTP 執行階段會尋找預存程序的 DLL 進入點。
然後會執行 DLL 中的機器碼，再將其結果傳回用戶端。

參數探測

與在建立時編譯的原生編譯預存程序相反，解譯的 Transact-SQL 預存程序會在第一次執行時編譯。在引動過程中編譯解譯的預存程序時，最佳化工具會在產生執行計畫時使用提供給這個引動過程的參數值。在編譯期間使用參數的動作，就稱為參數探測。

參數探測不會用於編譯原生編譯的預存程序。預存程序的所有參數都會視為具有 UNKNOWN 值。如同解譯的預存程式，原生編譯的預存程式也支援 OPTIMIZE FOR 提示。如需詳細資訊，請參閱查詢提示 (Transact-SQL)。

擷取原生編譯預存程序的查詢執行計畫

原生編譯預存程序的查詢執行計畫，可以使用 Management Studio 中的 [估計的執行計畫] 或 Transact-SQL 中的 SHOWPLAN_XML 選項加以擷取。例如：

SET SHOWPLAN_XML ON  
GO  
EXEC dbo.usp_myproc  
GO  
SET SHOWPLAN_XML OFF  
GO

查詢最佳化工具生成的執行計畫由一棵包含查詢運算子的樹組成，這些運算子出現在樹的節點和葉子上。樹狀結構的結構決定運算子之間的互動 (資料列從一個運算子到另一個運算子的流程)。在 SQL Server Management Studio 的圖形檢視中，流程是由右至左。例如，圖 1 中的查詢計劃包含兩個索引掃描運算子，這兩個運算子會提供資料列給合併聯結運算子。合併聯結運算子會將資料列提供給選取運算子。最後，選取運算子會將資料列傳回用戶端。

原生編譯預存程序中的查詢運算子

下表摘要說明原生編譯預存程序內部支援的查詢運算子：

操作員	範例查詢
選擇	`SELECT OrderID FROM dbo.[Order]`
插入	`INSERT dbo.Customer VALUES ('abc', 'def')`
更新	`UPDATE dbo.Customer SET ContactName='ghi' WHERE CustomerID='abc'`
刪除	`DELETE dbo.Customer WHERE CustomerID='abc'`
計算標量	這個運算子同時用於內建函數和類型轉換。並非所有函數和類型轉換都可在原生編譯預存程序內部受到支援。 `SELECT OrderID+1 FROM dbo.[Order]`
巢狀迴圈連接	巢狀迴圈是原生編譯預存程序中唯一支援的聯結運算子。即使做為解譯 Transact-SQL 執行的相同查詢計劃包含雜湊或合併聯結，所有包含聯結的計畫還是都會使用 Nested Loops 運算子。 `SELECT o.OrderID, c.CustomerID` `FROM dbo.[Order] o INNER JOIN dbo.[Customer] c`
排序	`SELECT ContactName FROM dbo.Customer` `ORDER BY ContactName`
頁首	`SELECT TOP 10 ContactName FROM dbo.Customer`
頂部排序	`TOP`表達式（要傳回的數據列數目）不能超過8,000個數據列。如果查詢中也有聯結和彙總運算子，則數目會更少。與基礎資料表的列數相比，聯結與彙總通常會減少需要排序的列數。 `SELECT TOP 10 ContactName FROM dbo.Customer` `ORDER BY ContactName`
流聚合	請注意，Hash Match 運算子不支援彙總。因此，即使解譯的 Transact-SQL 中相同查詢的計畫使用 Hash Match 運算子，原生編譯預存程序中的所有彙總仍會使用 Stream Aggregate 運算子。 `SELECT count(CustomerID) FROM dbo.Customer`

欄位統計資料和連接

SQL Server 會在索引鍵資料行中維護值的統計資料，以協助估計特定作業的成本，例如索引掃描或索引搜尋。 (SQL Server 也會為非索引鍵資料行建立統計資料，如果您明確建立它們或查詢優化器為了回應包含述詞的查詢而建立它們)。成本估計的主要指標是單一運算子所處理的資料列數。請注意，對於磁碟基礎的資料表，特定運算子所存取的頁面數目在成本估計中佔有相當大的比例。但是，由於頁面數對於記憶體最佳化資料表而言並不重要 (一律為零)，因此這裡的討論將著重在資料列計數。估計是從計劃中的索引搜尋和掃描運算子開始，然後延伸至包括其他運算子，例如聯結運算子。聯結運算子所要處理的估計資料列數是以基礎索引、搜尋和掃描運算子的估計為依據。若要使用 Transact-SQL 解譯存取記憶體最佳化資料表，您可以觀察實際執行計畫，以了解計畫中運算子的估計資料列計數與實際資料列計數之間的差異。

在圖 1 的範例中，

Customer 上叢集索引掃描的資料列估計為 91；實際為 91。
CustomerID 上非叢集索引掃描的資料列估計為 830；實際為 830。
Merge Join 運算子的估計資料列數為 815，實際資料列數為 830。

索引掃描的估計是正確的。 SQL Server 會維護磁碟資料表的資料列計數。完整資料表和索引掃描的估計永遠正確。聯結的估計同樣相當準確。

如果這些估計改變，不同計畫替代方式的成本考量也會改變。例如，如果聯結其中一端的估計資料列計數為 1，或只有少數資料列，則使用巢狀迴圈聯結成本較低。

以下是查詢的計劃：

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID

刪除 Customer 資料表中所有資料列，只留下一個資料列後：

資料行統計資料和連接。

關於這個查詢計畫：

Hash Match 已取代為 Nested Loops 實體聯結運算子。
對 IX_CustomerID 的完整索引掃描已取代為索引搜尋。這樣的結果會是掃描 5 個資料列，而不是完整索引掃描所需的 830 個資料列。

Memory-Optimized 資料表的統計數據和基數

SQL Server 會維護記憶體最佳化資料表的資料行層級統計資料。此外，它會維護資料表的實際行數。不過，相較於磁碟數據表，記憶體優化數據表的統計數據不會自動更新。因此，在數據表發生重大變更之後，需要手動更新統計數據。如需詳細資訊，請參閱 Memory-Optimized 數據表的統計數據。

另請參閱

記憶體最佳化資料表

Last updated on 2017-06-13

共用方式為