SQL Server 2008效能監控和效能優化

**SQL Server 2008效能監控和效能優化

performance analyzing, perf tuning
《SQL Server 2008 Database Monitoring and Performance》課程筆記

【單騎】整理，轉載請標明來源：http://liaohairun.blog.163.com

參考資料：
更多資訊可以通過以下入口獲得：
http://technet.microsoft.com/zh-cn/library/ms181091(SQL.100).aspx
http://technet.microsoft.com/zh-cn/library/ms174215(SQL.100).aspx
http://technet.microsoft.com/zh-cn/library/bb895232.aspx

■本課程的目的：基本瞭解微軟對於SQL SERVER的效能調優的工具，在談論相關議題時能夠有大概念、在需要實際運用相關技巧時能迅速上手。

■SQL Server 提供下列工具來監視 SQL Server 的元件：

SQL 跟蹤
SQL Server Profiler ——本課程的重點
Database Engine Tuning Advisor (資料庫引擎優化顧問) ——本課程的重點
Resource Governor
SQL Server Data Collector
SQL Server Management Studio 活動監視器
SQL Server Management Studio 圖形顯示計劃
儲存過程
資料庫控制檯命令 (DBCC)
內建函式
跟蹤標誌
Perfmon -- Windows系統工具

■SQL Server Profiler的定位是一個tracer: 跟蹤者。它對SQL Server中的活動進行跟蹤，從而方便找出伺服器中發生的事情，以便分析哪些是需要關注甚至調整的。
可以對profiler定義“收集有關特定事件的資料”或“排除不適合您的情況的事件”。
例如對於儲存vendor，可能關注空間調整相關的事件：
跟蹤模板屬性-> 事件選擇-> 顯示所有事件-> Database-> "Data File Auto Grow"

■選擇不同的模板，通常TSQL_REPLAY較強大；然後在“事件選擇”中選擇想要跟蹤的event；可以用“列篩選器”更詳細地定義跟蹤物件。

■跟蹤的結果可以儲存為檔案或資料庫中的表。

■可以將通過 SQL Server Profiler捕獲的跟蹤重新載入到 SQL Server Profiler中進行檢視和分析；
也可以用其它工具（例如前面提到的資料庫引擎優化顧問）分析Profiler捕獲的結果。

■重播跟蹤
重播就是儲存跟蹤並在以後對其重播的功能。此功能使您可以再現跟蹤中捕獲的活動。在建立或編輯跟蹤時，可以儲存跟蹤供以後重播。
"replaying trace"是可以在不同的資料庫中進行的。

■資料庫引擎優化顧問（Database Engine Tuning Advisor, DTA）

SQL2005引入，從原來的索引優化嚮導（Index Tuning Wizard）演化而來的一個工具。
藉助 MicrosoftSQL Server 資料庫引擎優化顧問，不必精通資料庫結構或深諳 MicrosoftSQL Server，即可選擇和建立索引、索引檢視和分割槽的最佳集合。
資料庫引擎優化顧問分析一個或多個資料庫的工作負荷和物理實現。工作負荷是對要優化的一個或多個資料庫執行的一組 Transact-SQL 語句。在優化資料庫時，資料庫引擎優化顧問將使用跟蹤檔案、跟蹤表或 Transact-SQL 指令碼作為工作負荷輸入。可以在 SQL Server Management Studio 中使用查詢編輯器建立 Transact-SQL 指令碼工作負荷。可以通過使用 SQL Server Profiler 中的優化模板來建立跟蹤檔案和跟蹤表工作負荷。有關使用 SQL Server Profiler 建立可用作工作負荷的跟蹤的資訊，請參閱SQL Server Profiler 簡介。
對工作負荷進行分析後，資料庫引擎優化顧問會建議您新增、刪除、或修改資料庫中的物理設計結構。此顧問還可針對應收集哪些統計資訊來備份物理設計結構提出建議。物理設計結構包括聚集索引、非聚集索引、索引檢視和分割槽。資料庫引擎優化顧問會推薦一組物理設計結構，以降低工作負荷的開銷（由查詢優化器估計）。

■從演進過程和設計思路看，DTA主要優化索引的利用，類似於Oracle的CBO模式（基於cost的優化模式）的思路。
Profiler和DTA結合起來用，類似於Oracle的Performance Tuning Pack；
而針對個別效率低下的SQL，Oracle 10G中可採用SQL優化器(SQL Tuning Advisor STA)。

■資料庫引擎優化顧問的優化功能 
資料庫引擎優化顧問具備下列功能：

通過使用查詢優化器分析工作負荷中的查詢，推薦資料庫的最佳索引組合。
為工作負荷中引用的資料庫推薦對齊分割槽或非對齊分割槽。
推薦工作負荷中引用的資料庫的索引檢視。
分析所建議的更改將會產生的影響，包括索引的使用，查詢在表之間的分佈，以及查詢在工作負荷中的效能。
推薦為執行一個小型的問題查詢集而對資料庫進行優化的方法。
允許通過指定磁碟空間約束等高階選項對推薦進行自定義。
提供對所給工作負荷的建議執行效果的彙總報告。 
考慮備選方案，即：您以假定配置的形式提供可能的設計結構方案，供資料庫引擎優化顧問進行評估。

 

■資料庫引擎優化顧問通常用GUI啟動；在軟體或指令碼中則可以用dta.exe命令列工具呼叫
dta.exe

■使用D他的步驟：
生成workload檔案或workload表（可以結合SQL Server Profiler生成）；
啟動DTA；
執行分析；
檢查分析結果；
apply分析結果。

■RESOURCE GOVERNOR
alter resource governor ...
用RESOURCE GOVERNOR管理sql server的workload和資源，這些資源是 CPU 和記憶體。

資源調控器是 SQL Server 2008 中的一項新技術，使用該技術可以通過指定傳入請求的資源消耗限制管理 SQL Server 工作負荷和資源。
在資源調控器上下文中，工作負荷是一組大小相似的查詢或請求，可以且應該視為單個實體。這並不是必需的，但是工作負荷的資源使用模式越統一，通過資源調控器可能獲得的益處越多。
資源限制可以實時重新配置，對正在執行的工作負荷影響非常小。

在相同伺服器上存在多個不同工作負荷的環境中，使用資源調控器可以區分這些工作負荷並能根據指定的限制在請求時分配共享資源。

資源調控器僅在 SQL Server 的 Enterprise Edition、Developer Edition 和 Evaluation Edition 中提供。

■SQL Server Resource Governor 類似於Oracle Resource Manager。
但Oracle早在8i時代就部署了該功能；而且Oracle能控制更多的資源（或者說能更小粒度地控制資源），包括並行度和執行時長甚至UNDO等

■為支援resource governor, SQL2008提供了一套相關的動態檢視：
sys.dm_resource_governor_workload_groups
sys.dm_resource_governor_resource_pools
sys.dm_resource_governor_configuration

■resource governor有助於：
監控系統資源的使用情況；
分配資源；
限制對資源的佔用（確保其它應用或回話有資源可用）；
資源利用優先順序的管理；
相關告警。

■SQL Data Collector
效能資料收集器和資料倉儲（Performance Data Collection and Warehouse）。
Data Collection是SQL Server 2008中用於多種資料集採集功能的新元件。它可以一直執行或者基於使用者定義的日程執行。元件採集到的資料會儲存到稱為Management Data Warehouse的關聯式資料庫中。
該元件提供了跨越資料庫伺服器和應用的跨時間段的資料採集功能。和SQL Trace所不同的是，它的資料來源並不侷限於效能方面。它同時提供API來擴充套件采集機制。
開發者通過在Server上配置Data Collection，然後把收集到的資料填充到中心MDW中。當前，該元件的的資料採集API僅支援SQL Server 2005和Server 2008。
使用方法：在SSMS的"Management"目錄中，右鍵"Data Collection"，選擇"Configure Management Data Warehouse"。透過配置嚮導，設定MDW。SSMS中整合了對MDW的報表分析展示功能。

這個工具就是走Oracle StatsPack/AWR/ADDM 的路子。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/90618/viewspace-1080402/，如需轉載，請註明出處，否則將追究法律責任。