SQL Server 資料庫最佳化方法 (一)

 

　　我經常碰到有人會問一些資料庫最佳化方面的問題，我覺得這是一最基本技能要求，特別是使用sql server 2005，對於sql server的最佳化，使用的專職dba或兼職dba們工作更輕鬆，效率更高了，但是還是有很多新人可能不大瞭解，我在這兒寫一下最基本的一些介紹。

　　一、首先介紹一下sql server 2005中最佳化資料時使用到的兩個工具

　　1.第一個工具sql server profile，這個工具在sql server 2000中也有，公司在使用sql 2000資料庫時，我經常使用這個工具，來捕捉那些有效能問題的sql語句。

　　sql server profiler 是用於伺服器捕獲sql server 2005事件的工具。事件可以儲存在一個追蹤檔案中，可在以後對該檔案進行分析，也可以在試圖診斷某個問題時，用它來重播某一系列的步驟。

　　sql server profiler 可用於以下活動：

　　1)逐步分析有問題的查詢以找到出現問題的原因。

　　2)尋找並診斷運行慢的查詢

　　3)捕獲導致某個問題的一系列T-SQL語句，然後用所儲存的跟蹤在某台測試伺服器上複製此問題，接著在測試伺服器上診斷問題。

　　4)監視SQL SERVER的效能以最佳化工作負載

　　5)使效能計數器與診斷問題關聯

　　使用 SQL Server Profiler時，您可以按“期間”、CPU、“讀”或“寫”資料列將跟蹤或追蹤檔案分組來排除資料故障。例如，您可以對效能差的查詢或邏輯讀取運算元特別高的查詢進行資料故障排除。

　　通過將跟蹤儲存至表和使用 Transact-SQL 查詢事件數目據，可以找到其他資訊。

　　工具介面

　　

2. 第二個工具是sql server 2005中新增加的工具——Database Engine Tuning Advisor

    

　　Database Engine Tuning Advisor用於分析在一個或多個資料庫中啟動並執行工作負載的效能效果。分析資料庫的工作負載效果後，Database Engine Tuning Advisor會提供在 Microsoft SQL Server 資料庫中添加、刪除或修改實體設計結構的建議。這些物理效能結構包括叢集索引、非叢集索引、索引檢視表和分區。實現這些結構之後，Database Engine Tuning Advisor使查詢處理器能夠用最短的時間執行工作負載任務。

　　資料庫管理員可以使用Database Engine Tuning Advisor進行探索性分析。探索性分析需要綜合使用手動最佳化和工具輔助最佳化。若要使用Database Engine Tuning Advisor進行探索性分析，需使用使用者指定的配置功能。使用使用者指定的配置功能可以指定最佳化現有和假設的實體設計結構(如索引、索引檢視表和分區)的配置。指定假設結構的好處就是，可以在不用首先實現這些結構的情況下評估它們對資料庫的影響。

　　儘管Database Engine Tuning Advisor圖形化使用者介面 (GUI) 和 dta 命令列工具 + 生產力都支援探索性分析，但是 dta 的靈活性更大，因為它可以使用 XML 輸入檔案。XML 輸入檔案使用Database Engine Tuning Advisor XML 結構描述，這是一種發行的架構，可以通過訪問Database Engine Tuning Advisor架構進行下載。

　　探索性分析的兩種模式

　　可以使用Database Engine Tuning Advisor以下列兩種模式之一執行探索性分析：

　　1) 評估模式

　　在評估模式中，Database Engine Tuning Advisor將相同工作負載下當前配置的成本 (C) 和使用者指定的配置的成本 (U) 進行比較。因為 C 由資料庫中當前存在的實體設計結構組成，所以 C 始終是實際配置。相比較而言，U 是由實際和假設的實體設計結構組成的配置。如果Database Engine Tuning Advisor報告 U 的成本低於 C 的成本，則 U 的實體設計效能可能優於 C。

　　例如，對於下列情況，評估模式是有用的：

　　資料庫管理員要確定向表中添加非叢集索引對效能的影響。

　　資料庫管理員剛剛完成了使用Database Engine Tuning Advisor最佳化資料庫並接受了建議 (R)。查看 R 後，管理員可能會通過修改 R 對其進行微調。

　　例如，

　　資料庫管理員想要添加兩個非叢集索引並刪除 R 中的一個非叢集索引。修改 R 後，該管理員將修改的 R 作為輸入發送給Database Engine Tuning Advisor，並再次最佳化以衡量修改後的 R 對效能的影響。

　　2) 最佳化模式

　　在最佳化模式中，資料庫管理員已經知道應該對資料庫實體設計的一部分進行修改，但是希望Database Engine Tuning Advisor能夠為其餘配置提供最佳實體設計結構方面的建議。

　　例如，最佳化模式在以下情況下非常有用：

　　資料庫管理員瞭解由於事實資料表過大，因此必須對其進行分區。管理員必須選擇是按月還是按季度分區。可以使用其中任意一種方式對錶進行分區，但管理員希望選擇在給定的工作負載下能提供最佳效能的分區方法。若要確定最佳分區方法，管理員可以使用Database Engine Tuning Advisor兩次化工作負載。

　　首先，管理員通過使用者指定的配置和按月假設分區的表來最佳化工作負載。

　　然後，使用按季度假設分區的表來最佳化工作負載。

　　使用兩種假設配置最佳化工作負載後，管理員可以通過比較提高的百分比來確定能提供最佳效能的分區方法。

　　例如：

　　Orders 表必須包含 ship_date 列的叢集索引。資料庫管理員想要確定 Orders 表的一組最佳非叢集索引。通過指定使用者指定的配置(該配置包含 Orders 表中 ship_date 列的叢集索引)，資料庫管理員可以部分修改物理資料庫設計。然後可以在最佳化模式下使用Database Engine Tuning Advisor確定使用者指定的配置對效能的影響。

　　Database Engine Tuning Advisor未最佳化事件的最常見原因包括：

　　1)工作負載引用了使用者未選擇最佳化的表。

　　2)工作負載引用的表過小，例如包含的資料頁少於 10 頁的表。

　　3)Database Engine Tuning Advisor無法在指定時間範圍內最佳化工作負載。

　　說明：工作負載是Database Engine Tuning Advisor的分析對象，它由要最佳化的一個或多個資料庫執行的一組T-SQL語句構成。