搜尋本站文章

2016-02-18

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()
SSDHDD效能差異
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

2016-02-04

效能調教:鎖定記憶體分頁(Lock Pages in Memory, LPIM) - 檢查是否有啟用此 Windows 原則

若要檢查是否有啟用 鎖定記憶體分頁(Lock Pages in Memory, LPIM)

請參考以下的方式:

(1) DBCC MEMORYSTATUS

-- 01_未啟用_鎖定記憶體分頁_DBCC MEMORYSTATUS



-- 02_已啟用_鎖定記憶體分頁_DBCC MEMORYSTATUS



(2) SQL Server error log

-- 03_未啟用_鎖定記憶體分頁_SQL Server error log



在 SQL Server error log 顯示:


Using conventional memory in the memory manager.


-- 04_已啟用_鎖定記憶體分頁_SQL Server error log



在 SQL Server error log 顯示:

Using locked pages in the memory manager


(3) T-SQL 陳述式 - sys.dm_os_memory_nodes

select osn.node_id, osn.memory_node_id, osn.node_state_desc, omn.locked_page_allocations_kb
from sys.dm_os_memory_nodes omn inner join sys.dm_os_nodes osn 
on (omn.memory_node_id = osn.memory_node_id)
where osn.node_state_desc <> 'ONLINE DAC'


-- 05_未啟用_鎖定記憶體分頁_sys.dm_os_memory_nodes


-- 06_已啟用_鎖定記憶體分頁_sys.dm_os_memory_nodes





請參考以下方式來啟用 鎖定記憶體分頁(Lock Pages in Memory, LPIM):

效能調教:鎖定記憶體分頁(Lock Pages in Memory, LPIM)
http://sharedderrick.blogspot.tw/2016/02/lock-pages-in-memory-lpim.html



參考資料

How to enable the "locked pages" feature in SQL Server 2012
https://support.microsoft.com/en-us/kb/2659143

Clarification about the two LPIM upgrade rules that did not FAIL
https://blogs.msdn.microsoft.com/psssql/2012/04/30/clarification-about-the-two-lpim-upgrade-rules-that-did-not-fail/

FIX: Locked page allocations are enabled without any warning after you upgrade to SQL Server 2012
https://support.microsoft.com/en-us/kb/2708594

效能調教:鎖定記憶體分頁(Lock Pages in Memory, LPIM)
http://sharedderrick.blogspot.tw/2016/02/lock-pages-in-memory-lpim.html

伺服器記憶體伺服器組態選項
https://msdn.microsoft.com/zh-tw/library/ms178067(v=sql.120).aspx

針對 4 GB 以上的實體記憶體啟用記憶體支援
https://technet.microsoft.com/zh-tw/library/ms179301(v=sql.105).aspx

如何使用 DBCC MEMORYSTATUS 命令,來監視 SQL Server 2005 上的記憶體使用量
https://support.microsoft.com/zh-tw/kb/907877

INF: 使用 DBCC MEMORYSTATUS 」 來監視 SQL Server 記憶體使用量
https://support.microsoft.com/zh-tw/kb/271624

sys.dm_os_memory_nodes (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/bb510622(v=sql.120).aspx

效能調教:鎖定記憶體分頁(Lock Pages in Memory, LPIM)

雖然非必要,不過我們建議在使用 64 位元作業系統時在記憶體中鎖定分頁。
對於 32 位元作業系統,您必須先授與 Lock pages in memory(LPIM) 權限,再針對 SQL Server 設定 AWE。

說明:

此安全性設定決定哪些使用者能使用處理程序來保留實體記憶體中的資料,阻止系統將資料分頁到磁碟上的虛擬記憶體。

履行此特殊權限會降低可用的隨機存取記憶體 (RAM) 數量,而對系統效能造成顯著影響。
預設值: 無。



此 Windows 原則 - 鎖定記憶體分頁(Lock Pages in Memory):
決定哪些帳戶可以使用處理序將資料保留在實體記憶體中,以防止系統將資料傳送到磁碟上的虛擬記憶體。

在 SQL Server 2005, 2008, 2008 R2 (32-bit) 版本中,設定 32 位元 SQL Server 執行個體的 [鎖定分頁] 選項時,需要具有 sqlservr.exe 執行權限的帳戶具有 LPIM 使用者權限,而且 'awe_enabled' 組態選項設定為 ON。

若要停用 SQL Server 的 [鎖定記憶體中的分頁] 選項,請移除 SQL Server 啟動帳戶的 [已鎖定記憶體中的分頁] 使用者權限。

-- SQL Server版本與鎖定記憶體分頁





停用鎖定記憶體中的分頁

1. 在 [開始] 功能表上,按一下 [執行]。在 [開啟舊檔] 方塊中,輸入 gpedit.msc。此時會開啟 [群組原則] 對話方塊。

2. 在 [群組原則] 主控台中,依序展開 [電腦設定] 和 [Windows 設定]。

3. 展開 [安全性設定],然後展開 [本機原則]。

4. 選取 [使用者權限指派] 資料夾。這些原則會顯示在詳細資料窗格中。

5.在窗格中按兩下 [鎖定記憶體中的分頁]。

6. 在 [本機安全性原則設定] 對話方塊中,選取具有 sqlservr.exe 執行權限的帳戶,然後按一下 [移除]。

-- 01_鎖定記憶體中的分頁



-- 02_鎖定記憶體中的分頁_本機安全性設定



-- 03_鎖定記憶體中的分頁_解說



-- 04_設定SQL Server服務啟動帳戶具備鎖定記憶體分頁的權利






參考資料

伺服器記憶體伺服器組態選項
https://msdn.microsoft.com/zh-tw/library/ms178067(v=sql.120).aspx

How to enable the "locked pages" feature in SQL Server 2012
https://support.microsoft.com/en-us/kb/2659143

針對 4 GB 以上的實體記憶體啟用記憶體支援
https://technet.microsoft.com/zh-tw/library/ms179301(v=sql.105).aspx