SQL Server DBA 必須了解的性能調優要點

這篇文章概述了SQL Server性能調優中的各種選項和工具。性能調優是一項疊代前行且永無止境的一項工作。典型的性能調優方法有 執行個體級别 資料庫級别 應用程式級别

在建立伊始就對SQL Server進行優化是性能調優的首要工作。要建立一個高性能的資料庫應用程式，你必須先對資料庫有一個合适的設計，并且考慮以下步驟 優化業務規則 優化邏輯和實體設計 對應用程式進行優化 對資料庫伺服器進行調優 對相應平台進行優化

應用優化 - 確定你的應用的效率平衡。 關系型資料庫管理系統特點 例子： 資料庫查詢前進行計算和統計。 支援模式對象。 例子：在進行資料聚合和計算時選擇合适的索引。 優化資料庫操作 例子：查找最消耗資源的語句。 利用系統工具欄的統計工具，例如解析，CPU時間和實體讀取。為了減少查詢對系統資源的占用，你可以使用各種途徑，例如建立合适的索引，或者為幫助查詢優化器選擇最佳執行計劃而添加查詢統計。增加提示以取代預設的查詢優化器設定。另外，優化SQL通路路徑也是重要的一步。你需要逐漸優化SQL通路路徑以擷取你的資料。如果查詢優化器沒有選擇最佳執行計劃，你可以通過查詢提示或者查詢規劃強制改變執行計劃。

SQL Server的查詢優化器是基于成本的優化器，它選擇的執行計劃是基于估計過的執行成本。依據估算的行的數目和經過查詢得出的資料量，不同的操作将會有有不同的估算執行成本。如果實際行數遠遠大于執行計劃估算的的行數，這是優化器名額出現了問題。可以提供更多的資訊給優化器用于解決這個問題。可靠的資料分布資訊是非常重要的。SQL Server會依據一些資料限制自動更新統計資訊。

執行個體優化 - 優化資源在發現瓶頸環節當中同樣重要。例如：記憶體配置設定和記憶體利用必須實作平衡。通過不同的檔案通路需求和不同的裝置讀寫的分發，分開不同的檔案以實作讀\寫優化。降低或者消除鎖，鎖存器，塊頭和隊列的沖突。其中一個選擇就是資料壓縮。資料壓縮可以通過減少讀\寫記憶體對象的次數，進而提高效率。

SQL Server 性能優化工具 圖形化實時監視工具 - 可以檢視資料庫上的任何操作的記錄，包括讀\寫統計，CPU使用統計，索引和表的掃描，執行計劃和其他統計資訊，以便我們優化SQL 和 T-SQL 分布式回放 - 分布式回放是一個包含在SQL Server 2012産品中的全新工具，我們可以通過它進行可擴充性測試或者估量硬體或者作業系統更新對資料庫的影響。 性能資料收集器 - 它是完全整合于資料庫系統的工具，通過它，我們可以在一台或多台SQL 伺服器上收集大量的性能名額資訊。同時，亦可以把資料存儲于中心資料倉庫。 資料庫引擎調整顧問 - 這是一個資料庫實體設計的工具，通過它可以幫我們優化索引和分區。 性能監測器 - windows工具，一個在硬體，作業系統，資料庫和應用層面的計數器,以監測性能資料。  使用資料壓縮器 - 這個工具向導提供實作行壓縮和頁壓縮的選項。并且通過工具向導或腳本計算資料壓縮的效率。

打開資料庫表：訂單明細表, 它有 1.2 萬行。如果你看 Data 和 Index space ，它大約有 15MB。通過壓縮我們可以降低其大小。 同下圖，打開壓縮向導 你可以使用壓縮向導選擇壓縮等級 選擇壓縮類型頁并點選“計算”，它将顯示現有空間和壓縮所需空間。你也可以用下面的SQL語句壓縮表。 壓縮完畢後表的細節如下：

使用分析器捕捉 SQL Server 中的 SQL事件 在 「SQL Server Management studio」 的 「Tools」 選項中打開 SQL 分析器。 然後選擇分析模闆及儲存軌迹的檔案。 通過操作「事件選擇器」指定事件。例如，為了檢視查詢執行計劃的執行，在清單中選擇 Show Plan XML 事件并點選「運作」 你能看到分析結果中的事件 打開 SQL 分析器時，你可以設定不同的過濾項，在追蹤「追蹤屬性對話框」中點選過濾按鈕并指定條件。同下圖： 同樣，你可以指定或建立包含 SQL 語句的追蹤定義檔案，同下圖所示，你可以選擇能在不同伺服器運作的版本。

T-SQL Hints 在查詢中強制使用 HASH 連接配接，語句以及執行計劃的示例如下 你将注意到，執行計劃使用合并連接配接。通過強制 Hash 連接配接執行上面的語句将獲得更好的性能。 使用 OPTIMIZE FOR 作為 T-SQL hint，示例如下 執行語句時移除鎖定同樣能夠改進執行計劃。 當表擁有的行非常多時，你也可以通過表級别的鎖定來代替行級别的鎖定。