SQL Server 記憶體中OLTP內部機制概述（四）

標籤：des   style   blog   http   io   color   ar   os   使用   

----------------------------我是分割線-------------------------------

本文翻譯自微軟白皮書《SQL Server In-Memory OLTP Internals Overview》：http://technet.microsoft.com/en-us/library/dn720242.aspx

譯者水平有限，如有翻譯不當之處，歡迎指正。

----------------------------我是分割線-------------------------------

 表和預存程序的原生編譯

記憶體中OLTP將原生編譯的概念引入到了SQL Server 2014中。SQL Server可以在原生編譯訪問記憶體最佳化表的預存程序，而且實際上，也本地編譯了記憶體最佳化表本身。原生編譯比起傳統的解釋型Transact-SQL可以提供更快的資料訪問和更高效的查詢執行。

什麼是原生編譯？

本地編譯是指將變編程結構轉換為本地代碼，包括可以由CPU直接執行的處理器指令，而不需要進一步的編譯或解釋的過程。

Transact-SQL語言由高層次的結構所組成，例如CREATE TABLE和SELECT... FROM。記憶體中OLTP編譯器分析這些結構，並將其編譯成本地代碼，以進行資料訪問和查詢執行的快速已耗用時間。SQL Server 2014中的記憶體中OLTP編譯器將表和預存程序的定義做為輸入。它產生C語言代碼，並利用Visual C編譯器來產生本地代碼。

表和預存程序編譯的結果為DLL檔案，這些檔案被載入到記憶體中並連結到SQL Server進程上。

SQL Server在建立記憶體最佳化表和本地編譯的預存程序時，將它們編譯成本地DLL檔案。另外，資料庫或伺服器在重新啟動後，會重新編譯表和預存程序的DLL檔案。重建DLL檔案所需的資訊儲存在資料庫的中繼資料中；DLL檔案本身並不是資料庫的一部分。因此DLL檔案並不是Database Backup的一部分。

DLL檔案的維護

記憶體最佳化表和本地編譯預存程序的DLL檔案，以及其他為了故障診斷和可支援性而儲存的產生的檔案，都儲存在檔案系統中。

以下查詢顯示了當前伺服器上載入到記憶體中的所有表和預存程序的DLL檔案：

SELECT name, description FROM sys.dm_os_loaded_modulesWHERE description = ‘XTP Native DLL‘

資料庫管理員不需要維護由原生編譯產生的檔案。例如在表和預存程序的刪除過程中，以及在刪除的資料庫中， SQL Server會自動刪除不再需要的組建檔案，而且伺服器或資料庫重新啟動時也會自動刪除。

表的本地編譯

使用CREATE TABLE語句建立記憶體最佳化表，表資訊寫入到資料庫中的中繼資料中，表和索引的結構在記憶體中建立，並且表還被編譯成一個DLL檔案。

請參考以下的樣本指令碼，這個指令碼建立了一個資料庫和一個記憶體最佳化表：

USE master GO create database db1 GO ALTER DATABASE db1 ADD FILEGROUP db1_mod CONTAINS memory_optimized_data GO -- adapt filename as needed ALTER DATABASE db1 ADD FILE (name=‘db1_mod‘, filename=‘c:\data\db1_mod‘)     TO FILEGROUP db1_mod GO USE db1 GO CREATE TABLE dbo.t1 (c1 int not null primary key nonclustered, c2 int) WITH (MEMORY_OPTIMIZED=ON) GO -- retrieve the path of the DLL for table t1 SELECT name, description FROM sys.dm_os_loaded_modules WHERE name LIKE ‘%xtp_t_‘ + cast(db_id() AS varchar(10))                + ‘_‘ + cast(object_id(‘dbo.t1‘) AS varchar(10)) + ‘.dll‘ GO

表的建立會編譯表的DLL檔案，並將這個DLL檔案載入到記憶體中。緊接著CREATE TABLE語句之後的查詢檢索出表的DLL檔案的路徑。

表t1的DLL檔案能夠解析表的索引結構和行格式。 SQL Server使用這個DLL檔案遍曆索引和檢索行，以及行中的內容。

預存程序的原生編譯

標有NATIVE_COMPILATION選項的預存程序是原生編譯的。這意味著，為了效能要求較高的商務邏輯的執行效率，在預存程序中的Transact-SQL語句都被編譯為本地代碼。

請參考以下的樣本預存程序，這個預存程序將行插入到之前樣本的表t1中：

CREATE PROCEDURE dbo.p1WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNERASBEGIN ATOMICWITH (TRANSACTION ISOLATION LEVEL=snapshot, LANGUAGE=N‘us_english‘)    DECLARE @i int = 1000000    WHILE @i > 0    BEGIN    INSERT dbo.t1 VALUES (@i, @i+1)    SET @i -= 1    ENDENDGOEXEC dbo.p1GO-- resetDELETE FROM dbo.t1GO

預存程序p1的DLL檔案可以直接與表t1的DLL檔案以及記憶體中OLTP的儲存引擎進行互動，以便儘可能快的插入行。

記憶體中OLTP編譯器充分利用了查詢最佳化工具為預存程序中的每個查詢建立高效的執行計畫。需要注意的是，對於原生編譯的預存程序，查詢執行計畫被編譯成DLL檔案。由於SQL Server 2014不支援原生編譯的預存程序的自動重新編譯，對錶資料的修改可能需要刪除並重新建立一些預存程序，將新的查詢計劃合并到預存程序的DLL檔案中。需要注意的是，在伺服器重新啟動以及容錯移轉到一個AlwaysOn副本後，原生編譯的預存程序在第一次執行時進行重新編譯，這意味著查詢最佳化工具將建立新的查詢計劃，新的查詢計劃隨後會被編譯到預存程序的DLL檔案中。

編譯和查詢處理

圖15顯示了原生編譯的預存程序的編譯過程：

圖15 預存程序的本地編譯

 

1. 使用者向 SQL Server 發出一條 CREATE PROCEDURE 語句

2. 分析器和 algebrizer 為該預存程序建立處理流程，並為預存程序中的 Transact-SQL 查詢建立查詢樹

3. 最佳化器為預存程序中的所有查詢建立最佳化的查詢執行計畫

4. 記憶體內部 OLTP 編譯器通過嵌入的最佳化查詢計劃接管處理流程，並產生一個 DLL，其中包含執行預存程序的機器代碼

5. 產生的 DLL 載入到記憶體中，並串連到SQL Server進程

原生編譯的預存程序的調用轉換為對 DLL 中函數的調用，16所示

圖16 本地編譯的預存程序的執行

 

1. 使用者發出一條’ EXEC myproc’語句

2. 分析器提取名稱和預存程序參數

3. 記憶體內部 OLTP 運行時尋找預存程序DLL檔案的 進入點

4. DLL檔案執行預存程序邏輯，結果會返回到用戶端

參數嗅探

解釋型 Transact-SQL 預存程序在首次執行（調用）時編譯成直接的物理執行計畫，而原生編譯的預存程序在建立時編譯。當在調用時對解釋型預存程序進行編譯，最佳化器使用為此調用提供的參數值產生執行計畫。這種編譯期間的參數用法稱為“參數嗅探”。

參數嗅探不適用於編譯原生編譯的預存程序。此類預存程序的所有參數都視為具有 UNKNOWN 值。

SQL Server的功能支援

許多SQL Server功能都支援記憶體中OLTP和含記憶體最佳化表的資料庫，但不是所有SQL Server功能都支援。比如，AlwaysOn組件，記錄傳送和資料庫的備份和恢複都完全支援記憶體中OLTP。事務複製部分支援，允許記憶體最佳化表作為一個訂閱使用，但不能作為發布項目。但是，不支援資料庫鏡像。可以使用SQL Server Management Studio來管理記憶體最佳化表，也支援SSIS。

有關支援和不支援功能的完整列表，請參閱SQL Server記憶體中OLTP的文檔。

管理經驗

記憶體中OLTP已完全整合到SQL Server的管理經驗中。正如上面提到的，SQL Server Management Studio中能夠管理記憶體最佳化表，檔案組和本地編譯的預存程序。還可以使用SQL Server管理對象（SMO）和PowerShell來管理您的記憶體最佳化對象。

中繼資料

一些已有的中繼資料對象已經得到增強，可提供關於記憶體最佳化表和預存程序的資訊，新的對象也已經增加。

有一個函數得到了增強：

• OBJECTPROPERTY - 現在包括一個屬性TableIsMemoryOptimized

目錄檢視

以下的系統檢視表得到增強：

• sys.tables 有三個新列：
  • durability（0或1）
  • durability_desc（SCHEMA_AND_DATA和SCHEMA_ONLY）
  • is_memory_optimized（0或1）
• sys.table_types  現在有一個is_memory_optimized列
• sys.indexes  現在有一個可能為7的type值和一個對應為NONCLUSTERED HASH的type_desc值。 （像一個非聚集的B-tree索引一樣，非叢集索引有一個為2的 type_value和為NONCLUSTERED的type_desc）。
• sys.index_columns列is_descending_key現在有不同的語義，對於雜湊索引，這個值是無意義的，可以忽略。
• sys.data_spaces  現在有一個可能為FX的type值和一個對應為MEMORY_OPTIMIZED_DATA_FILEGROUP的type_desc值
• sys.sql_modules和sys.all_sql_modules - 現在包含一個uses_native_compilation列

此外，還有一些專門提供記憶體最佳化表資訊的幾個新的中繼資料對象。

增加了一個新的目錄檢視來支援雜湊索引：sys.hash_indexes。這個視圖是基於sys.indexes，因此具有和sys.indexes相同的列，並有一個額外添加的列。這個bucket_count列顯示了為索引所指定的雜湊桶的數量，而且如果沒有刪除和重建索引的話，這個值不會被改變。

動態管理對象

以下SQL Server動態管理檢視是為記憶體中OLTP所新增的。 （xtp標識符代表“極限交易處理（eXtreme transaction processing）”。）sys.dm_db_xtp_*開頭的那些動態管理檢視提供了關於啟用記憶體中OLTP的各個資料庫的資訊， sys.dm_xtp_*開頭的那些動態管理檢視提供了執行個體層級的資訊。您可以在文檔中讀到關於這些對象的詳細資料。這些動態管理檢視中的一些在本文較早的相關章節已經提到過。

關於支援記憶體最佳化表的動態管理檢視的詳細資料，請參閱記憶體最佳化表的動態管理檢視。

• sys.dm_db_xtp_checkpoint
• sys.dm_db_xtp_checkpoint_files
• sys.dm_db_xtp_gc_cycles_stats
• sys.dm_xtp_gc_stats
• sys.dm_xtp_system_memory_consumers
• sys.dm_xtp_threads
• sys.dm_xtp_transaction_stats
• sys.dm_db_xtp_index_stats
• sys.dm_db_xtp_memory_consumers
• sys.dm_db_xtp_object_stats
• sys.dm_db_xtp_transactions
• sys.dm_db_xtp_table_memory_stats

XEvents

記憶體中OLTP引擎提供了xEvents來協助監控和故障排除。您可以運行以下查詢來查看當前可用的xEvents：

SELECT p.name, o.name, o.descriptionFROM sys.dm_xe_objects o JOIN sys.dm_xe_packages pON o.package_guid=p.guidWHERE p.name = ‘XtpEngine‘;GO

 

效能計數器

記憶體中OLTP引擎提供了效能計數器來協助監控和故障排除。您可以運行以下查詢來查看當前可用的效能計數器：

SELECT OBJECT_NAME，counter_namefrom sys.dm_os_performance_countersWHERE OBJECT_NAME LIKE‘XTP%‘;GO

還提供了名為XTP使用記憶體的資料庫計數器對象，用於跟蹤在資料庫層級的記憶體最佳化表的記憶體使用量情況。

記憶體使用量情況報表

要獲得記憶體最佳化表和索引當前使用記憶體的即時報表，您可以運行SQL Server Management Studio中提供的報表。在物件總管中，按右鍵包含記憶體最佳化表的資料庫的名稱，選擇報表|標準報表|記憶體最佳化對象的記憶體使用量情況。您會看到類似圖17的報表。

圖17記憶體最佳化對象的記憶體使用量情況報表

 

這個報表顯示表的行和索引所使用的空間，以及少量由系統使用的空間。請記住，一旦建立了雜湊索引，這些索引就會擁有分配給已聲明的雜湊桶數量的記憶體，因此這個報表將顯示在插入任何行之前，這些索引的記憶體使用量情況。對於非叢集索引，在添加行之前不會分配記憶體，並且記憶體需求將依賴於索引鍵的大小和行的數量。

記憶體需求

當運行記憶體中OLTP時，SQL Server需要配置足夠的記憶體來儲存所有的記憶體最佳化表。未能分配足夠的記憶體會導致在需要額外記憶體的操作運行時事務失敗。通常這會在INSERT或UPDATE操作時發生，但在一個記憶體最佳化的非叢集索引上的刪除操作也有可能發生。正如上一節所介紹的，刪除可能導致產生頁合并，而且由於索引頁永遠不會被更新，合併作業將會分配新頁。記憶體中OLTP的記憶體管理器與SQL Server的記憶體管理器完全整合，並在可能的情況下，通過更積極地清理舊行版本來對記憶體壓力進行反應。

在預測記憶體最佳化表所需的記憶體數量時，一個經驗法是，應該擁有資料佔用量兩倍的記憶體。除此之外，總記憶體需求還取決於工作負載；如果由於OLTP操作有大量的資料修改，則需要更多的記憶體供給行版本使用。如果大量讀取現有的資料，則可能需要更少的記憶體。

對於規劃索引所需的空間，雜湊索引則非常簡單。每個桶需要8個位元組，所以您可以計算桶的數量乘以8位元組數。記憶體最佳化非叢集索引的大小取決於索引鍵的大小和表中行的數量。可以假設每個索引行是8個位元組加上索引鍵的大小（假設為k位元組），因此，適合於一個頁面的最大行數將是8176/（K +8）。預計的行數除以這個結果將得到一個初步的估計。請記住，並不是所有的索引頁都是8K，也不是所有的頁都是全滿的。由於頁需要拆分和合并，建立出新的頁，需要為這些頁留出空間，直到垃圾收集進程將它們刪除。

用資源管理員管理記憶體

SQL Server的資源管理員是一種讓您可以主動管理記憶體的工具。與CTP2版本開始，資料庫可以綁定到一個資源集區上，您可以分配一定量的記憶體到這個池中。這個資料庫中的記憶體最佳化表使用的記憶體不能超過這個量。有一個可分配記憶體的80%的固定限制，以確保系統在記憶體壓力下仍然穩定。事實上，記憶體最佳化表及其索引佔用的任何記憶體都是由資源管理員管理，除此之外，並沒有其他類型的記憶體是由資源管理員管理。如果資料庫沒有顯式映射到一個資源集區，它會隱式地映射到預設池。

更多有關SQL Server資源管理員的詳細資料，請參閱SQL Server 2008引入資源管理員時編寫的擴充白皮書：http://view.officeapps.live.com/op/view.aspx?src=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2FD%2FB%2FD%2FDBDE7972-1EB9-470A-BA18-58849DB3EB3B%2FResourceGov.docx

SQL Server2012中資源管理員的更改，可以在這裡讀到：http://msdn.microsoft.com/en-us/library/jj573256.aspx

為記憶體中OLTP資料庫建立一個記憶體池的第一步是指定MAX_MEMORY_PERCENT值。這個值指定了可以被分配給與這個池關聯資料庫中記憶體最佳化表的SQL Server記憶體的百分比。

例如：

CREATE RESOURCE POOL HkPool WITH (MAX_MEMORY_PERCENT=50);ALTER RESOURCE GOVERNOR RECONFIGURE;

建立了資源集區之後，需要使用預存程序sp_xtp_bind_db_resource_pool將希望管理的資料庫綁定到各自的池中。注意，一個池可以包含多個資料庫，但資料庫在任何時間點只能與一個池關聯。

以下是一個樣本：

EXEC sp_xtp_bind_db_resource_pool ‘HkDB‘, ‘HkPool‘;

因為是在分配記憶體時，才將記憶體指派到一個資源集區中，只是將資源集區與一個資料庫關聯並不能轉移已指派記憶體的指派。要做到這一點，您需要將資料庫離線，再把它重新聯機。當資料被讀入到記憶體最佳化表，記憶體將關聯到新的資源集區中。

例如：

ALTER DATABASE [HkDb] SET OFFLINE;ALTER DATABASE [HkDb] SET ONLINE;

如果您想要移除一個資料庫和一個資源集區之間的綁定，則可以使用預存程序sp_xtp_unbind_db_resource_pool。例如，您可能希望將資料庫移動到另一個資源集區中，或者徹底刪除資源集區，來將其替換成其他的資源集區。

EXEC sp_xtp_unbind_db_resource_pool ‘HkPool‘;

 

通過分析，遷移和報表工具集（AMR）來協助遷移

安裝SQL Server2014（只要您已經選擇安裝了完整的管理工具集）之後，分析，遷移和報表工具集（Analyze, Migrate and Report , AMR）可用於提供關於可能要考慮將哪些表和預存程序遷移到記憶體中OLTP的建議。

該工具集的分析和報表方面使用一組新的資料收集器與管理資料倉儲相結合，以擷取工作負載瓶頸和效能指標。這些資料可以用來產生報表，可通過右擊管理資料倉儲的資料庫並選擇報表|管理資料倉儲來使用。然後有一個選項可以選擇“事務效能分析概述”。

其中一個報表包含了如果轉換成記憶體最佳化表，哪些表可能提供最大效能增益的建議。該報表也將基於表中同時使用了多少不支援的功能，說明進行轉換將需要多少代價。另一份報表包含為了記憶體最佳化表的使用，哪些預存程序可能會從轉換為原生編譯的預存程序中受益的建議。

一旦關鍵表確定，該工具集的遷移方麵包含的記憶體最佳化顧問可以協助您遷移它們。在右擊基於磁碟的表彈出的操作功能表中，點擊“記憶體最佳化顧問”。這個顧問將確定對象中已有的不相容性，並產生概述了這些不相容問題的報表，並且還能對有限的一組表進行有指導的遷移。

記憶體最佳化表可以由解釋型的Transact-SQL和本地編譯預存程序進行訪問，並且記憶體最佳化表可以與基於磁碟的表在同一個查詢中使用，這就意味著遷移到一個記憶體中OLTP的環境可以逐步迭代地來完成。根據管理資料倉儲報表中提供的建議，您可以開始將錶轉換成記憶體最佳化表，一次一個，可以從記憶體中最佳化結構中受益最多的表開始。當您開始看到轉換成記憶體最佳化表的好處，您可以繼續轉換越來越多的表，但訪問它們使用常規的Transact-SQL介面，如果有的話，應用程式只需要極少的更改。

一旦您的表已經完成轉換，您就可以開始計劃將代碼重寫成本地編譯的預存程序，可以再次從資料倉儲報表表明將提供最大收益的那些預存程序開始。利用原生編譯建議程式（另一種工具，可以通過右擊Management Studio中的預存程序找到）來確定Transact-SQL中的不相容性，可以協助遷移這些對象。

 

總結

SQL Server的記憶體中OLTP特性提供了建立和使用記憶體中最佳化表的能力，並可以極其有效對其進行管理，為 OLTP工作負載提供了效能最佳化。記憶體最佳化表可採用真正的多版本開放式並行存取控制進行訪問，在處理過程中不需要鎖或閂鎖。所有記憶體中OLTP的記憶體最佳化表必須至少有一個索引，並且所有的訪問都需要通過索引。記憶體中OLTP的記憶體最佳化表可以與基於磁碟的表在同一個事務中被引用，只是稍有些限制。原生編譯的預存程序是訪問記憶體最佳化表和高效商務邏輯演算法最快的方式。

 

---------------------------全文完-------------------------------

SQL Server 記憶體中OLTP內部機制概述（一）

SQL Server 記憶體中OLTP內部機制概述（二）

SQL Server 記憶體中OLTP內部機制概述（三）

SQL Server 記憶體中OLTP內部機制概述（四）

 

SQL Server 記憶體中OLTP內部機制概述（四）