搜尋本站文章

2016-03-03

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