Transcript SQL Server 效能調校準則及最佳範例分享

資料庫快跑!
集英信誠資深顧問 胡百敬
集英信誠資深顧問 許致學
2011 SQL Hero 莊國志
大綱
影響資料庫效能的因素
效能問題的迷思與調校指標
效能問題排除方法論 (DETECT)
效能調校參考原則
影響資料庫效能的因素
資料庫
查詢
調校伺服
結構
最佳化
器軟硬體
索引
鎖定
資料來源：SQL Server 2005 Performance Tuning效能調校，悅知文化，胡百敬等
效能調校的迷思
升級硬體=效能問題特效藥?
沒有解決不了的效能問題?
效能調校的指標
回應時間
同時上線人數
每分鐘處理量
CPU使用率
記憶體使用率
磁碟IOPS
效能問題排除方法論 (DETECT)
資料來源：SQL Server 2005 Performance Tuning效能調校，悅知文化，胡百敬等
效能調校參考原則
應用程式
設計
資料庫
設計
伺服器硬
體資源
效能調校參考原則(續)
應用程式
設計
資料庫設
計
伺服器硬
體資源
評估磁碟使用情形
Physical Disk: % Disk Time
評估記憶體使用情形
評估CPU使用情形
Memory: Page/sec
Processor: % Processor Time
Physical Disk: Avg. Disk
Queue Length
Memory: Available Bytes
Physical Disk: % Free Space
System: Processor Queue
SQL Server: Buffer Manager:
Logical Disk: % Free Space
Buffer Cache Hit Ratio
Length
效能調校參考原則(續)
應用程式
設計
資料庫設
計
伺服器硬
體資源
檔案與檔案群組
資料檔與交易記錄檔分別
存放於不同實體磁碟
資料表
分割資料表
索引
叢集索引與非叢集索引
先建立叢集索引再建立非叢
妥善規劃資料庫檔案
初始大小
集索引
資料型態、正規化
索引建立的時機
資料檔個數與CPU
避免前端程式直接存取基
數量相等
礎資料表
建立索引與復原模式的關係
效能調校參考原則(續)
應用程式
設計
資料庫設
計
伺服器硬
體資源
有效的查詢參數
(SARG)
其他注意事項
FirstName = ‘Chuang’、100 < Amount、
CustomerID = 5 AND Amount > 10
避免使用SELECT * FROM Table_Name
避免對資料欄位做運算
謹慎使用Union、 Distinct、Order By
避免使用否定句查詢（NOT、!=、
<>、!>、!<、NOT EXISTS、NOT IN)
UPDATE和DELETE搭配WHERE子句亦須符合
謹慎使用OR
SARG格式
效能調校參考原則-執行計畫重用
效能調校參考原則-執行計畫重用(續)
Ad-hoc
快取
自動參數
Exec
化快取
計畫
快取
Stored
sp_execu
Procedure
tesql
Prepared
效能調校參考原則-規劃tempdb
視需要手動擴大tempdb檔案
設定合理的初始大小
設定合理的自動成長百分比
tempdb資料庫放在快速的磁碟，並與使用者資料庫獨立
建立與CPU個數相同的資料檔個數
效能調校參考原則-交易
使用交易的建議
交易持續時間越短越好
交易期間避免與使用者互動
查詢資料期間盡量避免啟用交易
活用交易隔離等級與鎖定提示
效能調校參考原則-鎖定
常見鎖定的類型
共用(Shared ; S) : SELECT
更新(Update ; U) : 用於轉換至更
新資料前的鎖定
獨佔(Exclusive ; X) : INSERT、
嘗
試
鎖
定
對某項資源已經下的鎖定
IS
S
U
IX
SIX
X
IS
Y
Y
Y
Y
Y
N
S
Y
Y
Y
N
N
N
U
Y
Y
N
N
N
N
IX
Y
N
N
Y
N
N
SIX
Y
N
N
N
N
N
X
N
N
N
N
N
N
UPDATE、DELETE
意圖(Intent) : 意圖共用 (IS)、意
圖更新(IU)、意圖獨佔(IX)、共用
意圖獨佔(SIX)
效能調校參考原則-死結
防止與處理死結
盡量避免或盡快處理被鎖定
存取資源的順序要相同
SET DEADLOCK_PRIORITY LOW
應用程式針對1205的錯誤重新執行
效能調校參考原則-前端應用程式設計
使用者端存取SQL Server的方式
直接執行完整的SQL語法
Dim SelectCmd As String =
"SerialNo,BrokerId,BrokerName,MemberName FROM
Members WHERE
ConferenceDate = @ConferenceDate“
透過sp_executesql並搭配參數
Dim ConferenceDate As New SqlParameter With
{.ParameterName = "@ConferenceDate", .SqlDbType =
SqlDbType.Char, .Size = 8, .Value = …")}
謹慎使用指標
效能調校參考原則-連線集區
SqlClient的Connection Pooling設定
• Connection Lifetime，預設值0
• Connection Reset，預設值True
• Max Pool Size，預設值100
• Min Pool Size，預設值0
• Pooling，預設值True
取用和還回
• 善用Using … End Using
連線集區無法跨機器共用
Summary
影響資料庫效能的因素
效能問題的迷思與調校指標
效能問題排除方法論 (DETECT)
效能調校參考原則
執行計畫重用
規劃tempdb
使用交易的建議
管理鎖定
防止與處理死結
使用者端存取SQL Server的方式
前端應用程式設計
連線集區
介紹
•
•
•
資料庫顧問組
開發架構顧問組
SharePoint平台開發



資深合夥顧問
許致學
胡百敬
陳俊宇
[email protected]
[email protected]
[email protected]
MS-SQL為核心的各種資料庫規劃、設計、管理顧問。
包含：效能調校、整合、整併、升級…等。
MS-SQL資料庫長期維護服務。
資料庫系統開發建議。
介紹
•
•
•
資料庫顧問組
開發架構顧問組
SharePoint平台開發






資深合夥顧問
趙敏翔
曹祖聖
張書源
李智樺
[email protected]
[email protected]
[email protected]
[email protected]
TFS ALM團隊開發全生命周期導入顧問。
應用系統開發架構設計規劃、委外監管、執行方案驗証…等。
系統效能與問題排除，Code Review。
.NET、Java開發技術POC。
私有雲、公有雲設計及架構規劃。
服務導向架構設計。
介紹
•
•
•
資料庫顧問組
開發架構顧問組
SharePoint平台開發
資深合夥顧問
趙敏翔
曹祖聖
李智樺




SharePoint 2010 開發
SharePoint 2007 Upgrade
Notes Migration / Integration
Private Cloud Architect Design
(Private Cloud)
方案A 中型企業資料庫及應用系統健檢維護
•
•
•
•
•
主維護標的：SQL Server Instance x 3
顧問健檢：7 days / 年 (3.5hr x14)
Xecho 資料庫監控軟體標準版
20個技術問題遠端支援
期初及期末報告
1. 年度規劃：期初以2~3次到場顧問檢查系統環境、效能調校、
設定監控環境、並討論制定管理政策、備份還原設定及演練
2. 定期健檢：每月半天主動顧問式健檢
3. 技術諮詢：透過電話、郵件回覆問題諮詢
4. 結案報告：年底提供全年度資料庫維護報告
方案B 小型企業資料庫監控與技術支援方案
•
•
•
Xecho 資料庫監控軟體標準版
20個技術問題遠端支援
初次到場安裝及健檢
效能資訊
可針對單一主機或者所有主機
在某個時間區段間(開始及結束時間)的效能查詢
提供多重條件執行查詢:
 Disk可使用空間(Disk)
 SQL使用CPU(%)(CPU)
 SQL使用記憶體大小(MB)(Memory)
 系統使用記憶體大小(MB)(Memory)
 其他使用CPU(%)(CPU)
效能資訊—報表項目






%CPU：顯示主機在特定時間區間的CPU使用量%曲線。
記憶體使用量：顯示主機在特定時間區間的記憶體使用量曲線。
磁碟I/O使用量：顯示基在特定時間區間的I/O使用量曲線。
SQL Server活動
磁碟使用量
查詢統計紀錄
安全資訊
可針對單一主機或者所有主機
在某個時間區段間(開始及結束時間)的資訊查詢
提供多重條件執行查詢:
 SQL 無法連線
 SQL重新啟動
 SQL登入失敗次數
安全資訊—報表項目
 SQL無法連接
 SQL重新啟動
 SQL登入失敗
服務相關訊息與詢問
http://www.mentortrust.com
[email protected]
客戶連絡窗口
李書賢 SCOTT
[email protected]
0936-393-696
© 2009 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.