SQL Server 性能優化之——系統化方法提高性能

閱讀導航

<a href="http://www.cnblogs.com/BoyceYang/archive/2013/06/15/3138142.html#First">1. 概述</a>

<a href="http://www.cnblogs.com/BoyceYang/archive/2013/06/15/3138142.html#Second">2. 規範邏輯資料庫設計</a>

<a href="http://www.cnblogs.com/BoyceYang/archive/2013/06/15/3138142.html#Third">3. 使用高效索引設計</a>

<a href="http://www.cnblogs.com/BoyceYang/archive/2013/06/15/3138142.html#Fourth">4. 使用高效的查詢設計</a>

<a href="http://www.cnblogs.com/BoyceYang/archive/2013/06/15/3138142.html#Fifth">5. 使用技術分析低性能</a>

<a href="http://www.cnblogs.com/BoyceYang/archive/2013/06/15/3138142.html#Sixth">6. 總結</a>

在比較大的範圍内找出能夠大幅提高性能的區域，并且專注于分析這個區域，這是最有效的優化SQL Server性能的方式。否則，大量的時間和精力可能被浪費在不能提高很大性能的區域。在這裡并沒有讨論關于多使用者并發所帶來的性能問題。

能獲得最大性能提高的區域一般是：邏輯資料庫設計，索引設計，查詢設計。然而，最大的性能問題經常由于缺乏這些方面研究的原因造成。如果性能是被列為一個需要關注的問題，聰明的做法是首先專注于這些方面， 因為性能的大幅提高經常是用相對較小的時間精力完成。

下面開始進入正題。

合理規範性的邏輯資料庫設計可以産生最佳性能。大量的窄表是标準資料庫的特性。少量的寬表是非标準資料的特性。高度标準資料庫通常關聯着複雜的表的 聯合查詢，這個可能損害資料庫的性能。不管怎麼樣，SQL Server優化在快速查詢、高效聯接、可用有效索引方面是非常有效的，下面是規範化的好處：

如果是窄表，應該加快排序和建立索引

如果是寬表，最好使用聚集索引

索引往往是越窄的表，越應該精确

更好的利用段去控制表的實體空間

每個表的索引越少，對提高UPDATE操作的性能越有幫助

越少的NULLs列，越少的備援資料，越能增加資料庫的緊湊性

對于SQL Server，标準化将有助于提升而不是損害性能。随着标準化的提高，是以需要一定數量并且複雜的表連接配接來檢索資料。隻要标準化不會導緻很多查詢出現超過四個表的連接配接，就應進行标準化程序。

如果邏輯資料庫設計已經固定，并且不可能進行整體重新設計，而且通過研究表明一個大表存在性能瓶頸，在這樣的情況下，可以有選擇性的對這個大表進行 标準化。如果過存儲過程進行通路資料，那麼架構的改變不會影響應用程式。如果不是這樣，可以通過建立視圖來隐藏這種改變，因為視圖可以産生單個表的錯覺。

不像很多非關系系統，不把關系索引考慮作為邏輯資料庫設計的一部分。索引能被删除、添加和更新，除了影響性能以外，不會影響資料庫架構或者應用程式 設計。實作良好的SQL Server性能，高效索引設計是非常重要的。由于這些原因，不要猶豫展示不同索引帶來的性能改變吧。

大多數情況下，優化器将可靠地選擇最高效的索引。所有的政策應該提供良好的索引優化的選擇，相信這是正确的決定。這可以在多種情況下，減少分析時間并且能提供良好的性能。

接下來介紹索引。檢查SQL查詢的WHERE子句，因為這個是優化的主要焦點。在WHERE子句中列出的列都有可能成為索引的備選。假如有太多的語句需要檢查，挑選有代表性的一組，或者僅僅是速度緩慢的那組。

最好使用窄索引。窄索引比混合索引和複合索引更加高效。窄索引每頁行越多，索引級别應該越低，這樣才能提高性能。SQL Server優化隻是維護統計資料在複合索引最重要的列上。是以，如果複合索引的第一列可選擇性很差，那麼就不優化這個索引。

優化器可以快速、高效的分析成百上千的索引和表連接配接的可能性。有更多的窄索引提供給優化器，優化器就會有更多可能的選擇，這對性能很有幫助。有較少的寬索引、複合索引提供給優化程器，優化器隻有很少選擇的可能性，這對性能會有影響。

索引數目太多性能可能會降低，因為涉及到更新這些索引的開銷。然而，大量的面向更新操作需要更多的讀操作，而不是寫操作。假如，嘗試新索引時提高了性能，那就不要猶豫，使用這個是以吧。

使用聚集索引。适當的使用聚集索引可以極大的提升性能。甚至聚集索引可以使UPDATE和DELETE操作提速，因為這些操作需要很多讀操作。可能 每個表隻有單一的聚集索引，是以，要靈活地利用這個索引。傳回行數的查詢或者涉及一個範圍值的查詢都是一個可能被聚集索引提高性能的候選。

例子：

通過限制，上面提到的NAME和MEMBER_NO列，對于非聚集索引可能不是一個适合的候選。盡量在傳回很少行資料的列上使用非聚集索引。

檢查列資料的唯一性。這樣将幫助決定，什麼樣的列作為聚集索引、非聚集索引、無需索引的備選。

查詢語句檢查資料的唯一性，例子：

這個語句将傳回一個列中不重複值的數量。在表中比較這個數量和總的行數。在一個一萬行的表中，5000個不重複值的列對于非聚集索引可能是一個很好 的備選，20個不重複值的列可能最适合聚集索引，3個不重複值的列根本就不需要使用索引。這些僅僅是個例子，不是一成不變的規則。記住把索引建立在WHERE查詢子句列出的每一個列上。

在索引選擇時，查詢語句傳回行數也是一個重要的因素。優化器會考慮非聚集索引花費在每個傳回行至少一頁I/O的成本。以這樣的速度，并不需要很長的時間就可以變得更高效的掃描整個表。理性對待結果集，要麼限制結果集的大小，要麼使用聚集索引定位巨大結果集。

例如：

大型結果集

IN和OR語句

高度非唯一WHERE子句

!=（不等于）

某些列函數，比如SUM

WHERE子句中的表達式或資料轉換

WHERE子句的局部變量

有些因素可能需要使用這些查詢語句結構。如果優化器可以限制結果集，然後再應用資源密集型的查詢，那麼他們的影響将會減少。

例如:

在第一個例子中，SUM操作使用索引并不能使其加速。每行都需要被讀和求和。設想在ZIP列有一個索引，優化器将可能使用這個來初始限制結果集，然後再應用SUM函數。這可能會更快。

在第二個例子中，局部變量直到運作時才被指派。然而優化器無法拖延到運作時才選擇通路計劃，必須在編譯時進行選擇。然而，在編譯期間，當生成通路計 劃時，@VAR的值還不能确定，是以不能使用輸入的@VAR作為索引選擇。可以使用AND子句對結果集進行限制。使用存儲過程是一個可選技術，這樣可以傳 遞參數，将參數指派給存儲過程中@VAR值。

大多數RDBMSs的大型結果集是很耗費性能。可以嘗試不傳回大型結果集到用戶端作為最終資料選擇。允許資料庫背景執行預定函數，并限定結果集的大小，這種做法效率很高。

首先分離查詢，或者分離比較慢的查詢。當有少數SQL查詢速度慢，經常表現為整個應用程式速度慢。對能夠顯示生成SQL的工具，使用這個工具的診斷或調試模式記錄生成的SQL。使用嵌入式SQL工具會更加簡單。分離速度慢的查詢之前，先做一下下面的步驟：

單獨運作疑似速度慢的語句，使用工具（例如ISQL、SAF）驗證明際上是不是很慢。

使用SET STATISTICS IO ON，檢查語句的I/O消耗和已選擇的通路計劃。優化器的目的是最小的I/O。記錄邏輯I/O。以這個為基準測量改進成果

如果查詢涉及視圖或者存儲過程，從中提取這些語句并單獨運作。當嘗試使用不同索引時，通路計劃是可以改變。

有些表可以生成I/O作為觸發器運作，這時要注意可能和這些表有關系的觸發器和視圖。

檢查速度慢的語句表的索引。利用之前列出的技術檢查是否有更好的索引，如果有必要就修改。

改變索引後重新運作查詢，并觀察I/O和通路計劃的改變。

改進工作完成，運作主程式看看所有的性能是不是有所提升。

檢查程式的I/O或CPU限制的行為。通常這個對确定查詢語句是否在I/O或CPU臨界狀态很有用。我們要花費精力在提高真正的性能瓶頸上，例如， 如果一個查詢是CPU臨界狀态，就算增加更多的記憶體給SQL Server也太可能有性能的提高，當然更多的記憶體還是能提高緩存命中率。下面的步驟是檢查SQL Server的I/O和CPU臨界狀态：

使用OS/2 CPU監控程式。

當運作查詢時，如果CPU使用率保持很高（&gt;70%），這表明是CPU臨界狀态。

當運作查詢時，如果CPU使用率保持很低（&lt;50%），這表明也是CPU臨界狀态。

使用STATISTICS IO比較CPU使用率資訊