[SQL Server]記憶體配置與效能

最近兩個客戶不約而同碰到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

資料庫的效能監視器

記憶體架構