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

SQL 效能調教：沒有使用到索引，可改用SSD或PCIe介面的Flash儲存裝置來達到 秒殺，提供更佳的服務水準給客戶!

結論

(一)沒有使用到索引(Index)，就會耗用Disk I/O

1. 使用SSD，在一定的資料量內，可以達到秒殺，客戶不用浪費時間在等待傳統硬碟(HDD)的慢速I/O。

2. 對於講求I/O效能的應用以及重視服務水準的系統，SSD受限於相對SATA或SAS的傳輸介面，這就不夠用了。換句話說，遇到更大的資料量時，又浪費客戶的時間去等待!

3. 目前硬體發展，已有PCIe介面(已有80 Gb/s)的Flash儲存裝置，可取代相對慢速SATA或SAS的傳輸介面(僅6 Gb/s)。

Flash儲存裝置，例如：Fusion-io等，提供更佳的服務水準給客戶享用，不浪費客戶時間在傳統硬碟設備的等待上!

(二)檢視執行計畫時

除觀察估計的子樹成本外，還要進一步去理解：

1. 「估計的I/O成本」與「估計的CPU成本」。
2. 區分此SQL陳述式是耗用CPU資源，還是Disk I/O資源的程式，釐清效能調教的方向。

(三)建立合適的索引來提升效能

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

---

沒有使用索引

比較SSD與傳統硬碟(HDD)的差異

資料表使用空間(MB)	儲存設備	費時(秒)
945	SSD	1.399
	HDD	10.586
1,953	SSD	2.351
	HDD	27.363
3,906	SSD	5.117
	HDD	88.807

使用空間 儲存設備	945MB	1,953MB	3,906MB
SSD	1.399(秒)	2.351(秒)	5.117(秒)
HDD	10.586(秒)	27.363(秒)	88.807(秒)
SSD與HDD效能差異	7.566(倍)	11.638(倍)	17.355(倍)

小結

1. 傳統硬碟(HDD)：耗費太多時間!

2. 資料量越大，凸顯了傳統硬碟(HDD)的效率不佳之問題!

---

---

傳統硬碟版本 - 沒有使用到索引 - 慢慢等!

費時長達：約 10 秒鐘。
SQL Server Execution Times:
CPU time = 998 ms,  elapsed time = 10586 ms.

-- 001_HDD_沒有使用到索引

SSD版本 - 沒有使用到索引 - 秒殺

費時：約 1 秒鐘。
SQL Server 執行次數:
CPU 時間 = 171 ms，經過時間 = 1399 ms。

-- 002_SSD_沒有使用到索引

沒有使用到索引

觀察其執行計畫，可以看到：
估計的I/O成本，就耗用掉：89.6134。
估計的CPU成本，只用：0.0332944

-- 003_執行計畫_沒有使用到索引_資料表掃描

有使用到索引

觀察其執行計畫，可以看到：
估計的I/O成本，就耗用掉：0.003125。
估計的CPU成本，只用：0.0032831。

-- 004_執行計畫_有使用到索引_叢集索引搜尋

-- 005_HDD_有使用到索引

-- 006_SSD_有使用到索引

示範環境

使用兩個資料表，其資料結構、資料、資料列筆數等都相同，唯一差異是：一個有建立索引，一個沒有建立索引。
資料表約使用：945 MB，資料列筆數：120999。

-- 007_資料表的使用空間資訊

-- 008_回傳的資料列

-- 009_資料檔案mdf大小約2GB

---

範例程式碼如下：

/*
沒使用到索引，就會耗用Disk I/O!
比較 傳統硬碟 與 SSD 的差異
*/

-- 01_建立資料庫
SET NOCOUNT ON
USE master
GO
CREATE DATABASE [TSIO] ON  PRIMARY 
( NAME = N'TSIO', FILENAME = N'C:\TSQLDB\TSIO.mdf' , SIZE = 2097152KB , 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
(sid INT IDENTITY, wdatetime datetime2 ,cdata char(8000))
GO
CREATE TABLE TS_PK
(sid INT IDENTITY PRIMARY KEY, wdatetime datetime2 ,cdata char(8000))
GO

-- 03_新增資料列
DECLARE @cnt INT=1
WHILE @cnt < 121000
BEGIN
 INSERT TS_PK VALUES (SYSDATETIME(),CONVERT(varchar(100),GETDATE(), 113)+' '+ APP_NAME())
 INSERT TS_NOPK 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 a3.name, a2.name
GO

-- 05_查詢_有使用到索引，回傳一筆
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS TIME ON
GO
SELECT * FROM TS_PK WHERE sid=1

/*
SQL Server 剖析與編譯時間: 
   CPU 時間 = 0 ms，經過時間 = 0 ms。
SQL Server 剖析與編譯時間: 
   CPU 時間 = 0 ms，經過時間 = 0 ms。

 SQL Server 執行次數: 
，CPU 時間 = 0 ms，經過時間 = 0 ms。
*/

-- 06_查詢_沒有使用到索引，回傳一筆
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
SELECT * FROM TS_NOPK  WHERE sid=1
GO

/*
-- SSD
SQL Server 剖析與編譯時間: 
   CPU 時間 = 0 ms，經過時間 = 5 ms。
SQL Server 剖析與編譯時間: 
   CPU 時間 = 0 ms，經過時間 = 0 ms。

 SQL Server 執行次數: 
，CPU 時間 = 171 ms，經過時間 = 1399 ms。

-- HDD
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 998 ms,  elapsed time = 10586 ms.
*/

-- 刪除範例資料庫
USE master
GO
DROP DATABASE TSIO
GO

---

補充：
資料表使用空間為： 2GB、4GB 時的測試資料。

---

參考資料：
記憶體儲存裝置｜Fusion-io ioDrive PCIe Flash 企業級PCIe Flash產品
http://www.ithome.com.tw/node/72973

PCIe儲存介面的新應用：突破I/O瓶頸
http://www.ithome.com.tw/tech/93047

利用PCIe突破儲存傳輸通道瓶頸
http://www.ithome.com.tw/article/93046