[筆記][2010/11/05]資料庫效能提升：SQL Server 2008 R2 效能調校工具實戰

拖到快要月底才弄出來 orz 以下是筆記節錄 :

* 專案的事前規劃是很重要的，可以有效地 cost down 費用，甚至是電力的使用量都可以省
(錯誤的規劃有可能導致無法收拾的結果，一定要注意!!)

* 64Bit 的 Windows Server 要配上 64Bit 的 SQL Server，不然會影響效能
(例如：避免在 64bit windows server 上安裝 32bit sql server ... 就杯具了)



* SQL Server 2008 R2 在 交易處理效能委員會 的 TPC 效能評測獲得相當好的結果
(微軟對此似乎相當自豪XD)

* 要經常注意資料的成長率
(雖然資料庫預設會自動長大，但是也會有來不及長大就掛點的危機，而且，這還很常發生)

* 效能的檢測與評估，可以分為三大部分：硬體資源配置規劃，資料庫設計，應用程式設計
(必須要對症下藥才會有效，不然可以花了錢又製造更多的災難)

* 可使用 ALTER [Table] REBUILD 來重建資料表 (瘦身)
TechNet Ref : ALTER TABLE (Transact-SQL)

* 備份起來之後大量刪除會造成大量的交易記錄(Transaction Log)
MSDN Ref : Managing the Transaction Log
(註：這邊忘了，是要先備份起來避免，還是怎樣 ... 總之每次刪除都會產生紀錄這樣)

* Do not View on View
(檢視表拿出來再做檢視表，這樣的效能一定不好)

* Table 與 Index 的 File Group 可以分開設置
(利用硬體的特性，分開存放在不同實體才有效果，放在同一顆硬碟的不同磁碟區其實並沒有意義)

* 核心處理資料時，只讀取 8k per page (要注意)

* 設計時的資料型態(DataType)規劃很重要
* 設計時的索引(Index)規劃也很重要
(結論：規劃真的很重要!!)

* 64Bit 不會檢查 AWE
TechNet Ref : 使用 AWE

* SQL Server 設定中的選項不要亂勾選
(選項之間有可能互相影響)

* 硬體會影響的部分可分為 CPU / HD / RAM / NETWORK

* File Group / Table / Index 相關

* 2 Tier / 3 Tier / SQL Command / Transaction / Lock 等都有可能影響效能

* Do not over 6 indexes in one table.
(數大不一定美)

* 使用 LIKE 比對字串，會比使用 SubString(0 ~ n) 來的快速
假使你的字串比對都是「從頭開始」，選用 SubString 不見得會變快

* 使用 SET 改變資料會比 Loop Cursor 要來得有效率

* 定期量測效能指標 :
- response time
- concurrent user
- data process per min
- cpu usage
- HD IOPS
- ram usage

* D.E.T.E.C.T. 方法
- Discover the problem
- Explore the conditions
- Track down possible approaches
- Execute the most likely approach
- Check for success
- Tie up loose ends

* 分割 Table 與 Index 可以提升效能

* 使用資料壓縮來縮減 Table 與 Index 的大小
(跟壓縮有關的功能似乎有版本限制)

* 內建效能調校工具 :
- Database Tuning Advisor
- SQL Server Profiler

* SanpShot 會使用到 tempdb

* 設定交易隔離等級

* 利用資料分割鎖定 與 減少磁碟爭用

補充 : tempdb 資料庫的並行增強功能

* 使用資源管理員控制資源的使用量

* 使用資料收集器收集資料，並且分析與產生報表

* 資料壓縮 :
- Row 壓縮 : 適合 Online : 2% ~ 5% CPU 使用量, 20% 壓縮比
- Page 壓縮 : 適合 History : 10% ~ 15% CPU 使用量, 60% ~ 70% 壓縮比
(不只檔案縮小，連記憶體的使用量也會減少，代價是 CPU 使用率)

* 使用 Filtered Indexes (篩選索引) 可以提升索引效率
語法 : CREATE INDEX [IndexName] ON [TableName]([ColumnName]) WHERE TYPE='[TypeName]';

* 向上擴充支援 NUMA, 256 顆 CPU, 2TB RAM, 50 Instance ... 等

* 向外擴充支援 SSAS, Service Broker, Data Dependent Routing, 分割檢視表(可橫切) ... 等

* 分割檢視表中的定序很重要，計算型欄位不可以有索引，可搭配 INSTEAD OF 觸發程序

* 查詢通知可以使用系統內建的預存程序 sp_DispatcherProc

* 查詢通知在 ADO.NET 的相關元件為 : SQLNotifcation, SqlDepandency

* 資料異動擷取(CDC)
- 異動資料擷取的基本概念
- 啟用異動資料擷取
- 異動資料擷取資料表 (Transact-SQL)

* 變更追蹤(CD) ... 此處缺肉 orz

* 點對點交易式複寫有 分散查詢 + 容錯 的特性

* 可擴充共用資料庫(唯讀)

* 使用系統的效能監視器(Performance Counter)來監測伺服器的健康狀況
也可以找出"基準線"，讓將來更容易分辨異常
記錄下來的數值，要注重其相對性，尤其是與之前的紀錄數據做比較
注意：這邊主要是要知道發生了什麼(WHAT)，而不是為何(WHY)或是如何(HOW)

* 重要的效能物件與計數器 : ... (這邊投影片上有，我就不多打了)

* 善用 SQL Trace (server side) 與 SQL Profiler (client side)

* SQL Profiler 的死結圖示可以很直覺的看到死結的事發現場 XD

* SQL Profiler 可以整合效能監視器，同時紀錄系統效能

* Database Engine Tuning Advisor 可以由各種紀錄的來源，來分析出效能調整的建議
(這工具非常強大，不過似乎也有版本的限制 ...)

* 伺服器端的好幫手 - Management Studio
可以產生各種好用的報表，以及即時的活動監視器，用來觀察目前伺服器運行的狀況
更貼心的是，下方直接就將"最近且費時的查詢"的前幾名列出來了，黑名單就在這!!

* 動態管理檢視與動態管理函數
內建許多功能，像是可以找出 TOP 20 MAX CPU Time (in one day) 這樣的資訊

微軟很佛心的有以下這個網頁 :
指令碼中心 ( Script Center )

有興趣的人可以上去找尋自己需要的資源 :)

投影片最後面的案例分享也是相當不錯的唷

* 非同步 mirror : use 2 snapshots on mirror
(Tip : join-in tempdb)

* 正確的 DataType 是很重要的
(當你每筆資料省下了 50 Bytes，當有 4000 萬筆資料時，就省下約 2GB 的空間)

* 可以使用 VS2008 做壓力測試模擬

* 可以使用 Performance Dashboard Report 尋找 Missing Index (重要)
- SQL Server 2005 Performance Dashboard Reports Download

* DataType Matching is important.
(假使兩端資料型態不吻合，有可能影響執行計畫的流程，進而造成效能降低)

* 資料庫正規畫與 JOIN 指令，大量的 JOIN 會直接影響效能
(正規化與反正規化的取捨，要靠經驗與專案需求而定，沒有標準答案)

* 觀察 T-SQL 指令的執行計畫，進而改善查詢的效能

* 使用者自訂函數，在某些情況可以改善效能

===

參考資料：

課程所使用的投影片 - 感謝講師 許致學 無私分享

SQL Server 2008 R2 官方首頁

SQL Server 2008 R2 版本支援的功能 (比較表)

以上，分享得很雜亂無章，還請各位多包涵