最有效地優化 Microsoft SQL Server 的性能

，您必須明确當情況不斷變化時，性能将在哪些方面得到最大程度的改進，并集中分析這些方面。否則，在這些問題上您可能花費大量的時間和精力，而性能卻得不到明顯的改善。 

以下大部分資訊并不解決由多使用者并發使用而引起的性能問題。“Maximizing

Database Consistency and

Concurrency”（資料庫一緻性和并發性的最大化）一文以及其他知識庫文章将對這個複雜的主題做單獨的讨論，前者可從 SQL Server 4.2x

版的“Programmer‘s Reference for C”（C 程式員參考）的附錄 E 中找到。6.0 版的文檔中沒有這一主題，不過可從 MSDN

(Microsoft Developer Network) CD 上的相應标題下找到。 

本文并不進行理論性的讨論，而是着眼于

Microsoft SQL Server

支援小組幾年來總結出的對現實情形有着實際價值的經驗。 

經驗表明：可以從邏輯資料庫設計、索引設計、查詢設計以及應用程式設計的正常方面獲得

SQL Server

性能上的最大優勢。相反，最大的性能問題通常是由這些方面的缺陷引起。如果您關注性能問題，首先應着重考慮這些方面，因為通常隻需投入相對較少的時間即可顯著地改善性能。 

雖然其他系統級的性能問題，如記憶體、高速緩存、硬體等也值得考慮，不過經驗表明，從這些方面獲得的性能改善不斷增加。SQL

Server

會自動管理可用硬體資源，這在很大程度上減少了大量系統級手動調整的需要（也是以減少了手動調整帶來的好處）。 

Microsoft SQL

Server 6.0

提供了大容量記憶體、對稱多路處理、并行資料掃描、增強的優化程式及磁盤條帶化等功能，為平台層次的性能改善帶來了新的機會。這種性能改善很充分，但畢竟範圍有限。最快的計算機也會因為低效的查詢或設計糟糕的應用程式而陷入癱瘓。是以，盡管

SQL Server 6.0

允許的性能改進有所增加，但對資料庫、索引、查詢及應用程式設計進行優化仍是非常重要的。 

如果隻把考慮的重點放在伺服器端，很多性能問題是無法得到圓滿解決的。伺服器從本質上說仍是用戶端的“傀儡”，因為用戶端控制着查詢的發送，并由此控制鎖的擷取或解除。雖然可以在伺服器端做某些調整，但能否圓滿解決性能問題，通常取決于對用戶端在這個問題中所扮演的主導角色的認可程度，以及對用戶端應用程式行為的分析。

下面是根據經驗總結出的一些建議，它們能使性能得到顯著的改善： 

對邏輯資料庫的設計進行合理的規範将産生最佳的性能。數量較多的“窄”表是規範化資料庫的特征。而數量較少的“寬”表是沒有規範化資料庫的特征。高度規範化的資料庫通常與複雜的關系聯接相關，這會損害性能。但隻要有有效的索引，SQL

Server 優化程式就能非常有效地選擇快速、高效的聯接。 

規範的好處包括： 

由于表較窄，會加快排序和建立索引的速度。

由于表較多，允許有更多的群集索引。

索引變得更窄、更緊湊。

每個表中的索引減少，有助于改善 UPDATE 操作的性能。

空資料和備援資料減少，使資料庫更為緊湊。

減少了 DBCC 診斷的并發影響，這是因為必要的表鎖隻會影響較少的資料。

是以對于 SQL

Server，合理的規範往往有益于而不是有損于性能。随着規範化程度的提高，檢索資料所需聯接的數量和複雜程度也會相應增加。根據經驗，Microsoft

建議除非規範化過程導緻衆多查詢出現 4

重或更多的聯接，否則應始終進行該過程。 

在資料庫的邏輯設計已經确定，整體重新設計不可行的情況下，可以選擇一個經分析顯示對性能造成瓶頸的大表進行規範化。如果對資料庫的通路是通過存儲過程進行的，則可在不影響應用程式的情況下對這種架構進行更改。否則，可以建立一個視圖（看上去像一張表）來隐藏此更改。 

與許多非關系型系統不同，關系型索引不是資料庫邏輯設計的一部分。索引可以被删除、添加或修改，除了影響性能之外，它不會對資料庫架構或應用程式設計的各方面造成影響。有效的索引設計對獲得優異的

性能是至關重要的。鑒于這些原因，您應毫不猶豫地試用各種索引。 

在大多數情況下，優化程式會可靠地選擇最有效的索引。索引設計的整體政策應是為優化程式提供一組最佳的索引選擇，并相信它會做出正确的決定。這将減少分析時間，并在情況不斷變化時提供最佳的性能。 

下面是一些索引設計建議： 

檢查 SQL 查詢的 WHERE 子句，因為這是優化程式的重點。 

WHERE

子句中列出的每一列都可考慮使用索引。如果檢查的查詢太多，不妨選擇一個有代表性的集合，或隻檢查那些速度較慢的查詢。如果您的開發工具以透明方式生成 SQL

代碼，檢查将更加困難。很多這樣的工具出于調試目的，都允許将生成的 SQL 文法記入檔案或螢幕。可能需要從工具供應商那裡了解這一功能是否可用。

使用窄索引。 

窄索引往往比多列的組合索引更為有效。窄索引在每頁上有更多的行，同時具有較少的索引級别，因而能提升性能。 

優化程式能快速而有效地分析幾百個、甚至幾千個索引和聯接可能性。擁有更多的窄索引，将給優化程式提供更多的選擇餘地，這通常有助于提高性能。相反，擁有較少的多列寬索引，提供給優化程式的選擇餘地很少，這可能會損害性能。 

通常最好不要采用一個強調完全覆寫查詢的政策。如果

SELECT

子句中的所有列都被一個非群集索引覆寫，優化程式會識别出這一點，并可以提供很好的性能。不過，這通常會導緻索引過寬，并會過度依賴于優化程式使用該政策的可能性。通常，您應使用數量更多的窄索引，這對于大量的查詢來說可以提供更好的性能。 

要獲得足夠的讀取性能，您不應該有更多的索引，因為更新這些索引需要相當的開銷。然而，即便最面向更新的操作，所需要的讀操作也比寫操作多得多。是以，如果您覺得使用新的索引會有幫助，那就不要猶豫；之後可以随時再将其删除。

使用群集索引。 

适當使用群集索引可以極大地改善性能。甚至“更新”和“删除”操作速度也會因使用群集索引而大大加快，因為這些操作也需要很多的讀取。每個表隻允許有一個群集索引，是以使用時務必謹慎。傳回很多行的查詢或涉及一個數值範圍的查詢，都适合用群集索引來加速執行。 

示例：

相反，如果這類查詢很普通，則上述的 LASTNAME 或 MEMBER_NO

列就不太适合用非群集索引。非群集索引應盡量用在傳回行數不多的列上。

檢查列的唯一性。 

這有助于您決定什麼樣的列适合使用群集索引、非群集索引，或不用索引。 

下面是一個檢查列唯一性的示例查詢：

它會傳回列中唯一值的數量。将這一數量與表中的總行數進行比較。對于一個 10,000 行的表，如果一列有 5,000

個唯一值，它就很适合使用非群集索引。在同一表中，如果一列有 20 個唯一值，它适合使用群集索引。而如果隻有 3

個唯一值，就不要用索引。這些僅是例子而已，并不是死的、硬性的規定。請記住，索引應放在查詢 WHERE 子句列出的各列上。

檢查索引列的資料分布。 

如果對某個隻有很少唯一值的列建立了索引，或在這樣一列上執行了聯接，往往會導緻查詢速度緩慢。這對資料和查詢來說是一個重大問題，如不了解情況就無法得到解決。例如，如果某城市所有的人都叫

Smith 或

Jones，那麼想從一本姓氏按字母順序排列的該市的電話簿中快速找到某個人是不太可能的。除了上面那個查詢可以給出列唯一性的單個名額外，您還可使用 GROUP

BY

查詢來檢視索引鍵值的資料分布。它提供了一個分辨率更高的資料視圖，并為優化程式檢視資料提供了更好的視角。 

下面是一個檢查索引鍵值資料分布的示例查詢（假定

COL1、COL2 兩列是關鍵字）：

對應每一個鍵值都将傳回一行，并帶有各個值的執行個體計數。要減少傳回的行數，可用 HAVING 子句排除一部分傳回結果。例如，子句

将排除所有具有唯一關鍵字的行。 

查詢傳回的行數也是關系索引選擇的一個重要因素。優化程式認為一個非群集索引對傳回的每一行至少要消耗一頁

I/O。按這個速度計算，掃描整個表将變得更為有效。這也是限制結果集大小，或用群集索引查找大型結果的另一個原因。

不要把使用索引與好的性能等同起來，反之亦然。假如使用索引總能産生最佳性能，優化程式的工作就太簡單了，隻要使用任何可用的索引就行了。而實際情況是，選用索引檢索不當會導緻很糟糕的性能。是以，優化程式的任務是選擇有益于性能的索引檢索，而避免有損于性能的索引檢索。 

某些類型的查詢本來就是要占用大量的資源。這與大多數關系型資料庫管理系統 (RDBMS)

常見的基本資料庫和索引問題有關，而不是 SQL Server

特有的。它們并非是低效的，因為優化程式将以盡可能最為有效的方式來實作這些查詢。但它們仍會占用大量的資源，而 SQL

面向集合的特性使它們顯得效率很低。優化程式沒有辦法消除這些結構對資源的固有消耗。與較簡單的查詢相比，它們非常耗費資源。盡管 SQL Server

将使用最佳通路計劃，但根本上還是受可能占用大量資源的限制。 

例如： 

大型結果集

IN、NOT IN 和 OR 查詢

高度非唯一 WHERE 子句

!=（不等于）比較運算符

某些列函數，如 SUM

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

WHERE 子句中的局部變量

GROUP BY 的複雜視圖

許多因素使得有必要使用這些查詢結構。如果優化程式能在執行占用大量資源的部分查詢之前限制結果集的大小，這些查詢結構對性能的影響将減少。下面是一些示例： 

占用大量資源的查詢： 

占用較少資源的查詢： 

在第一個示例中，SUM 操作無法通過索引加快速度。因為每一行都得讀取并累加。假定在 ZIP

列上有一個索引，優化程式将很可能在應用 SUM

之前先用它來限制結果集。這樣會加快速度。 

在第二個示例中，隻有在運作時才會解析局部變量。但優化程式不能将通路計劃的選擇推遲到運作時；它必須在編譯時進行選擇。然而，如果在編譯時建立通路計劃，@VAR

值還是未知的，因而無法作為索引選擇的輸入項。 

以上示例中用于改善性能的技術，涉及到用 AND

子句來限制結果集。作為另一種替代技術，可以使用一個存儲過程，并把用于 @VAR

的值作為參數傳遞給存儲過程。 

某些情況下，最佳做法是使用一組簡單的查詢，并通過臨時表存儲中間結果，這比使用單個複雜查詢好得多。 

對于大多數

RDBMS，大型結果集是很耗費資源的。您應該通過浏覽盡量避免把一個大型的結果集作為最終的資料選擇傳回給用戶端。限制結果集的大小，允許資料庫系統執行一些固有功能，效率會更高。這将減少網絡

I/O，并使得應用程式更适合于通過慢速遠端通訊連結進行部署。随着應用程式擴充到更多的使用者，它還會改善與并發相關的性能。 

應用程式設計在 SQL Server

性能中所起的作用無法用言語充分表達。把用戶端看作控制實體，而伺服器隻是用戶端的一個“傀儡”，要比把伺服器看成是主導角色更為準确。在查詢類型、何時送出查詢、如何處理結果等方面，SQL

Server 完全受用戶端的支配。這反過來對鎖的類型及持續時間、伺服器 I/O 及 CPU

負擔都有很大的影響，并是以決定了性能的好壞。 

鑒于此原因，在應用程式設計階段做出正确的決策是非常重要的。即便對使用轉包應用程式時遇到的性能問題（也就是說，修改用戶端應用程式好象不太可能），影響性能的這些基本因素也不會變化，即用戶端起着決定性的作用，如果不對用戶端進行修改，很多性能問題将無法解決。 

使用設計完美的應用程式，SQL

可能支援數以千計的并發使用者。而如果應用程式設計不好，即使是最強大的伺服器平台在隻有幾個使用者的情況下也會陷入癱瘓。 

在設計用戶端應用程式時采用以下建議，将會帶來優異的

SQL Server 性能： 

使用小型結果集。為用戶端浏覽檢索不必要的大型結果集（例如，好幾千行），将會增加 CPU 和網絡 I/O

負擔，使應用程式不能十分有效的用于遠端，同時還限制了多使用者擴充性。是以您設計的應用程式最好提示使用者輸入足夠的資訊，以便送出的查詢可以生成最适當的結果集。 

有助于達到這一目的的應用程式設計技術包括：建立查詢時限制通配符的使用、強制要求輸入某些字段、禁止随意性的查詢。

在 DB-Library 應用程式中正确使用

dbcancel()。所有的應用程式都應當允許取消正在進行的查詢。任何應用程式都不能迫使使用者通過重新啟動計算機來取消查詢。不遵循這個原則就會出現無法解決的性能問題。在使用

dbcancel() 時，對事務級應給予一定的關注。有關其他資訊，請參見以下 Microsoft 知識庫文章：

：INF：使用

dbcancel() 或 sqlcancel() 的條件或方法如果使用了 ODBC sqlcancel() 調用，ODBC

應用程式也會出現同樣的問題。

一定要處理完所有結果。不要設計沒有取消查詢就停止結果行處理的應用程式，或使用這類轉包應用程式。這樣做通常會導緻阻塞并降低性能。

一定要實作查詢逾時。不能讓查詢無限期地運作。請用适當的 DB-Library 或 ODBC 調用設定查詢逾時。在 DB-Library

中，查詢逾時是通過 dbsettime() 調用實作的，在 ODBC 中是通過 SQLSetStmtOption() 實作的。

不要使用不允許對發送給伺服器的 SQL 語句進行顯式控制的應用程式開發工具。不要使用基于更進階對象以透明方式生成 SQL

語句的工具，除非它提供了查詢取消、查詢逾時、完全事務控制等重要功能。如果由應用程式本身生成所有的“透明

SQL”，要保持好的性能或解決性能問題通常不太可能，因為這将不允許顯式控制事務性和鎖定問題，而它們對性能是很關鍵的。

不要把決策支援與聯機事務處理 (OLTP) 查詢混在一起。

不要設計強迫使用者通過重新啟動客戶機來取消某個查詢的應用程式，或使用這樣的轉包程式。由于可能有處于無主狀态的連接配接，這将導緻許多難以解決的性能問題。有關更多資訊，請參閱下面的

Microsoft 知識庫文章：

 INF：如何排除

SQL Server 中無主連接配接的問題

隻通過系統級伺服器性能的調整就能解決性能問題是很誘惑人的。例如，記憶體大小、檔案系統類型、處理器數量和類型等等。Microsoft SQL Server

支援小組的經驗表明，大多數性能問題無法通過這一方式解決。必須通過分析應用程式、應用程式送出給資料庫的查詢、以及這些查詢如何與資料庫架構進行互動，才能解決這些問題。 

首先，将速度很慢的一個或多個查詢隔離。通常在整個應用程式看起來都很慢的情況下，實際隻有少數

SQL 查詢很慢。不對問題作細緻分解并隔離慢速的查詢，往往無法解決性能問題。如果您用的是以透明方式生成 SQL

的開發工具，請用該工具的診斷或調試模式來捕獲生成的

SQL。很多情況下還有一些跟蹤功能可用，但它們可能沒有被公開。請與應用程式的技術支援部門聯系，以确定有沒有可監視應用程式生成的 SQL

語句的跟蹤功能。 

對于那些使用嵌入式 SQL 的應用程式開發工具來說，這要容易的多，因為 SQL

是可見的。 

如果您的開發工具或最終使用者應用程式沒有提供跟蹤功能，則還有幾種備用方法可供選擇： 

根據 SQL Server 4.2x“故障排除指南”和 SQL Server 6.0“Transact-SQL 參考”中的說明使用 4032

跟蹤标記。這将在 SQL 錯誤日志中捕獲發送到伺服器的 SQL 語句。

通過使用像“Microsoft 網絡螢幕”（它是 Systems Management Server

的一部分）這樣的網絡分析程式來監視查詢。

對于 ODBC 應用程式，使用 ODBC Administrator 程式來選擇跟蹤 ODBC 調用。有關詳細資訊，請參見 ODBC

文檔。

使用在 DB-Library 或 ODBC 層截獲 SQL 的第三方用戶端工具。Blue Lagoon Software 的 SQL

Inspector 便是一個例子。

使用 Microsoft TechNet CD 中作為示例提供的 SQLEye 分析工具。注意： SQLEye 不在 Microsoft

技術支援範圍之内。

在隔離了慢速查詢後，執行以下操作： 

使用 ISQL 之類的查詢工具，獨立運作被懷疑速度很慢的查詢，對其實際速度進行驗證。最佳做法是用 ISQL

及本地管道在伺服器計算機上運作查詢，然後再将輸出重定向到檔案中。這有助于消除複雜的因素（如網絡和螢幕 I/O）以及應用程式結果緩沖的影響。

使用 SET STATISTICS IO ON 來檢查查詢占用的 I/O。注意邏輯頁 I/O 計數。優化程式的目标是使 I/O 計數最小。記錄邏輯

I/O 計數。這将成為衡量性能改善程度的基準。與使用 SET SHOWPLAN ON 相比，專門關注 STATISTICS IO

輸出，并試用不同的查詢和索引類型，會更為有效。解釋并有效地應用 SHOWPLAN

輸出需要作一些研究工作，把它占用的時間花在一些試驗性測試上可能效率更高。如果這些簡單的建議還不能解決性能問題，您可以使用 SHOWPLAN

對優化程式行為作更徹底的調查。

如果查詢涉及一個視圖或存儲過程，把它從視圖或存儲過程提取出來單獨運作。在您嘗試使用不同的索引時，這将允許對通路計劃進行更改。這還有助于把該問題定位在查詢本身，而不是優化程式如何處理視圖或存儲過程。如果查詢本身沒有問題，而在查詢作為視圖或存儲過程的一部分運作時出現問題，那麼獨立地運作查詢也将有助于确定這一點。

注意查詢涉及的表上可能存在的觸發器，它們在運作時會産生明顯的

I/O。應删除慢速查詢中涉及的觸發器。這将有助于确定問題是與查詢本身有關，還是與觸發器或視圖有關，進而确定您努力的方向。

檢查慢速查詢所使用的表的索引。通過上面列舉的技術來确定這些索引是否合理，并對其進行必要的更改。您努力的第一步應是對 WHERE

子句中的每一列進行索引。通常，性能問題是由于 WHERE 子句中的列沒有進行索引，或者這些列上沒有有效的索引而引起的。

用上面提到的查詢來檢查 WHERE

子句中提到的每一列（尤其是每一索引列）的資料唯一性和分布。在很多情況下，隻需對查詢、表、索引和資料進行簡單的檢查，便能立即找出問題的起因。例如，出現性能問題通常是由于對一個隻有

3 個或 4 個唯一值的關鍵字進行了索引，或在這樣的列上執行了聯接，或把過多的行數傳回給用戶端。

在這些研究的基礎上，對應用程式、查詢或索引進行必要的更改。完成更改後，再次運作查詢，并觀察 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”計數器。要檢視有效的磁盤性能資訊，您必須先從指令提示符發出“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

中更是如此。為了挖掘這種性能潛力，您必須使用有效的資料庫、索引、查詢和應用程式設計。因為最有可能從這幾個方面顯著提高性能。讓每一個查詢都盡可能地有效，這樣當應用程式擴充到更多的使用者時，可以支援集團多使用者負載。我們鼓勵您使用本文提供的指導方針對用戶端應用程式的行為、應用程式送出的查詢進行必要的研究，并試用各種索引。可以使用分析性能問題的方法，投入相對較少的時間，來顯著改善性能。