最近兩個客戶不約而同碰到SQL Server記憶體配置問題,一個客戶諮詢SCOM(System Center Operations Manager)監測到BufferCacheHitRatio比例過低以及Page Life Expectancy分頁停留在快取中的時間太低的警示;另一個客戶則是在測試環境因為資源太少(2GB記憶體)使得Table Scan執行語法跑很久,來筆記一下記憶體壓力對SQL執行效能上的影響。
為了能測試記憶體壓力下的效能差異,我們將準備一個4GB大小的資料表,比較在記憶體1.5GB及6.4GB的配置下,兩者執行資料表掃描(Table Scan)的差異。
準備測試環境
use tempdb
create table t1
(
c1 int identity,
c2 char(500),
primary key(c1)
)
insert into t1 values('test')
建立大量資料
insert into t1
select c2 from t1
go 23
查詢table大小
EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''
ok,我們有一個800萬筆,Data使用4GB空間的資料表了
從管理工具觀察table
關閉read ahead(為了待會取得正確的Buffer cache hit ratio)
DBCC TRACEON(652, -1)
實驗組
調整instance最高只能使用到1.5GB的記憶體。
-- Turn on advanced options
EXEC sp_configure'Show Advanced Options',1;
Go
RECONFIGURE;
GO
-- Set max server memory
EXEC sp_configure'max server memory (MB)',1500;
GO
RECONFIGURE;
GO
清理buffer與執行計畫
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
執行實驗組
set statistics io on
set statistics time on
select count(*) from t1 where c2 = 'test'
i/o及時間統計:
資料表 't1'。掃描計數 21,邏輯讀取 567296,實體讀取 531785,CPU 時間 = 258634 ms,經過時間 = 108160 ms。
叢集索引掃描=資料表掃描=4GB
對照組
調整instance最高使用到6.4GB的記憶體。
-- Turn on advanced options
EXEC sp_configure'Show Advanced Options',1;
GO
RECONFIGURE;
GO
-- Set max server memory
EXEC sp_configure'max server memory (MB)',6400;
GO
RECONFIGURE;
GO
清理buffer與執行計畫
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE WITH NO_INFOMSGS
執行對照組
set statistics io on
set statistics time on
select count(*) from t1 where c2 = 'test'
i/o及時間統計:
資料表 't1'。掃描計數 21,邏輯讀取 563148,實體讀取 59844,CPU 時間 = 219539 ms,經過時間 = 34469 ms。
實驗組與對照組數據整理
# |
邏輯讀取 |
實體讀取 |
CPU |
經過時間 |
實驗組(1.5G Ram) |
567296 |
531785 |
258.634(s) |
108.160(s) |
對照組(6.4G Ram) |
563148 |
59844 |
219.539(s) |
34.469(s) |
由於實驗組記憶體不足,Buffer Cache持續大量的替換,執行時需要更多的實體讀取(physical read),也就是更多的磁碟存取、更多的CPU時間以及執行時間。
BufferCacheHitRatio及Page Life Expectancy:
Buffer Cache Hit Ratio
不需讀取磁碟即可在緩衝區快取中找到之頁面的百分比。此比率是過去數千個分頁存取中,快取叫用總數除以快取查閱所得的結果。時間一久,比率的變動會越來越小。從快取讀取遠比從磁碟讀取節省成本,因此您會希望此比率越高越好。通常,您可以藉由增加 SQL Server 的可用記憶體數量,來提高緩衝區快取點擊率。
Page Life Expectancy
是指在沒有參考之下,分頁存留在緩衝集區的秒數。Page Life Expectancy 較高就表示系統不必存取硬碟,就能夠在快取中找到所需的資料。
我們試著在實驗組及對照組在執行20秒之後去偵測系統當前兩個performance counters(BufferCacheHitRatio及Page Life Expectancy):
use tempdb
SELECT
(CAST(SUM(CASE LTRIM(RTRIM(counter_name)) WHEN 'Buffer cache hit ratio' THEN CAST(cntr_value AS INTEGER) ELSE NULL END) AS FLOAT) /
CAST(SUM(CASE LTRIM(RTRIM(counter_name)) WHEN 'Buffer cache hit ratio base' THEN CAST(cntr_value AS INTEGER) ELSE NULL END) AS FLOAT)) * 100
AS BufferCacheHitRatio
FROM
sys.dm_os_performance_counters
WHERE
LTRIM(RTRIM([object_name])) LIKE '%:Buffer Manager' AND
[counter_name] LIKE 'Buffer Cache Hit Ratio%'
SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name like '%Page Life Expectancy%'
實驗組(1.5G Ram)
對照組(6.4G Ram)
整理下表
# |
BufferCacheHitRatio |
Page life expectancy(秒) |
實驗組(2G Ram) |
2.2% |
1 |
對照組(8G Ram) |
87% |
280 |
從效能監控數據觀察,實驗組執行時記憶體不足,Page停留時間甚至只有1秒,從BufferCache取得資料的比率也只有2%。
MSDN有關SQL Server記憶體架構文章:
所有資料庫軟體的主要設計目的之一,便是將磁碟 I/O 最小化,因為磁碟的讀取和寫入,是電腦上最需要用到大量資源的作業之一。SQL Server 會在記憶體中建立緩衝集區,以保存從資料庫讀取的頁面。SQL Server 的大部分程式碼,主要是用來最小化磁碟和緩衝集區之間實體讀取和寫入數目。SQL Server 會嘗試在兩個目標之間取得平衡:
記憶體架構說明寫的好棒!平衡王道!
小結:
-
答案是更多的實體磁碟讀取、CPU使用(10x)以及執行時間(2x)。
-
複雜系統內的t-sql語法要都不走到Table Scan好難。
-
看到客戶SCOM有監控Buffer Cache Hit Ratio及Page Life Expectancy,覺得好專業(快筆記)!
- 歷史交易資料封存。
參考
Suggested Max Memory Settings for SQL Server 2005/2008
Microsoft.SQLServer.2014.DBEngine.PageLifeExpectancyMonitor
MSSQL 2016: Buffer Cache Hit Ratio