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

Report
資料庫快跑!
集英信誠資深顧問 胡百敬
集英信誠資深顧問 許致學
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]
[email protected]
包含:效能調校、整合、整併、升級…等。
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.

similar documents