(一)沒有使用到索引(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
沒有留言:
張貼留言