Transcript Insights and Answers for IT Professionals

Insights and Answers for IT Professionals
SQL Server 2000 效能調整
胡百敬
臺灣微軟顧問
(恆逸資訊教育訓練處)
講座大綱







SQL Server 2000 運行架構剖析
執行效能瓶頸監控
索引機制的使用與效能考量
交易與查詢調校
應用程式設計的技巧與注意事項
案例研討
結論
使用者與SQL Server的溝通過程
1
2
使用者
使用者應用程式
查詢
結果集
Database API
(OLE DB, ODBC,
DB-Library)
Client Net-Library
SQL Server
Server Net-Libraries
Open Data Services
3
查詢
結果集
5
Relational Engine
伺服器
4
Storage Engine
處理器
記憶體
資料庫
硬體限制




記憶體
儲存子系統
中央處理器
網路系統
SQL Server 2000 如何使用記憶體
Server Net-Libraries
Open Data Services
執行檔部分
(非用不可)
SQL Server Service
Distributed
Query OLE DB
Providers
OLE
Automation
Objects
Extended
Stored
Procedures
System-Level Data Structures
Procedure Cache
Buffer Cache
Log Caches
Connection Context
資料結構區塊
(可以動態增減)
監控記憶體的使用
物件\計數器(Counter)
描述
使用參考
Memory\ Available
KBytes
當下任何程序都可
以使用的記憶體數
量
應該大於 5,000 KB，若小於
5,000 KB 表示 SQL Server 已
經沒有記憶體可用，這也可以
直接觀察 Task Manager 的
Physical Memory/Available
SQL Server\ Buffer
Manager\Free pages
有多少可用的
Pages
應該要大於零
SQL Server\ Buffer
Manager\Buffer Cache
Hit Ratio
從 Buffer 讀出資料
與從硬碟讀出資料
的比率
應該要大於 95%
作業系統從硬碟讀取
或寫入的資料頁數以
解決 page faults(要
參考的記憶體頁不存
在實體記憶體中)。
讀取磁碟以解決 page
faults 的次數
不應該持續大於一，若持續
大於一表示實體記憶體不足
Memory\ Pages/sec
Memory\ Page
Reads/sec
不應該持續大於五，若持續大
於五表示實體記憶體不足
SQL Server 2000 如何從硬碟讀取資料
SQL Server Buffer Manager
C
7
E
A
D
1
8
3
F
6
G
4
H
B
H
F
G
E
2
5
C
1
2 3 4
5
6 7 8
B
Local Database
Memory Buffer Cache Pages
C
1
D
E
2
A
3
D
4
F
5
H
6
G
7
B
8
Windows 2000
I/O Buffer (64 KB)
8-KB increments
A
計算磁碟子系統的效能瓶頸

估算每一個裝置的 I/O 處理量
每秒傳輸次數
x 資料區塊大小
= 最大傳輸量
範例
150(循序存取
x 64(資料區塊大小)
= 9.6 MB(最大傳輸量
(KB) (每秒多少
MB)
每秒傳輸次數
KB)
每秒多少 MB)

估算每一個磁碟控制器(SCSI或RAID)控
制硬碟的量
控制器的傳輸速率
÷ 最大傳輸量
每個控制器= 4 (每個控制器
範例
40(控制器的傳輸速率
÷ 9.6=(最大傳輸量
(每秒多少
MB) (每秒多少
MB)
連接的最大硬碟數
每秒多少
MB)
每秒多少
MB) 連接的最大硬碟數)

估算 PCI Bus 所能使用的控制器的量
PCI bus
傳輸速率
控制器的傳輸速率
= 連接的控制器數目
範例
266
(PCI bus ÷÷ 40
(控制器的傳輸速率
= 6 (連接的控制
(每秒多少
MB)
(每秒多少
MB)
傳輸速率
每秒多少
MB) 每秒多少
MB)
器數目)
監控硬碟的使用
物件\計數器
(Counter)
PhysicalDisk\
%Disk Time
PhysicalDisk\
Avg. Disk Queue
Length
PhysicalDisk\
Disk Reads/sec
PhysicalDisk\
Disk Writes/sec
描述
使用參考
硬碟花在讀寫上的
時間
應該小於百分之九
十
在取樣的間隔內平
均有多少存取的命
令停在 queue 內
讀取動作的使用率
應該持續性地小於
3
讀取動作的使用率
應該持續性地小於
系統的規格
應該持續性地小於
系統的規格
硬碟子系統的使用建議

設定適當的檔案與 log 的初始大小與增長
的量

將資料檔與記錄檔分開不同硬碟存放

使用 FileGroup

使用 RAID

使用 Partition View
SQL 2000 如何使用中央處理器和執行緒
(Threads)
SQL Server
維護一個 Thread Pool
來處理使用者的需求
如查詢或是連結
1
查詢
使用自己的排程
而非作業系統的，
SQL Server
來決定哪個處理器
Open Data Services
2
執行哪條執行緒
UMS
Processor
IOCompletion Port
查詢 (User Mode
Scheduler)
0
Scheduler
Thread
Thread
Thread
Thread
Thread
Thread
Thread
Thread
Thread
查詢
UMS
Scheduler
查詢
UMS
Scheduler
記憶體
Processor
1
Local
DB
Processor
n 處理器處理查詢
從記憶體或是硬
碟中取出資料，
並將這些結果還
Threads
4 回Storage Engine
Relational Engine
將執行緒還回
IOCompletion
Port
Storage Engine
Rowsets
3
監控執行緒和中央處理器的使
用
物件\計數器(Counter)
Processor\
%Processor Time
System\ Context
Switches/sec
System\ Processor
Queue Length
Processor\
%Privileged Time
描述
使用參考
監控處理器花在處理非
停滯狀態執行緒的時間
所佔的百分率
應該小於百分之百，若
常常百分之百考慮增加
處理器，或減少工作量
每秒中處理器切換的次
數
若計數值達到 8000，
且前述的計數值達百分
之百考慮使用 fiber
mode
不應該持續超過 2。若
持續超過 2考慮增加處
理器，或減少工作量
有多少執行緒正在等待
處理器
處理器花多少百分率的
時間在處理 privileged
的命令(kernel 命令，
如 I/O)
如果大量的比率在作核
心的動作，考慮增強硬
碟子系統。
效能瓶頸監控的建議

設定 Performance Counter 的警告訊息

利用 Profiler 監控忙碌時期的運作找出最耗
資源的運作
設定索引


索引的運作方式

以非叢集索引查詢，且資料表沒有叢集索引

以叢集索引查詢

以非叢集索引查詢，但資料表有叢集索引
設定與維護索引的建議
利用非叢集索引在 Heap 中找尋符合的記錄
sysindexes
id
indid = 2
root
Akhtar
...
Martin
SELECT lastname,
firstname
Page 37
Page
28
Page 12 - Root
AkhtarFROM member
Martin
Ganio
Smith
WHERE
lastname
...
...
Page 41
Akhtar 4:706:01
Barr 4:705:03
Con 4:704:01
Funk 4:706:02
Funk 4:704:02
Non-Leaf
Level
非叢集
索引
BETWEEN 'Masters' AND 'Rudd'
Page 51
Ganio 4:709:01
Hall 4:709:04
Jones 4:709:02
Jones 4:708:03
Jones 4:707:03
Page 61
Martin 4:708:01
Matey 4:706:04
Matey
Ota 4:707:02
Ota
Phua 4:708:02
Rudd 4:705:01
Page 71
Leaf Level
(鍵值)
Smith 4:706:03 (Key Value)
Smith 4:708:04
Smith 4:707:01
White 4:704:03
White 4:705:02
Heap
Page 704
01 ... Conn
02 ... Funk
03 ... White
... ... ...
... ... ...
File ID #4
Page 705
01 ... Rudd
02 ... White
03 ... Barr
... ... ...
... ... ...
Page 706
01 ... Akhtar
02 ... Funk
03 ... Smith
04 ... Matey
... ... ...
Page 707
01 ... Smith
02 ... Ota
02
03 ... Jones
... ... ...
... ... ...
Page 808
01 ... Martin
02
02 ... Phua
03 ... Jones
04 ... Smith
... ... ...
Page 709
01 ... Ganio
02 ... Jones
03 ... Hall
... ... ...
... ... ...
在叢集索引中找記錄
sysindexes
id
indid = 1
root
叢集索引
Clustered Index
Akhtar
…
Martin
Page 140 - Root
Akhtar
SELECT
lastname, firstname Martin
Ganio
Smith
FROM
member
…
…
WHERE
lastname
=
'Ota'
Page 141
Page 145
Akhtar 2334
Barr
5678
Con
2534
Funk 1334
Funk 1534
...
...
Page 100
...
...
...
...
...
...
Ganio 7678
Hall
8078
Jones 2434
Jones 5978
Jones 2634
...
...
Page 110
...
...
...
...
...
...
Martin 1234
Martin 7778
Ota
5878
Phua 7878
Rudd 6078
...
...
Page 120
...
...
...
...
...
...
Smith 1434
Smith 5778
Smith 7978
White 2234
White 1634
...
...
Page 130
...
...
...
...
...
...
透過非叢集索引在叢集索引內找記錄
sysindexes
以
First Name
Nonclustered
為鍵值的
Index on
非叢集索引
First Name
id
indid = 2
root
Non-Leaf
Level
Aaron
...
Jose
Aaron
Jose
SELECT
lastname, firstname,
phone
Deanna
Nina
FROM …
member
…
WHERE firstname = 'Mike'
Aaron
Adam
Amie
…
Con
Barr
Baldwin
…
Daum
Hall
Hampton
…
Jose
Judy
Mike
…
Leaf Level
Lugo
(Clustered
Kaethler
Key Value)
Nash
…
Barr
Kim
Nagata
O’Melia
以
Last Name
Clustered
Index
為鍵值的
On Last Name
叢集索引
Barr
Cox
Daum
…
Deanna
Don
Doug
…
Adam
Arlette
Deanna
…
…
…
…
…
Kim
Kobara
LaBrie
…
Shane
Linda
Ryan
…
…
…
…
…
Nagata
Nash
Nixon
…
Susanne
Mike
Toby
…
…
…
…
…
設定與維護索引的建議(一)




索引利於查詢，對於新增、修改、刪除則要很精準
地建立
小心選擇叢集索引，因為 SQL Server 2000的預
設是最好要有叢集索引，所以當你以 SQL 語法建
置主鍵時(PK)，預設是同時建立叢集索引。可以
在宣告主鍵時使用 NONCLUSTERED 關鍵字，以
保留叢集索引給更好的用途。
非叢集索引應該具備資料密度低，也就是下
WHERE 條件時具有高選擇性(SELECTIVITY)
若查詢的欄位少，且長度短。建立複合索引來涵蓋
查詢所有的欄位，如此可以不必讀取實體資料頁。
設定與維護索引的建議(二)





設定 Fillfactor 以減少在交易時的 split 動作
定期刪掉並重建索引、更新索引，或是至少自動維
護索引的統計值
考慮刪掉一些為平常不執行的動作建立的索引，如
季報表、年報表。但在要建立報表時再重新建立索
引。以減少日常資料庫進行交易時的維護動作。
若是以查詢為主的資料庫可以 DBCC
SHOWCONTIG 來檢視索引的不連續狀況
若是 WHERE 子句包含 AND 的查詢，可以考慮建
立複合索引，包含所有子句內的欄位，查詢利用該
索引就可以判讀記錄符合與否
設定與維護索引的建議(三)






若是 WHERE 子句包含 OR 的查詢，最好是所有子
句內的欄位都有索引，否則 SQL Server 2000 會
以 Table Scan 來完成查詢。
對於 WHERE 條件是否定的查詢，如 <>‘胡xx’、
NOT( Salary > 30000) 等等 (以 % 開頭 Like 也是
一樣)，索引是沒有用的。必須要使用 Table Scan
利用 Index Analysis 來選擇為某句查詢語法建立
索引
利用 Index Tuning Wizard 來了解最常執行的查詢
，與建議該建立的索引
對於常查詢但不常改變的 View 建立索引
了解 sysindexes 資料表，以了解索引與統計的概
況
Sysindexes 部分欄位架構
欄位名稱
id
first
資料型別
int
binary(6)
說明
資料表的ID (若 indid = 0 或 255). 否則, 是索引所屬的資料表的 ID
對第一或是 root 頁的指標
indid
smallint
索引的 ID:
1 = 叢集索引、>1 = 非叢集索引、255 = 有 text 或 image 的資料表的
進入點
root
binary(6)
對於 indid >= 1 和 < 255, root 是到 root 頁的指標。 若 indid = 0 或
indid = 255, root 最後一頁的指標
dpages
int
對於 indid = 0 或 indid = 1, dpages 是使用的資料頁的量. 若
indid=255, 本欄設為 0。 若是其他的值本欄是索引頁所使用的量
int
對於 indid = 0 或 indid = 1, 本欄是資料頁加索引頁所有保留的量。若
indid = 255, 本欄是 text 或 image 資料所保留的頁數。 若是其他的值
，本欄是索引頁所保留的量
used
int
對於 indid = 0 或 indid = 1,本欄是資料頁加索引頁所有已經使用的量
。若 indid = 255,本欄是 text 或 image 資料所已經使用的頁數。 若是
其他的值，本欄是索引頁所已經使用的量
rowcnt
bigint
對於 indid = 0 或 indid = 1本欄是 Data-level 的記錄筆數 。若
indid=255，本欄設為 0
xmaxlen
smallint
一筆記錄最大的大小
maxirow
smallint
statblob
image
reserved
非葉層集索引頁中最大一筆記錄的大小
統計值組成的大型二進位資料
10 萬筆記錄 ﹔Pentium iii 750 256MRAM
select top 10 * from testtable order by col1
desc



不透過索引 ~ 1.6 sec
透過 Nonclustered Index ~0.14 sec


Indexed View
Clustered Index 和 Nonclustered Index
的比較
交易與查詢調校

查詢最佳化程式的執行步驟

Join 的運作方式

解讀圖形化執行計劃的意義

解讀統計資料的內容

交易與查詢調校建議
查詢最佳化程式的執行步驟
Transact-SQL
解析：檢查語法正確性，並將語法
拆解成 relational engine 能理解的小
單元。輸出 parsed query tree
標準化：將各種同目的的
相似語法取得最佳的語法，去除多餘
的部分。輸出 standardized query tree
最佳化：在多種可以執行的
方式中選一個成本最低，傳回
最快的方式
編譯
資料庫資料存取
結果集
Join 的運作方式

Nested Loop Join




Merge Join



從外層的資料表取出一筆記錄
使用這個記錄掃描內層的資料表
再回到外層的資料表，重複上述的步驟
使用兩個資料表用來 Join 的欄位既有的索引
兩邊的資料表以游標由小到大比較，一邊移動到
比另一個資料表大時，換移另一個資料表
Hash Join



處理大量、未排序、無索引的資料
以資料少的資料表的 Join 欄位建立 Hash 值
對應的資料表計算 Join 欄位的 Hash ，再與前
一個資料表做比對
解讀圖形化執行計劃的意義
查詢的執行計劃
執行步驟的順序
由右到左、由上到下
Member.corp_no
Cost 9%
SELECT
Cost: 0%
Bookmark Lookup
Cost: 8%
Hash Match Root…
Cost 28%
Index Seek
Scanning a particular range of rows from a
non-clustered index.
Physical operation:
Logical operation:
Rowcount:
Count
Row
Estimated
Estimatedrow
rowsizes:
size
I/O
cost:
I/O Cost
CPUcost:
Cost
CPU
Number
ofexecutes:
executes
Number of
Cost
Cost:
Subtree
Subtreecost:
Cost
Index Seek
Index Seek
414
24
0.00706
0.000605
1.0
0.007675(6%)
0.00767
Argument:
OBJECT: ([credit].[dbo].[member].[fname]),
SEEK: ([member],[firstname] >=‘Rb’ AND
[member],[firstname] <‘T’) ORDERED
Filter
Cost: 0%
Member.fname
Cost: 10%
這個操作將
傳回多少筆
預估傳回記錄
預估這個動作
記錄
預估這個動作
的大小
所花費的 I/O
在本查詢中這個
所花費的
CPU
這個動作的總成本
成本
動作會被執行
成本
這個動作再加上
以及暫總查詢的成
的次數
匯集在該點之前
本的百分率
子樹總的成本

解讀 Query
解讀統計資料的內容
透過設定
SET <下列內容> ON
STATISTICS
TIME
STATISTICS
PROFILE
STATISTICS IO
輸出範例
CPU time
CPU 計算
所花時間
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
Elapsed time
總時間
SHOWPLAN_ALL
Rows Executes StmtText
StmtId…
的結果在加上
----------------------------------------------Rows
47
1
SELECT * FROM [charge]
16
WHERE (([charge_amt]>=@1)
每一個運算子真
.
實傳回的記錄數
.
Executes
.
運算子被執行的次數
Table 'member'. Scan count 1,
logical reads 23, physical reads 0,
read-ahead reads 0.
交易與查詢調校的建議(一)





儘量不要使用 SELECT *(不指定欄位名稱) 且沒有
WHERE 子句的查詢。
若查詢需要超過四個以上的 Join ，考慮作反正規
化(denormalized)。但若單一 Table 太大，雖然
該 Table 已經符合三階正規，仍可以考慮做分割
。
為參加 Join 的欄位建立索引，考慮為冗長的 Join
欄位建立整數型別的代替鍵，以方便比對，且欄位
簡短。
SQL Server 2000 會自動為主鍵建立索引，但不
會為外鍵建立索引，考慮為常用的外鍵建立索引。
無須針對所有的交易查詢語法調校，但要為高執行
次數的語法或執行時間過長的語法做效能調校。確
定交易的關鍵語法。
交易與查詢調校的建議(二)

考慮使用 Trigger 替代聚合函數(aggregation)如
Sum、Avg 等等

保持整筆記錄及索引鍵值儘量簡短，以提昇一個
Page 內能容納的數量，減低 I/O 的運作。

設計以資料表或欄位為橫軸、交易所使用的SQL
查詢語句為縱軸，內含交叉資料擺放 InsertSelect-Update-Delete 的 ISUD 表，以找尋可能
最熱的資料存取。
應用程式設計的建議(一)

將基本的資料庫運作建立成預存程序(Stored
Procedure)，以 Command 搭配 Parameters 來
重複執行

少開 Connection，開啟的 Connection 儘快利用
Close 方法關閉。

Connection 資源會被使用者的機器透過 ODBC
或 OLE DB Pool 住

儘量不要使用 Recordset(不是不能使用)

使用 COM+/MTS

透過中層來設定安全機制
應用程式設計的建議(二)

可以利用 SET LOCK_TIMEOUT <微秒> 來設定單
一連線的 Lock timeout 時間。以避免過長的 Lock
，但要做錯誤碼 1222 的錯誤處理

在開啟交易(BeginTran)後，不要讓使用者沒有執
行完成(Commit)或是回復(Rollback)交易的動作就
逕行離開交易

交易時間儘量短，在交易的過程中不要有與使用者
互動的動作，避免一個人出去吃午餐，全部的人都
要出去吃午餐

當使用到的資料量非常大，但不是即時性的動作﹔
考慮使用批次作業(如製作年、季、月報表)在離峰
時間執行。
處理 Lock 的指令與執行結果
指令
執行結果
Sp_lock
spid dbid ObjId
IndId Type Resource
------ ------ ---------------- ---- ---------------51
1
775009842 0
TAB
Sp_who
spid ecid status
loginame
hostname
blk dbname cmd
----------------------------------------------------------------------------------------------------------------------------51
0
runnable
sa
BYRONNB2
0 Northwind
SELECT
Sp_who
2
SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch
ProgramName SPID
----- ------------------------------ ----- -------- ----- --------- ---------------- -------- ------ --------------------------- ----------------- ----52 sleeping sa BYRONNB2 . Northwind WAITFOR
110
43 03/07 14:14:51
Query Analyzer 52
53 sleeping sa BYRONNB2 52 Northwind SELECT
30
0
03/07 14:14:22
Query Analyzer53
kill ：停掉相關的Connection
使用 Enterprise Manager/Management/Current Activity
相關 Table：Syslockinfo、sysprocesses
Mode Status
------------IS
GRANT
系統效能測試(一)

測試系統的資料應儘量保持與上線系統資料的相似
性，因為不同的資料型態會導致不同的查詢最佳化
行為，而造成預估的錯誤。

測試的運作方式與壓力應與上線運作時相似，以檢
視 Lock 的狀況。

在測試時可能需執行如 DBCC
DROPCLEANBUFFERS、DBCC
FREEPROCCACHE 等指令清除快取區的資料，
以求得最差的 I/O 狀況。
系統效能測試(二)

系統效能測試應該在開發過程中持續完成，若等到
系統完成再做效能調校，可能導致大量的系統變更
，與程式碼重寫。

建立效能運作底限，之後的調校以該底限為基準。

測試時一切以時間、數據為準，不要憑感覺判斷快
慢，以及猜測瓶頸在哪裡。
案例研討
案情一

大型主機傳到 PC 上，一個檔案 > 500 MB，沒有
Relation，僅僅憑某個欄位更新另一個欄位。

系統


HP 的 PC 伺服器

PIII

4 顆 SCSI HD

256 MRAM
單一 Update 就需要十幾秒
案例研討
案情二

使用 DAO 存取 SQL Server，以月份加日期
當 Table 名稱，到 1 月 1 號時效率大減
案例研討
案情三

四百多人同時存取的系統，依 DNA 架構建置。
前端使用者下了命令約兩分鐘後才回應。

上線前如何評估機器的規格

前端應用程式需要確認是哪一行程式產生瓶頸

中層系統要監控效能，與壓力測試

DB 需要監控 Lock，Index，瓶頸的運作
結論
Conceptual 的考量

兩方面分析：




從商業邏輯領域來分析瓶頸所在，需要領域的專業人才將
商業邏輯合理化。
從資料庫的實體運作分析，利用各種計數器與工具程式找
出整體實體運作瓶頸(從使用者端到伺服器端，包含網路)
考量資料庫實體設計的差異，例如系統整體運作是偏
向查詢還是偏向交易，若同時具備，考慮將系統切成
兩個伺服器的運作。
某些參考到大量資料但重複的運算，如薪資計算要配
合勞、健、團保、考勤、福利貸款、考績、紅利等等
，可以考慮將資料先一次下載到某台電腦，在該台電
腦上做所有的計算，以便不影響其他作業。
結論
監控的考量

考量尖峰時間的效能需求，而非平均值。

考量整體系統回應時間，尋求瓶頸所在。

在整個開發週期中要時時對 prototype 作效能測
試，而非系統完成才開始做。

以 SQL Server Profiler 及效能監視器監控。

小心觀察並解決鎖定與死結(dead lock)，通常使
用者抱怨系統掛掉，並不是真的掛掉，而是存在太
多 Lock。
結論
程式撰寫以及其他的考量

建立有用的索引 (不要用找尋灰姑娘的方式)

小心使用游標 (Cursor)

使用預存程序 (stored procedure)

減少網路使用，避免商業邏輯的運作需要多次的網
路來回往返呼叫，以及大量的資料在網路上傳輸

交易的嚴謹程度 (isolation level) 及資料一致性與
同時上線的處理量是互斥的

執行週期性的資料庫維護

選擇適當的硬體
更多的資源

SQL Server 產品網址 :


新聞群組





msnews.microsoft.com 之下的
microsoft.public.sqlserver
相關書籍


http://msdn.microsoft.com/sqlserver/Default.asp
完全剖析 SQL Server 7.0(Inside SQL Server 7.0) -Microsoft Press
SQL Server 7.0 Unleashed -- SAMs
SQL 2000 線上叢書
Microsoft TechNet
http://www.uuu.com.tw