INF: 將 Microsoft SQL Server 效能最佳化

文章翻譯 文章翻譯
文章編號: 110352 - 檢視此文章適用的產品。
本文曾發行於 CHT110352
全部展開 | 全部摺疊

在此頁中

結論

為了最有效地最佳化 Microsoft SQL Server 的效能,您必須認明哪些方面的效能可以得到最大程度的增強,並集中在這些方面的分析。否則,您可能花費大量的時間與精力,卻得不到太大的效能改進。

下列資訊中的絕大部份並不是針對因多使用者併行而引起的效能問題。「Maximizing Database Consistency and Concurrency」(「最佳化資料庫的一致性與併行性」) 文件以及其它 Knowledge Base 文章有對此獨立、復雜的主題做探討,前者文件可從 SQL Server 4.2x 版的「Programmer's Reference for C」(「C 的程式員參考指南」) 附錄 E 裡找到。6.0 版本的文件中缺少此主題,但可從 MSDN (Microsoft Developer Network) 光碟裡的相同標題下找到。

本文主旨不在於進行理論性的討論,而是著重探討 Microsoft SQL Server 支援小組幾年現實經驗所總結出的有實際價值的領域。

經驗顯示 SQL Server 效能可從邏輯資料庫設計、索引設計、查詢設計以及應用程式的設計方面獲得最大效益。反之,效能的最大問題亦由這些方面的缺陷引起的。若您關注效能的問題,首先應把重點放在這些方面,因為經常可在投入較少時間的情況下獲得顯著的效能增進。

雖然其它系統層級的效能問題,如記憶體、快取記憶緩衝區、硬體等亦值得去研究,不過經驗顯示,從這些方面獲得的效能增進往往是緩慢遞增的。SQL Server 會自動管理可用的硬體資源,這在很大程度上減少了深入系統層級手動校正的需要 (也因此減少所帶來的好處)。

Microsoft SQL Server 6.0 及此後版本的 Microsoft SQL Server 提供大容量記憶體、對稱性多重處理、並行資料掃瞄、增強功能的最佳化程式及磁碟區等量化等功能,為平台層次的效能改進帶來了新的契機。這樣的效能改進雖大,但畢竟範圍是有限的。最快的電腦也會因為低效的查詢或設計不良的應用程式而陷入癱瘓。因此,儘管 SQL Server 6.0 及此後版本的 Microsoft SQL Server 啟動的效能改進增加了,對資料庫、索引、查詢及應用程式的設計進行最佳化仍是非常重要的。

如果只把探討改進的重點放在伺服器上,很多效能問題是無法得到圓滿解決的。伺服器本質上仍是客戶的 「傀儡」,因為客戶控制著查詢種類的傳送,亦因此控制著取出或解除之鑰。雖然在伺服器端也可做一定的校正,但要圓滿解決效能問題,應當首先承認客戶端在此問題中所扮演的主導角色,並且分析客戶端應用程式的行為。

其他相關資訊

以下是根據經驗總結出的一些能使效能得到顯著增進的建議:

將資料庫的邏輯設計正規化

對資料庫的邏輯設計做合理的正規化將產生最大的效能。數量較多的 「窄」 表格是資料庫正常化的特徵。而數量較少的 「寬」表格是未正規化的資料庫的特徵。極高度規範化的資料庫通常對應復雜的關連結合(join),這將損害效能。但只要有有效率的索引,SQL Server 最佳化程式就能有效地選擇快速、高效率的結合(join)。

正常化的好處包括:
  • 由於表格較窄,可加快排序、索引建立的速度。
  • 由於表格較多,允許更多的叢集索引。
  • 索引通常更窄、更精簡。
  • 每個表格中的索引減少,有助於 UPDATE (更新) 的效能。
  • 空值和重覆資料減少,增加資料庫的精簡度。
  • 減少併行對 DBCC 診斷的影響,因為必要的表格鎖會影響較少的資料。
對於 SQL Server,合理的正規化往往有助於而非有損於效能。隨著正常化程度的提高,取出資料所需的結合的數量與復雜程度也相對提高了。大略的原則是,Microsoft 建議持續進行正常化程序直到許多查詢出現四重或更多的結合(join)為止。

在資料庫的邏輯設計已定型,整體重新設計不可行,而經由分析亦顯示此表格已經有瓶頸的情況下,有可能進行選擇性的正規化。如果資料庫的存取是透過儲存的程序(Stored Procedure)進行的,此架構改變可在不影響應用程式的情況下進行。否則,可透過建立一檢視,使其看似一個表格。

使用有效率的索引設計

與許多非關連性系統不同的是,關連性索引不被當成資料庫邏輯設計的一部分。索引可被刪除、添加或修改,而除了影響效能之外,不會對資料庫的架構或應用程式設計造成任何影響。有效率的索引設計對取得優異的 SQL Server 效能是極為重要的。有鑒於此,您應毫不猶豫地測試不同的索引。

在大多數情況下,最佳化程式會可靠地選擇最有效的索引。索引設計的整體策略應是為最佳化程式提供一組良好的索引選擇,並相信它會做出正確的決定。這將減少分析時間,並在許多不同情況下都能帶來良好的效能。

下列是一些索引設計的建議:
  • 檢查 SQL 查詢的 WHERE 子句,因為這是最佳化程式的中心焦點。

    WHERE 子句中所列的每一資料欄都可能建立索引。如果需要檢查的查詢太多,可選擇一個具代表性的集合,或只檢查速度慢的查詢。如果您的發展工具將 SQL 代碼的輸出透明化,將增加困難度。許多此類工具基於偵錯目的,允許將 SQL 語句記錄成檔案或顯示於頻幕上。您可從工具的供應商得知是否擁有此功能。
  • 使用窄索引

    窄索引往往比多欄位的複合索引更為有效。窄索引每頁有更多的列,以及較少的索引層次,因而提升效能。

    最佳化程式能快速且有效的分析上百、甚至上千個索引和結合機率。擁有更多的窄索引將給最佳化程式提供更多的可能性選擇,這通常對效能的提高會有所幫助。而擁有較少的多欄位的寬索引,提供給最佳化程式的選擇相對減少,這可能對效能會有負面作用。

    通常最好不要採用一個強調完全覆蓋查詢的策略。如果 SELECT 子句中所有欄位都被一個非叢集索引覆蓋,最佳化程式會識出這一點,並提供很好的效能。但是,這通常會造成過寬的索引,並會過度依賴最佳化程式使用此策略的可能性。通常,使用更多的窄索引會給於許多查詢提供更高的效能。

    在達到適當的讀取效能之後,您不應該再增加更多的索引,因為這將加重更新這些索引的負擔。然而,就算是最導向於更新的作業,所需的讀取遠比寫入多。因此,您大可不必猶豫的嘗試添加新的索引;您可以隨時把它去除。
  • 使用叢集索引

    適當使用叢集索引可以極度增進效能。就連 [更新] 和 [刪除] 作業也能因使用叢集索引而加快速度,因為這些作業需要大量的讀取。一個表格只允許有一個叢集索引,因此使用時務必謹慎。傳回很多列的查詢或涉及一個範圍數值的查詢,都適合用叢集索引來加速執行速度。

    範例:
          SELECT * FROM PHONEBOOK
          WHERE LASTNAME='SMITH'
    
          -or-
    
          SELECT * FROM MEMBERTABLE
          WHERE  MEMBER_NO > 5000
           AND MEMBER_NO < 6000
    
    
    與此相對的是,如果這類查詢很普遍的話,上述的 LASTNAME 或 MEMBER_NO 這兩個欄位不太適合用非叢集索引。非叢集索引應盡量用在傳回資列列數不多的欄位上。
  • 檢查欄位的不重複性

    這有助於您決定什麼樣的欄位適合使用叢集索引、非叢集索引,或不使用索引。

    下列是一個檢查欄位不重複性的查詢範例:
          SELECT COUNT (DISTINCT COLNAME)
          FROM TABLENAME
    
    
    這將傳回欄位中不重複值的數量。將此數量與表格中的總列數作一比較。對於一個有 10,000 列的表格,如果有 5,000 個不重複值,此欄位很適合使用非叢集索引。在同一表格中,如果只有 20 個不重複值,則較適合使用叢集索引。而如僅有 3 個不重複值,就不要用索引。這僅是一個範例,並非不可更改的硬性規定。請記得將索引放在查詢裡 WHERE 子句所列出的各欄位上。
  • 檢查索引欄位裡的資料分佈

    如果對一個擁有少數不重複值的欄位建立索引,或在此欄位上執行 [結合] ,往往會導致查詢執行緩慢。這是出於資料和查詢本身的一個基本問題,如不了解此情況就無法得到解決。例如一本電話簿裡的姓氏是根據英文字母順序排列的,但如果城市裡所有的人都姓「Smith」或「Jones」,那麼想很快地找到一個人是不太可能的。除了以上的查詢能夠為欄位的不重複性提供單一數據之外,您還可使用「GROUP BY」查詢來檢視索引關鍵值的資料分佈。這提供了一個更清晰的資料檢視圖,並為最佳化程式在檢視資料時提供了另一個好的方法。

    下列是一個檢查索引鍵資料分佈的範例查詢 (假設 COL1、COL2 兩欄位擁有索引鍵)。
          SELECT COL1, COL2, COUNT(*)
          FROM TABLENAME
          GROUP BY COL1, COL2
    
    
    每一個索引鍵值都將傳回一列,並帶著各個值的實例計數。欲減少傳回的列數,可用 HAVING 子句排除一部分的回傳。例如
          HAVING COUNT(*) > 1 
    
    
    子句將排除所有帶不重複索引鍵的列。

    查詢傳回的列數也是選擇索引的一個重要因素。最佳化程式視一個非叢集索引所傳回的每一列至少要消耗一頁的 I/O (輸入/輸出)。以這種速度,掃瞄整個表格將變得更為有效。這也是限制結果集合的大小,或用叢集索引尋找大量結果的另一個原因。
不要永遠把索引的使用與高效能畫上等號,反過來也是一樣。假如使用索引總能產生最佳效 能,最佳化程式的工作就太簡單了,亦是使用任何可用的索引即可。實際情況是,不當的選用索引讀取會導致很差的效能。因此,最佳化程式的工作是選擇有益于效能的索引讀取,而避免有損于效能的索引讀取。

使用有效率的查詢設計

有些查詢本質上就要占用大量的資源。這與大多數的關聯式資料庫管理系統 (RDBMS) 常有的資料庫和索引根本問題有關,而不是 SQL Server 特有的。它們並非低效率,因為最佳化程式將盡可能最有效地執行查詢。但它們仍會占用大量的資源,而 SQL 以集合為導向的特性可能使它們顯得效率很低。最佳化程式的任何設定都無法消除這些因結構導致而需消耗的大量資源。與較簡單的查詢相比,它們非常耗費資源。盡管 SQL Server 將使用最理想化的存取計畫,但還是受限於一些基本不可變的因素。

例如:
  • 大型結果集合
  • IN、NOT IN 及 OR 查詢
  • 多處重複的 WHERE 子句
  • != (不等於) 比對運算符
  • 某些欄位函數,如 SUM
  • WHERE 子句中的運算式或資料轉換
  • WHERE 子句中的區域變數
  • GROUP BY 或 ORDER BY 的復雜檢視
不同的因素可能使得有必要使用一些此類查詢結構。如果最佳化程式能在運用需要大量資源的查詢部分之前,限制此查詢的結果集合的大小,此查詢結構對效能的影響將減少。以下是一些範例。

需要大量資源的查詢:
   SELECT SUM(SALARY) FROM TABLE

需要較少資源的查詢:
   SELECT SUM(SALARY) FROM TABLE WHERE
   ZIP='98052'

需要大量資源的查詢:
   SELECT * FROM TABLE WHERE
   LNAME=@VAR

需要較少資源的查詢:
   SELECT * FROM TABLE
   WHERE LNAME=@VAR AND ZIP='98052'

在第一個範例中,SUM 運算無法透過索引加速。因為每一列都必須經讀取並累計。假設在 ZIP 欄位上有一索引,最佳化程式將很可能在應用 SUM 之前先用它來限制結果集合。這樣作速度會快得多。

在第二個範例中,只有在執行階段才會分解區域變數。但最佳化程式不能將存取計畫的選擇延至執行階段;它必須在編譯時就進行選擇。但如果在編譯時建立存取計畫,@VAR 值還未知,因而無法用作索引選擇的輸入項。

以上範例中用於改善效能的技術,是利用 AND 子句來限制結果集合。另一替代技術,是使用一儲存的程序(Stored Procedure),再把 @VAR 值以參數形式通過此程序。

有些情況下,最佳方法是使用一組簡單的查詢,利用暫存表格儲存中間結果,這比使用單一復雜查詢好得多。

對於大多數 RDBMS,大型結果集合是很耗資源的。您應該盡量避免把一個大型的結果集合作為最終的資料選擇傳回給客戶。限制結果集合的大小,讓資料庫系統完成本來就屬於它的一些功能,效率會更高。這將減少網路 I/O,並使得應用程式更易於通過慢速遠端通訊連結的部署。隨著應用程式擴展到更多的使用者,還能改善與併行相關的效能。

使用有效率的應用程式設計

應用程式設計在 SQL Server 效能中起著舉足輕重的作用。與其把伺服器看成是支配角色,還不如說客戶是控制實體,而伺服器是客戶端的一個「傀儡」更為準確。在查詢類型、何時提交查詢、如何處理結果等方面,SQL Server 完全受客戶端的支配。這直接對鎖的類型及持續時間、伺服器 I/O 及 CPU 負擔都有很大的影響,亦因此決定了效能的好壞。

正因如此,在應用程式設計階段作出正確的決策是非常重要的。就算使用整套應用程式還是會遇到效能問題 (修改客戶端應用程式似乎是不可能),因為影響效能的基本因素是不會改變的,即是客戶端起著決定性的作用,如果不對客戶進行修改,很多效能問題將無法解決。

一個設計良好的應用程式,可以讓 SQL Server 支援數以千計的併行使用者。而如果應用程式設計不良,即使是最強大的伺服器平台在只有幾個使用者的情況下也會陷入癱瘓。

在設計客戶端應用程式時採用以下建議,將會帶來良好的 SQL Server 效能。
  • 使用小型結果集合。為客戶瀏覽而取出不必要的大型結果集合 (例如上千列),將會增加 CPU 和網路 I/O 負擔,使應用程式不能十分有效的用於遠端傳送,同時還限制了多使用者擴展性。因此您設計的應用程式最好提示使用者輸入足夠的資訊,以使提交的查詢可得到最適當的結果集合。

    有助於達到此效應的應用程式設計技術包括:建立查詢時限制萬用字元的使用、強制要求輸入的欄位、禁止隨意性的查詢。
  • 在 DB-Library 應用程式中正確使用 dbcancel()。所有的應用程式都應當允許取消正在進行的查詢。不能有任何應用程式強迫使用者透過重新啟動電腦來取消查詢。不遵循此原則可導致無法解決的效能問題。在使用 dbcancel() 時,對於異動層級應給予一定的關注。額外資訊請參照下列 Microsoft Knowledge Base 文章:
    117143 : INF: 何時與如何使用 dbcancel() 或 sqlcancel()
    這些準則亦同樣適用於發出 ODBC sqlcancel() 指令的ODBC 應用程式。
  • 一定要處理完所有的結果。不要設計在未取消查詢就停止結果列處理的的應用程式,或使用此類的整套應用程式。這樣做通常會導致阻塞(blocking)以及效能緩慢的問題。
  • 一定要設定查詢逾時。不能讓查詢無限期的執行下去。建立適當的 DB-Library 或 ODBC 呼叫以設定查詢逾時。在 DB-Library 裡,查詢逾時是透過 dbsettime() 呼叫啟動的,在 ODBC 裡則是用 SQLSetStmtOption()。
  • 不要使用無法對傳送給伺服器的 SQL 語句進行明確控制的應用程式發展工具。不要使用會透明化基於更高層級物件的 SQL 語句的工具,除非它提供了查詢取消、查詢逾時、以及完全的異動控制功能。如果應用程式自己產生「透明 SQL」,要保持好的效能或解決效能問題通常不太可能,因為這將不允許明確控制對效能很關鍵的異動性和鎖定問題。
  • 不要把決策支援與線上異動處理 (OLTP) 查詢混淆。
  • 不要設計強迫使用者透過重新啟動客戶電腦來取消查詢的應用或套裝程式。由於可能出現的無主狀態的連線,會導致許多難以解決的效能問題。詳細資訊請參照下列 Microsoft Knowledge Base 文章:
    137983 : 如何排解 SQL Server 中無主連線的問題

分析效能緩慢問題的技術

僅透過系統層級伺服器效能的校正就能解決效能問題是很誘人的。例如校正記憶體大小、檔案系統類型、處理器數量和類型等等。Microsoft SQL Server 支援小組的經驗顯示,大多數問題無法透過此方法排除。必須透過分析應用程式、應用程式提交給資料庫的查詢、以及這些查詢如何與資料庫架構進行交流,方可解決此類問題。

首先,將速度很慢的查詢隔離出來。通常整個應用程式看似很慢的情況,實際上只有少數 SQL 查詢緩慢。不對問題作細致分解並隔離慢速的查詢,效能問題往往無法解決。如果您用的是產生透明化 SQL 的發展工具,請用該工具的任何診斷或偵錯模式來補抓產生的 SQL。很多情況下會有一些追蹤功能可用,但可能沒有被公佈。請與應用程式的技術支援聯繫,以確定有無可監視應用程式產生的 SQL 語句的追蹤功能。

對於那些使用內嵌式 SQL 的應用程式發展工具,這要容易的多,因為 SQL 是公開可見的。

如果您的發展工具或最終使用者應用程式沒有提供追蹤功能,可選擇幾種備用方法:
  • 根據 SQL Server 4.2x 版 「疑難排解指引」 和 SQL Server 6.0 版 「異動-SQL 參考」 的指示使用 「4032 追蹤標幟」。這將允許在 SQL 錯誤記錄中擷取傳送到伺服器的 SQL 語句。
  • 透過像 Microsoft 網路監視器 (它是 [系統管理伺服器] 的一部分) 的網路分析程式來監視查詢。
  • 對於 ODBC 應用程式,使用 ODBC Administrator 程式來選擇追蹤 ODBC 呼叫。欲知詳細資訊,請參閱 ODBC 文件。
  • 使用在 DB-Library 或 ODBC 層截取 SQL 的協助廠商用戶端工具。Blue Lagoon Software 的 SQL Inspector 便是一個例子。
  • 使用 Microsoft TechNet 光碟中供作範例的 SQLEye 分析工具。附註: SQLEye 不在 Microsoft 技術支援範圍之內。
在隔離了慢速查詢後,執行下列:
  • 使用 ISQL 之類的查詢工具,單獨的執行被懷疑速度很慢的查詢,對其實際速度進行驗證。最好的做法是用 ISQL 及本機管道(Local pipes)在伺服器電腦上執行查詢,然後把輸出轉向輸出至檔案中。這有助於去除復雜的因素,如網路和螢幕畫面 I/O,以及應用程式結果緩衝等的影響。
  • 透過 SET STATISTICS IO ON 來檢查查詢使用的 I/O。注意邏輯頁 I/O 計數。最佳化程式的目的是使 I/O 計數達到最少。記錄下邏輯 I/O 計數。這將成為衡量效能改進的基準。比較 SET SHOWPLAN ON,專注於 STATISTICS IO 輸出,並試驗不同的查詢和索引類型,效果會更好。解釋並有效地應用 SHOWPLAN 的輸出是需要作一些研究工作,若把花費的時間用在一些試驗性測試上可能效率更高。如果這些簡單的建議還不能解決您的效能問題,您可使用 SHOWPLAN 對最佳化程式的行為作更徹底的調查。
  • 如果查詢涉及檢視(view)或儲存的程序(Stored Procedure),把它從檢視或儲存的程序提取出來單獨執行。在您嘗試不同的索引時,將允許對存取計畫作更改。這還有助於把該問題鎖定到查詢身上,而不是最佳化程式處理檢視或儲存程序的方法。如果問題並不在查詢本身,而是存在於查詢作為檢視或儲存程序的一部分,那麼獨立執行查詢也有助於確定這一點。
  • 注意所涉及的表格上可能存在的觸發器(Trigger),它們在觸發時會產生透明的 I/O。您應該把慢速查詢中涉及的觸發器刪除。這有助於確定問題究竟是在查詢本身,還是在觸發器或檢視上,從而確定您的焦點所在。
  • 檢查慢速查詢用到的表格的索引。透過上述技術確定這些索引是否合理,並對它們作必要的修改。您的第一步應該是對 WHERE 子句中的每一欄位進行索引。很多時候,效能問題是由於 WHERE 子句中的欄位沒有進行索引,或是欄位上缺少有效的索引而引起的。
  • 用之前提到的查詢來檢查 WHERE 子句所列的每一欄位 (尤其是每一索引欄位) 的資料不重復性和分佈。在很多情況下,只需對查詢、表格、索引和資料作簡單的檢查,馬上就能看出問題的起因。例如,效能問題經常是由於對一個只有三個或四個不重覆的關鍵值進行了索引,或在此類欄位上執行結合,或把過多的列數傳回給客戶而造成的。
  • 在此基礎上,對應用程式、查詢或索引作必要的修改。完成修改後,再次執行查詢,並觀察 I/O 計數的變化。
  • 如果發現速度有所提高,請執行主應用程式,看看整體效能是否有提高。
檢查程式所受 I/O 或 CPU 限制的行為。通常,確定查詢是不是受 I/O 或 CPU 限制是非常有用的。這有助於把提高效能的焦點集中在真正的瓶頸上。例如,如果查詢是受 CPU 限制,那麼為 SQL Server 添加再多的記憶體也不能明顯改善它的效能,這是因為更多的記憶體只能改善快取記憶體使用比率,而在此,本來就已經很高了。

如何檢查 I/O 受限與 CPU 受限的查詢行為:
  • 用 Windows NT 效能顯示器來監視 I/O 與 CPU 之間的活動。監視 LogicalDisk 物件「% Disk Time」計數器的所有實例。同時監視 System 物件的 「% Total Processor Time」計數器。要檢視有效的磁碟效能資訊,您必須先從 DOS 模式發出「diskperf -Y」,開啟 Windows NT DISKPERF 設定,然後重新啟動系統。詳細資訊請參閱 Windows NT 文件。
  • 查詢執行階段,如果 CPU 圖形一直很高 (例如,高於 70 %),而「% Disk Time」一直很低,說明這是一個 CPU 受限的狀態。
  • 查詢執行階段,如果 CPU 圖形一直很低 (例如,低于 50 %),而「% Disk Time」一直很高,說明這是一個 I/O 受限的狀態。
  • 把 CPU 圖形與 STATISTICS IO 資訊進行比較。

結論

SQL Server 在大型資料庫中可以有很高的效能。尤其 SQL Server 6.0 及此後版本的 Microsoft SQL Server 更是如此。為了實現這種效能潛力,您必須使用有效的資料庫、索引、查詢和應用程式設計。從這幾個方面,最有可能獲得顯著的效能改善。讓每一個查詢都盡可能地有效率,這樣應用程式就可達到更好的多使用者擴展性,可以支援集團多使用者負載。我們鼓勵您使用本文提供的指導方針對客戶應用程式的行為、應用程式提交的查詢進行必要的研究,並對不同的索引進行試驗。有系統的分析效能問題的方法,通常可以透過投入相對較少的時間,產生顯著的效能改善。

屬性

文章編號: 110352 - 上次校閱: 2004年9月22日 - 版次: 2.2
這篇文章中的資訊適用於:
  • Microsoft SQL Server 4.21a Standard Edition
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
關鍵字:?
kbother sqlfaq ssrvadmin ssrvwinnt kbhowto kbhowto KB110352
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應,不負任何擔保責任。Microsoft及(或)其供應商謹此聲明,不負任何對與此資訊有關之擔保責任,包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。
依現狀不再更新的知識庫內容免責聲明
本文旨在說明 Microsoft 不再提供支援的產品。因此,本文係依「現狀」提供,不會再更新。

提供意見

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com