搜尋本站文章

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