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

2017-07-22

最近兩個客戶不約而同碰到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取得資料的比率也只有２%。

MSDN有關SQL Server記憶體架構文章:

所有資料庫軟體的主要設計目的之一，便是將磁碟 I/O 最小化，因為磁碟的讀取和寫入，是電腦上最需要用到大量資源的作業之一。SQL Server 會在記憶體中建立緩衝集區，以保存從資料庫讀取的頁面。SQL Server 的大部分程式碼，主要是用來最小化磁碟和緩衝集區之間實體讀取和寫入數目。SQL Server 會嘗試在兩個目標之間取得平衡：

記憶體架構說明寫的好棒!平衡王道!