SQL SERVER Buffer Pool擴展
Buffer Pool擴展簡介
Buffer Pool擴展是buffer pool 和非易失的SSD硬碟做連接。以SSD硬碟的特點來提高隨機讀性能。
在Buffer Pool 擴展之前,SQL Server 從磁碟中讀入數據,並且存放在buffer pool中以供讀取和修改,修改完之後臟數據還是放在buffer pool中,當內存緊張時,lazy write把臟數據寫入磁碟,並且釋放內存頁。當再次使用當前數據時,再一次從磁碟讀取數據到內存。這樣會導致大量的IO操作,影響性能。對於這類IO操作頻繁造成的性能問題,我們通常採用的方法是加內存,較少IO操作。
SQL SERVER 2014新增加了Buffer Pool的擴展特性,使用SSD硬碟擴展緩存容量;
SSD作為Buffer Pool的擴展,可以很好的解決因頻繁讀取數據引發的系統大量IO操作問題。 Buffer pool在SSD上創建一個緩衝文件,該文件與內存一起構成多層的緩存區,如下圖:
其中第一層為內存構成的緩衝池,第二層為擴展的後備存儲區。在L2上只存儲乾淨的數據,為讀提供緩存,不提供臟數據的緩存。這樣的設計讓我們不用擔心SSD出現損壞的情況,當SSD出現損壞時,只是我們的緩存結構回到了加擴展前的狀態,之前緩存在SSD上的數據,只需要從磁碟上再讀取進內存。
Buffer Pool擴展實現
在SSD盤上設置擴展緩存區:
alter server configuration
set buffer pool extension on
(filename = "I:EXAMPLE.BPE", size= 4GB )
執行該命令後,在SSD盤上回生成一個EXAMPLE的文件,如下圖:
撤銷在SSD盤上設置的擴展緩存區:alter server configuration
set buffer pool extension off
撤銷緩衝池擴展後,將從註冊表中刪除所有相關的配置設置。SQL Server 實例關閉時,將會刪除緩衝池擴展文件。
擴展緩存區注意事項設置緩存區的時候,緩衝池擴展大小最大可為 max_server_memory 值的 32 倍。建議物理內存 (max_server_memory) 的大小與緩衝池擴展的大小之比不應超過 1:16。 介於 1:4 至 1:8 之間的比率是最佳的。
Buffer Pool擴展的優勢
為了驗證Buffer Pool擴展的作用,我們做了3個場景的數據讀取,並對比磁碟物理讀取次數與執行時間。
數據在磁碟上,從磁碟讀取數據。該種場景是在語句執行時,數據存儲在磁碟上,執行步驟如下:
- 使用 dbcc dropcleanbuffers 命令清空 Buffer Pool 中的數據
- 開啟IO 統計 set statistics io on
- 執行查詢語句
從上圖可以看出,當數據在磁碟上時,該查詢執行了2次物理讀即從磁碟讀取了2頁的數據。
從上圖可以看出,該語句讀持續了169毫秒。
數據在內存緩存中,從內存讀取數據
該種場景是在語句執行時,數據在內存緩存上:
- 執行查詢語句,數據從磁碟讀取到緩存
- 開啟IO 統計 set statistics io on
- 再次執行語句,查看IO信息與執行時間
從上圖可以看出,當數據在內存緩存上時,該查詢執行了0次物理讀。
從上圖可以看出,該語句讀持續了2毫秒。
數據在SSD擴展盤上有緩存,從SSD緩存讀取數據該種場景是在語句執行時,數據存儲在Buffer Pool擴展 SSD硬碟上:
定位當前查詢的數據所在的頁,執行查詢,使用 %%physloc%%獲取當前記錄行對應的標記信息(0x85E5000001000000)
標記信息是十六進位,將其轉換為十進位數字,該標記0x後8為表示所在的頁號;在轉換的過程中這8位數字需要做個順序調整,如上圖中的值(0x85E5000001000000)需要轉換為如下計算方式:
select cast(0x0000e585 as int )
獲取頁號後執行如下語句查看該頁在Buffer Pool 中的情況。
select * from sys.dm_os_buffer_descriptors a where a.page_id="58757"
從如上執行結果可以看出,58757 頁對應的is_in_bpool_extension值為1,說明該頁存儲在Buffer Pool 擴展上。
執行查詢語句,查看IO信息,如下圖:
從上圖可以看出,當數據在Buffer Pool 擴展有緩存時,該查詢執行了0次物理讀。
從上圖可以看出,該語句讀持續了49毫秒。
結論通過同一環境中的如上三個場景測試,測試結果如圖:
物理讀次數 | 持續時間 | |
數據頁在磁碟 | 2次 | 169毫秒 |
數據頁緩存在內存 | 0 | 2毫秒 |
數據頁緩存在SSD Buffer Pool擴展 | 0 | 49毫秒 |
通過如上測試,我們知道使用SSD Buffer Pool擴展後,當內存不足時內存中的乾淨數據會移至SSD Buffer Pool擴展上,此時再次讀取這些數據的時候降低了IO操作,並且讀取時間較直接從硬碟讀取數據快。
※ABP從入門到精通(3):aspnet-zero-core 使用Redis緩存
※原生js之千位分隔符
※Golang使用pprof和qcachegrind進行性能監控
※python中數字類型與處理工具
※SpringBoot上傳任意文件功能的實現
TAG:達人科技 |
※GIS on CentOS 7之PostgreSQL&PostGIS
※搜索:ElasticSearch OR MySQL?
※AWS CEO炮轟Oracle和SQL Server,背後是給Aurora鋪路!
※EF Core:一統SQL和NoSQL資料庫
※用TPC-DS測試Sql server,Oracle
※雲資料庫TencentDBforMySQL
※開源資料庫 PostgreSQL、MariaDB 和 SQLite 的對比
※Google 的雲端資料庫Cloud SQL:開始支持 PostgreSQL
※微軟收購Citus Data擴展PostgreSQL服務
※配置SAP Solution Manager連接監控遠程Microsoft SQL Server
※蘋果開源NoSQL 資料庫FoundationDB
※WordPress插件YITH WooCommerce Wishlist SQL注入漏洞
※雲資料庫TencentDBforPostgreSQL
※SQL JOIN 中 on 與 where 的區別
※乾貨分享!CynosDB for PostgreSQL 架構淺析
※SQL樣式指南 · SQL Style Guide
※NoSQL&Redis介紹
※雲資料庫TencentDforSQLServer
※初探SQL Operation Studio
※WordPress Plugin AutoSuggest插件SQL注入復現與分析