SQL 效能調教：規劃記憶體(RAM Capacity)到比資料表、資料庫還要大的容量

結論

(1) 配置足夠的記憶體，讓 SQL Server 緩衝集區大到足以存放資料表


擴充記憶體(RAM Capacity)到比資料庫 還要大的容量。
若未配置足夠的記憶體(RAM)給 SQL Server 使用，將導致耗費時間在等待慢速的Disk I/O上!


(2) 建立合適的索引，避免發生存取大量Disk I/O


沒有使用到索引(Index)，就會耗用Disk I/O。
Table Scan、Index Scan，是耗用大量的Disk I/O。


索引的建立需要考量更多的事情，筆者將在後續文章討論。

(3) 使用高速SSD或PCIe介面的Flash儲存裝置等來處理慢速Disk I/O的問題

(4) 規劃良好的資料表設計結構、資料歸檔機制、資料存取策略(例如：push、pull、讀寫分流)等，避免發生存取大量Disk I/O


使用合適的資料庫架構規劃、軟體架構規劃、適合的演算法等，是我們軟體人員一致都認同的解決方案。

但每個資訊系統或多或少都有「技術債(Technical Debt)」，當年抄捷徑的技術債，遲早要還的!


理由

所有資料庫軟體的主要設計目的之一，便是將磁碟 I/O 最小化，因為磁碟的讀取和寫入，是電腦上最需要用到大量資源的作業之一。

SQL Server 會在記憶體中建立緩衝集區，以保存從資料庫讀取的頁面。
SQL Server 的大部分程式碼，主要是用來最小化磁碟和緩衝集區之間實體讀取和寫入數目。


延伸閱讀

對回應速度且重視服務水準的系統，需要更快速的執行效能，資料庫廠商推出了：In-Memory Database。

Oracle Database In-Memory
http://www.oracle.com/us/products/database/options/database-in-memory/overview/index.html?ssSourceSiteId=opn

SQL Server In-Memory OLTP (記憶體中最佳化)
https://msdn.microsoft.com/zh-tw/library/dn133186(v=sql.120).aspx

---

實測數據

情境 1：記憶體配置足以存放資料表

環境：記憶體配置 1 GB，但資料表使用空間僅約 500 MB。

資料表使用空間	SQL Server配置的記憶體	執行次數	耗時(秒)
476.64(MB)	1024(MB)	第1次執行	9.155
		第2次執行	0.017
		第3次執行	0.019

分析：

第 1 次執行，會慢的原因是：由硬碟將資料讀取到記憶體(RAM)內，需要耗費時間在等待慢速的Disk I/O上!

第 2 次之後的執行，因資料表都在 SQL Server 緩衝集區內，無須存取存取慢速的Disk I/O，達成秒殺報表的需求!


情境 2：記憶體配置不足

環境：記憶體僅配置 1024 MB，而資料表使用空間大於此配置(例如：約 2031 MB)

資料表使用空間	SQL Server配置的記憶體	執行次數	耗時(秒)
2031.57(MB)	1024(MB)	第1次執行	38.887
		第2次執行	39.123
		第3次執行	39.109

分析：

由於記憶體配置不足，使得 SQL Server 緩衝集區沒有足夠的記憶體可以存放資料表，導致每次存取資料表都是執行慢速的 Disk I/O 作業!

---

實測的截圖資料

01_實測數據

02_資料表的使用空間

03_觀察：各個資料庫所使用的緩衝集區_2GB資料表

21_資料表500MB，記憶體1GB_第1次查詢

22_資料表500MB，記憶體1GB_第2次查詢

23_資料表500MB，記憶體1GB_第3次查詢

24_資料表2GB，記憶體1GB_第1次查詢

25_資料表2GB，記憶體1GB_第2次查詢

26_資料表2GB，記憶體1GB_第3次查詢

31_查詢目前_最大可用記憶體的組態選項值_SSMS

32_查詢目前_最大可用記憶體的組態選項值_TSQL

---

範例程式碼

 
-- 01_建立資料庫
SET NOCOUNT ON
USE master
GO
CREATE DATABASE [TSIO] ON  PRIMARY
( NAME = N'TSIO', FILENAME = N'C:\TSQLDB\TSIO.mdf' , SIZE = 8192MB , FILEGROWTH = 102400KB )
 LOG ON
( NAME = N'TSIO_log', FILENAME = N'C:\TSQLDB\TSIO_log.ldf' , SIZE = 102400KB , FILEGROWTH = 102400KB )
GO
 
-- 02_建立兩個範例資料表，一個有索引，一個沒有
USE TSIO
GO
CREATE TABLE TS_NOPK_2GB
(sid INT IDENTITY, wdatetime datetime2 ,cdata char(8000))
GO
CREATE TABLE TS_PK_2GB
(sid INT IDENTITY PRIMARY KEY, wdatetime datetime2 ,cdata char(8000))
GO

CREATE TABLE TS_NOPK_500MB
(sid INT IDENTITY, wdatetime datetime2 ,cdata char(8000))
GO
CREATE TABLE TS_PK_500MB
(sid INT IDENTITY PRIMARY KEY, wdatetime datetime2 ,cdata char(8000))
GO

 
-- 03_新增資料列
DECLARE @cnt INT=1
WHILE @cnt < 61000
BEGIN
 INSERT TS_PK_500MB VALUES (SYSDATETIME(),CONVERT(varchar(100),GETDATE(), 113)+' '+ APP_NAME())
 INSERT TS_NOPK_500MB VALUES (SYSDATETIME(),CONVERT(varchar(100),GETDATE(), 113)+' '+ APP_NAME())
 SET @cnt +=1
END
GO
 
-- 04_查詢目前資料庫內，每一個資料表的使用空間資訊
SELECT a3.name AS N'結構描述', a2.name AS N'資料表', a1.rows AS N'資料列筆數', (a1.reserved + ISNULL(a4.reserved,0))* 8.0/1024 AS N'配置的空間總量(MB)', a1.data * 8.0/1024 AS '資料(MB)', 
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8.0/1024 AS N'索引(MB)', 
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8.0/1024 AS N'尚未使用(MB)',
a2.create_date N'建立日期', a2.modify_date N'修改日期'
FROM (SELECT ps.object_id, SUM ( CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END ) AS [rows], 
SUM (ps.reserved_page_count) AS reserved, SUM ( CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) 
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data, 
SUM (ps.used_page_count) AS used 
FROM sys.dm_db_partition_stats ps 
GROUP BY ps.object_id) AS a1 LEFT OUTER JOIN
(SELECT it.parent_id, SUM(ps.reserved_page_count) AS reserved, SUM(ps.used_page_count) AS used 
FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) 
WHERE it.internal_type IN (202,204) 
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id) 
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id ) 
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id) 
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY a1.data DESC
GO

 
-- 查詢：伺服器所組態的最大可用記憶體是：1024 MB
SELECT
cfg.name AS N'組態選項', cfg.minimum AS N'最小值', cfg.maximum AS N'最大值', cfg.value AS N'設定的組態選項值', cfg.value_in_use AS N'執行中的值', cfg.description AS N'描述'
FROM sys.configurations AS cfg
WHERE name ='max server memory (MB)'

-- 原本：2147483647
USE master
GO
EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'1024'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

/*=========================================================*/

-- 05_查詢_沒有使用到索引，回傳一筆
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS TIME ON
GO

-- 第 1 次：查詢此資料表的使用空間是：500MB
/*
目前組態的最大可用記憶體是：1GB，
*/

SELECT * FROM TS_NOPK_500MB WHERE sid=1

-- 第 2 次：查詢此資料表的使用空間是：500MB

SELECT * FROM TS_NOPK_500MB WHERE sid=1

-- 第 3 次：查詢此資料表的使用空間是：500MB

SELECT * FROM TS_NOPK_500MB WHERE sid=17


-- 05_查詢_沒有使用到索引，回傳一筆
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS TIME ON
GO

/*=========================================================*/

-- 第 1 次：查詢此資料表的使用空間是：2GB
/*
目前組態的最大可用記憶體是：1GB，
*/

SELECT * FROM TS_NOPK_2GB WHERE sid=1

-- 第 2 次：查詢此資料表的使用空間是：2GB

SELECT * FROM TS_NOPK_2GB WHERE sid=1

-- 第 3 次：查詢此資料表的使用空間是：2GB

SELECT * FROM TS_NOPK_2GB WHERE sid=1

/*=========================================================*/

-- 觀察：各個資料庫所使用的緩衝集區 - SQL Server 緩衝集區(buffer pool)中的使用資訊
SELECT db.name, (COUNT(*)*8.0)/1024 AS N'耗用緩衝集區(MB)'
FROM sys.dm_os_buffer_descriptors ob INNER JOIN sys.sysdatabases db
ON ob.database_id =db.dbid
GROUP BY db.name
ORDER BY 2 DESC
GO

---

參考資料

SQL 效能調教：沒有使用到索引，可改用SSD或PCIe介面的Flash儲存裝置來達到 秒殺，提供更佳的服務水準給客戶!
http://sharedderrick.blogspot.tw/2016/02/disk-iossdpcieflash.html

如何降低在 64 位元版本的 SQL Server 的緩衝區集區記憶體的分頁
https://support.microsoft.com/zh-tw/kb/918483

如何使用 SQL Server 中的組態選項來調整記憶體使用量
https://support.microsoft.com/zh-tw/kb/321363

緩衝區管理
https://technet.microsoft.com/zh-tw/library/aa337525(v=sql.105).aspx

記憶體架構
https://technet.microsoft.com/zh-tw/library/ms187499(v=sql.105).aspx

技術債觀念及實務
http://www.ithome.com.tw/voice/100462

抄捷徑的技術債，遲早要還的
http://www.ithome.com.tw/node/71807

In-Memory OLTP (記憶體中最佳化)
https://msdn.microsoft.com/zh-tw/library/dn133186(v=sql.120).aspx