INF:正確的 SQL Server 組態設定

文章翻譯 文章翻譯
文章編號: 166967 - 檢視此文章適用的產品。
本文曾發行於 CHT166967
本文已封存。本文係以「現狀」提供且不會再更新。
全部展開 | 全部摺疊

結論

SQL Server 的組態只需稍加微調,便可提供非常高的效能,例如,Microsoft 的 IS 部門就擁有可支援 4,000 名並行使用者的 SQL Server,除了記憶體、鎖定及 使用者連線等明顯的設定外,該系統大部分都使用預設的組態設定,然而,在 業界標準的四處理器電腦上執行時,該系統的效能卻非常的高。這種高效能 是透過優良的應用程式及資料庫設計所達成,而不是靠花大量的時間來微調 組態。

如需關於 SQL Server 效能最佳化的詳細資訊,請參閱 Microsoft Knowledge Base 中的下列文件:
110352INF: 將 Microsoft SQL Server 效能最佳化
當我們談到效能問題時,除非系統的組態原本就不正確,否則組態微調所提供的改善空間 通常都很有限。因為 SQL Server 7.0 使用自動組態微調,所以 7.0 中的組態設定 (尤其是 進階設定) 極少需要變更,除非極為必要,否則這些設定一般都不該變更。在變更之前, 請小心地循序進行測試,以確認變更的必要性,及變更的確可以改善情況。

如果組態不正確,則可能會使伺服器不夠穩定,或表現異常。除了記憶體、鎖定及 使用者連線等明顯的項目外,在調整其餘的設定前,都必須仔細考慮,尤其在調整進階 設定前,更需如此。請不要因為看了雜誌或白皮書的報導,或聽了諮詢者的意見,就 盲目地調整設定 (特別是在這些新設定與「Microsoft 技術支援」所推薦的設定相左時)。

其他相關資訊

我們服務數百個站台的多年支援經驗指出,失當的組態設定往往造成嚴重的後果,而 您尤其不該在沒有特定的必要原因時,變更下列設定的預設值。如果您在事前及事後沒有 仔細地根據科學方法來測試效能,也不該調整這些設定。這對 7.0 版尤為如此。我們許多 實際的支援案例均指出,就算不對 7.0 版進行人工組態微調,它也能有極高的效能表現。
關連遮罩 (affinity mask)(僅限 7.0 版)
輕量型共用 (lightweight pooling)(僅限 7.0 版)
設定工作集大小 (set working set size)
SMP 並行處理 (SMP concurrency)(僅限 6.5 版)
優先啟動 (priority boost)
最大工作者執行緒 (max worker threads)
程序快取區 (procedure cache)(僅限 6.5 版)
其他如 tempdb RAM 數 (tempdb in RAM)(僅限 6.5 版)、緩衝區 (free buffers)(僅限 6.5 版)、預先讀取參數 (read ahead parameters) (僅限 6.5 版) 及同步 I/O 上限 (max async I/O) 等設定,也不該在沒有進行目標效能測試並確定其改善 幅度前,隨便加以調整。

本文其餘部分將對上述每一項參數進行討論,並提供使用該參數時應考慮的事項:

關連遮罩 (僅限 7.0 版)

這是指某執行緒與特定 CPU 相關的程度。依預設值,Windows NT 使用的是「軟性(soft)」 相關性,也就是系統會將執行緒重排在該執行緒最後一次執行時所在的 CPU 上 執行,但如果不能,系統就可能會用不同的 CPU 來執行。在使用關連遮罩時,系統允許 人為干預,並會嚴格地將執行緒連結到指定的 CPU。這項作業是由附帶說明的 Win32 API SetThreadAffinityMas() 來完成,理論上,這會減少重新載入的機會或 CPU 快取及管道的量,因而提昇效能,但在實際應用時,卻很少能提昇效能,而常會 對效能造成負面的影響。

使用關連遮罩的另一個原因是限制 SQL Server 使用可用 CPU 子集,以增加 其他競爭服務存取 CPU 的機會,通常沒有必要這麼做,因為 SQL Server 7.0 是以一般 優先順序 (以 Windows NT 優先順序的 1-31 級而言,大約是 7 級) 來執行。Windows NT 執行緒規劃程式會動態地調整所有競爭執行緒的優先順序,以確保所有的執行緒都可 公平使用可用的 CPU。除了在十分不尋常的情況下,否則都不應調整關連遮罩。 在變更之前和之後,都應該用科學方法來進行測試,以確保變更確實能改善效能。

輕量型共用 (僅限 7.0 版)

依預設值,SQL Server 7.0 在執行每個作用中的 SPID (或使用者程序) 時,會使用一個執行 緒。這些執行緒是以共用的組態來執行,以將執行緒的數量保持在可以管理的程度。進階 組態選項「輕量級共用」會使用 Windows NT 的「光纖」支援,好在單一執行緒中執行數個 使用者程序。理論上,這可以減少執行緒內容切換,進而改善效能。

實際的生產經驗顯示,在大部分的情況下,上述情況並非必要,只有在符合下述所有情況時, 輕量型共用才「可能」有益,實際上是否有益還必須透過仔細的控制測試才能夠判斷:
  • 大型的多重 CPU 伺服器
  • 所有的 CPU 都幾乎以最高效能執行
  • 內容切換次數很高 (> 20,000/sec)
設定工作集大小

這個設定的預設值為 0,這樣 Windows NT Server 的虛擬記憶體管理程式便可 判斷 SQL Server 的工作集大小。在大多數的情況下,這都是正確的設定。在安裝 SQL Server 時,安裝程式會自動在伺服器服務內容的「網路控制台」中,選取 「最大化網路應用程式的輸送量」,這樣便可指示 Windows NT Server 的虛擬 記憶體管理程式不要對工作集進行過度的裁切,將對 SQL Server 工作集的干擾 降至最低。因為上述原因,所以通常您都不必覆寫「設定工作集大小」預設值, 但是,如果您將該值設為 1,則 SQL Server 在啟動時便會使用 Win32 API SetPRocessWorkingSetSize () 來儘可能地修正工作集。如果之後您又配置了過多的 SQL Server 記憶體,則作業系統就 比較無法彌補,而較可能發生與記憶體相關的作業系統錯誤。如需詳細資訊, 請參閱 Microsoft Knowledge Base 的下列文件:
110983 Recommended SQL Server for NT Memory Configurations
SMP 並行性 (SMP concurrency)(僅限 6.5 版) :

這個設定的預設值為 0,這可讓 SQL Server (在啟動時) 自動偵測出數字處理器, 並使用 n-1 個處理器。在 SMP 電腦上,該設定也會將 SQL Server 的程序優先順序 提高到 15 級。絕大多數的情況下,您都應該使用預設設定。將這個設定覆寫為 -1 會 移除 SQL Server 可使用的處理器數目限制,而這在某些情況下,可能會耗盡作業 系統可用的 CPU。其典型徵兆包括網路錯誤,或主控台停止回應,而 NET VIEW 指令也可能無法使用而產生「網路工作階段已經取消」的錯誤訊息。由於 CPU 耗盡, 所以電腦上執行的其他程序也可能停止回應,這可能包括其他的服務、SQL 可執行檔、 備份程式、批次工作等等。

在 Compaq Proliant 伺服器上,Automatic Server Recovery (ASR) 監視驅動程式可能 會沒有 CPU 可用,並假設 Windows NT Server 作業系統已經失敗,而重新啟動 伺服器來進行還原。

預設的「SMP 並行性」設定值 0 並不會「浪費」一個處理器,因為 SQL Server 在執行所有 的網路、I/O 及記憶體管理功能時,都會呼叫作業系統,當作業系統處理這些經常性的呼叫 作業時,都要使用 CPU 資源,而這個預設設定可確保作業系統取得所需的 CPU 時間,以快速 地處理 SQL Server 要求。因此,當您使用預設的「SMP 並行性」設定值 0 時,繁忙的 SQL Server 系統上的所有處理器都幾乎不會閒置,因此並不需要將「SMP 並行性」設定為 1, 2 或其他的值。

此處也要提到什麼時候該使用「SMP 並用性」設定值 -1,以及為什麼要提供這個設定值。 在純線上交易處理 (OLTP) 負載下,交易路徑長度受到嚴格的控制,並不允許進行臨時性或 決策支援查詢,而在電腦完全專用於 SQL Server 的環境中 (沒有使用主控台、檔案共用或 SQL 可執行檔),這個設定值也可用來提昇一些效能,而幾乎沒有副作用。

但是,如果伺服器所處理的查詢資料流,從上述單純的狀態而變為較複雜的情況, 怪異的行為便會發生。例如,向來執行順暢的系統可能會忽然停止回應。此處除錯 作業可能會很複雜,因為這些問題看來可能很像是作業系統的問題。

如需詳細資訊,請參閱 Microsoft Knowledge Base 中的下列文件:
111405 SQL Server and Windows NT Thread Scheduling
優先啟動 (priority boost) :這項設定的預設值是 0,這會使 SQL Server 在單處理器電腦上以處理優先順序 7 級執行, 而在 SMP 電腦上則以 15 級執行 (僅限 6.5 版 -- 7.0 版則不論是單處理器或 SMP 電腦均會以 7 級的基準優先順序來執行)。

大多數的情況下,均請使用預設設定。若將該設定設為 1,則在單處理器的電腦上, SQL Server 的處理優先順序會提昇至 15 (6.5 版及 7.0 版),而在 SMP 電腦上則會提昇 至 24 (僅限 6.5 版)。這與將「SMP 並行性」設定為 -1 的效果類似。

實際的支援經驗顯示,在大多數的情況下,啟用優先啟動並不一定會帶來優異的 效能,在某些狀況下,開啟這項功能反而可能干擾伺服器的穩定,因此除非情況特殊, 否則不該使用這項功能。例如,「Microsoft 產品支援服務」在進行效能調查時,可能會 使用這項功能。請不要因為某白皮書或諮詢顧問的建議,便使用這項功能。 最大工作者執行緒 (max worker threads) :這項設定的預設值為 255,也就是可以最多建立 255 條工作執行緒。大多數的情況下, 均請使用這個 255 的預設設定。但這並不表示系統只能建立 255 個使用者連線,一個系統可以 擁有數以千計的使用者連線,但基本上會進行多工處理,使其減至 255 條工作執行緒,而 使用者通常都不會感覺到延遲;在這種情況下,能夠「並行」的查詢數量只有 255 個, 但這又會進一步進行多工處理,直到降至可用的 CPU 數,因此,無論設定了多少工作 執行緒,並行作業實際上只是一個概念。

每建立一條新的使用者連線,就會建立一個新的工作者執行緒,直到達到工作者執行緒的上限。 在 SQL Server 6.5 中,工作者執行緒若連續一段時間沒有動作,就會自動過時而關閉。在 SQL Server 7.0 中, 則不會因為要節省資源,而將活動停滯的工作者執行緒從集區移除。如果設定了大量的工作者執行緒, 則通常會因為排程和資源多出的開銷,而使生產力下降、效能降低。 程序快取區 (procedure cache)

在 SQL Server 6.5 中,這個設定的預設值為 30,代表 SQL Server 快取區中有 30% 的空間會 保留給程序快取區。如果系統的記憶體很多 (例如 256 MB 或更多),則可能會將這個值 減少一點,而將較多的記憶體保留給緩衝快取區。但是,就算系統使用的預存程序極少, 也不要盲目地將這個設定值降到很低的數字 (例如 5%),這是因為程序快取區是一個 一般用途的記憶體區,可用來處理多種內部伺服器配置功能,而並不是只用於預存程序 的快取。如果這個記憶體區的空間不足,系統便可能發生錯誤或發生其他異常的行為。 您可在 SQL Server 6.5 中用「效能監視器」來監控程序快取區的使用,以決定是否要降低 程序快取區的設定。

在很多情況下,由於降低程序快取區的設定而釋放出的緩衝快取區,並不會對緩衝快取區 的觸及數比率造成正面的影響,因為快取區在百分比上的些微變化,並不會使該比率有多大 的變更。

?考

本文件是根據 Microsoft Knowledgebase 文件編號 Q166967 翻譯的. 若要參考原始英文文件內容, 請至以下網址:

http://support.microsoft.com/support/kb/articles/Q166/9/67.asp

屬性

文章編號: 166967 - 上次校閱: 2013年10月6日 - 版次: 1.0
這篇文章中的資訊適用於:
  • Microsoft SQL Server 4.21a Standard Edition
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
關鍵字:?
kbnosurvey kbarchive kbinfo kbenv kbusage KB166967
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