記憶體中OLTP是關于記憶體中的一切。但那隻是對了一半。在今天的文章裡我想給你展示下,當你從記憶體讀取資料時,即使記憶體中OLTP也會引起磁盤活動。這裡的問題是執行計劃裡,不正确的統計資訊與排序(sort)運算符的組合。
排序(sort)運算符問題
我們都知道,排序(sort)運算符需要所謂的記憶體授予(Memory Grant)來作它的運作。記憶體區域是用來進行執行計劃裡到來記錄的排序。記憶體授予的大小是基于估計行數數量。在基數計算(Cadinality Estimation)期間查詢優化器估計執行計劃裡每個運算符的預計行數。
我在今年6月寫了篇文章,展示了當估計錯誤時,排序(sort)運算符如何能溢出到TempDb。在記憶體中OLTP裡同樣的事情會發生:當估計行數錯誤時,在執行計劃裡有排序(sort)運算符,排序(sort)運算符會溢出到TempDb!我們來重制這個情形。
記憶體中OLTP溢出到TempDb
我們建立一個有記憶體中OLTP檔案組配置的新資料庫。
1 -- Create new database
2 CREATE DATABASE HashCollisions
3 GO
4
5 -- Add MEMORY_OPTIMIZED_DATA filegroup to the database.
6 ALTER DATABASE HashCollisions
7 ADD FILEGROUP InMemoryOLTPFileGroup CONTAINS MEMORY_OPTIMIZED_DATA
8 GO
9
10 USE HashCollisions
11 GO
12
13 -- Add a new file to the previously created file group
14 ALTER DATABASE HashCollisions ADD FILE
15 (
16 NAME = N'InMemoryOLTPContainer',
17 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\InMemoryOLTPContainer'
18 )
19 TO FILEGROUP [InMemoryOLTPFileGroup]
20 GO
下一步我建立新的記憶體優化表:
1 -- Create a test table
2 CREATE TABLE Table1
3 (
4 Column1 INT IDENTITY,
5 Column2 INT
6 CONSTRAINT pk_Column1 PRIMARY KEY NONCLUSTERED HASH (Column1) WITH (BUCKET_COUNT = 1)
7 ) WITH
8 (
9 MEMORY_OPTIMIZED = ON,
10 DURABILITY = SCHEMA_ONLY
11 )
12 GO
從表定義可以看到,我在Column1列建立了1個哈希索引。因為它是個哈希索引,你也需要指定哈希表上你想擁有的哈希桶數。這裡我指定了1個哈希桶,這是個非常,非常糟的做法。當你往表裡插入記錄時,因為隻有1個哈希桶,你會得到巨大數量的哈希沖突(hash collisions)。一般來說,在你定義你的哈希索引的列上,哈希桶數應該和你列上的唯一值個數一緻。下面代碼往剛才建立的表裡插入14001條記錄。
1 -- Insert 14001 records
2 INSERT INTO Table1(Column2) VALUES (1)
3
4 SELECT TOP 14000 IDENTITY(INT, 1, 1) AS n INTO #Nums
5 FROM
6 master.dbo.syscolumns sc1
7
8 INSERT INTO Table1 (Column2)
9 SELECT 2 FROM #nums
10 DROP TABLE #nums
11 GO
你可以通過DMV sys.dm_db_xtp_hash_index_stats檢視哈希沖突數。從這個DMV的輸出可以看到,你有14001條記錄在這個且唯一的哈希索引的哈希桶上。現在我們來運作執行計劃裡有排序(sort)運算符的SELECT語句。
1 -- The sort operator in this execution plan spills over to TempDb!
2 SELECT * FROM Table1
3 ORDER BY Column1
現在當你檢視執行計劃時,你會看到排序運算符已經溢出到TempDb。
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIyZuBnL4cDNzkDNwQDN2ITOwcDMvwFOwUTMwIzLchDNzATN38CX1EDMyc2bsJ2Lc12bj5ycn9Gbi52YuAzcldWYtl2Lc9CX6MHc0RHaiojIsJye.png)
這是因為哈希索引上不正确的統計資訊才發生的。當你檢視執行計劃裡Index Scan (NonClusteredHash) 運算符屬性時,你會看到查詢優化器從我們的哈希索引上估計行數為1,實際我們傳回了140001行。
Index Scan (NonClusteredHash) 運算符的估計總是基于哈希表裡哈希桶數。查詢優化器這裡做出的假設是你沒有哈希沖突(hash collisions)——這在這裡是不正确的。是以對排序運算符的記憶體授予是根據那個不正确的估計作為标準,這就會溢出到TempDb。在我的系統裡這個查詢運作了近80毫秒,對于記憶體中技術來說這個算很長時間了。
你如何修正這個問題?删除你的表,在哈希索引裡仔細計劃哈希桶數。歡迎來到記憶體中OLTP的精彩世界……
小結
當你使用記憶體中OLTP的哈希索引時,你要對你的哈希所用仔細設計你的哈希桶數。當它們錯誤時,是傷及性能。我已經在1個月前,寫了篇文章描述哈希沖突(hash collisions)如何傷及記憶體中OLTP的性能——即使沒有溢出到TempDb!
從中我們可以看出:在哈希索引有哈希沖突的話,你用記憶體中OLTP的話不能期望得到驚豔的快速性能,因為它們帶來巨大的負擔且影響基數計算。
感謝關注!
參考文章:
https://www.sqlpassion.at/archive/2015/02/05/memory-oltp-hekaton-sort-warnings/
注:此文章為
WoodyTu學習MS SQL技術,收集整理相關文檔撰寫,歡迎轉載,請在文章頁面明顯位置給出此文連結!
若您覺得這篇文章還不錯請點選下右下角的推薦,有了您的支援才能激發作者更大的寫作熱情,非常感謝!