1.問題提出

  這些天菜鳥又遇到麻煩事兒了。Server Team交給菜鳥的這批伺服器跑起SQL Server來老是不順暢。菜鳥情不至盡的想起了老鳥，於是，敲開了老鳥辦公室的門：

  “鳥哥，到底我們要如何定製化或者說如何優化我們的Windows Server OS來使得我們的SQL Server達到最大程度的效能優化呢？”。老鳥還沒有反應過來，菜鳥劈頭蓋臉的問道。

  老鳥頓了兩秒，自信的回答道：“菜鳥，有進步啊，開始學會思考問題了。我們可以按照如下方法來優化我們的作業系統，使的SQL Server達到最大程度的優化。”。

2.SQL Server獨享OS系統資源

  SQL Server做為資料存取的後臺資料庫服務，註定了具有先天的I/O敏感型，CPU密集型的特點。因此，最好是將Windows OS儘可能多的資源都分配給SQL Server，以期望能夠獲取最優的資料存取效能。

2.1.SQL Server獨享物理機器

  要想獲得最大的效能優化，SQL Server必須是獨享Windows OS系統資源的。這一點非常容易理解，SQL Server需要像是獨生子女家庭的孩子一樣，集萬千寵愛於一身，獨享OS資源。

  試想一下，如果一個伺服器既提供DC服務，又提供FTP檔案下載上傳服務，還要提供SQL Server的資料存取服務。那麼，DC和FTP服務勢必會與SQL Server爭搶CPU，I/O，記憶體等系統資源。因此，SQL Server不可能取得最大的效能優化。

2.2.關閉不必要的服務

  基於上一小節的分析，我需要關閉SQL Server宿主伺服器上不必要的服務，以免造成和SQL Server資源的爭搶。這些服務包含但不僅限於：

  IIS、FTP、Index Service、Messenger、Microsoft Searching等。如果已經安裝了這些服務，請將服務啟動修改為Manual，以免服務開機自動啟動。

3.NTFS檔案系統

  關於Windows平臺檔案系統的選擇，我們一般選擇NTFS檔案系統。但，就NTFS而言，我們的版本選擇也有講究。

3.1.版本選擇

  2001年釋出的Windows XP及以後的Windows版本，引入了NTFS 5.0。我們有充足的理由選擇NTFS 5.0檔案系統，因為它比更早的NTFS 有一些新的效能增強點，比如：

  更少的磁碟訪問來找到想要的檔案。

  總體更快的磁碟讀取速度。

  當然，如果你的產品環境SQL Server已經使用了NTFS 4.0格式化你的檔案系統，要想轉化為NTFS 5.0就會變得非常困難。但是，當你有新的SQL Server例項需要安裝時，建議你使用NTFS 5.0。以下是檢查NTFS檔案系統版本的方法，我的測試機是Windows 7：

>fsutil fsinfo ntfsinfo C:
NTFS Volume Serial Number :       0x8ca4ddeba4ddd832
Version :                         3.1
Number Sectors :                  0x0000000009fccfff
...

3.2.加密與壓縮

  NTFS 提供了檔案加密和資料壓縮的功能，雖然預設情況下新安裝的作業系統這兩項功能都是關閉的，但是，為了避免人為錯誤的開啟了這兩項功能，我們還是非常有必要重點審查這兩項功能。因為，這兩項功能涉及到資料加密、解密、壓縮和解壓縮，都屬於典型的I/O密集型和CPU密集型操作，而對於SQL Server而言，任何增加I/O和CPU消耗的行為對於SQL Server都是巨大的傷害。

  檢視NTFS是否關閉壓縮和加密功能：

>fsutil behavior query DisableCompression
DisableCompression = 0

>fsutil behavior query DisableEncryption
DisableEncryption = 0

  關閉NTFS壓縮和加密功能

>fsutil behavior set DisableEncryption 1
NOTE: Changes to this setting require a reboot to take effect.
DisableEncryption = 1

>fsutil behavior set DisableEncryption 1
NOTE: Changes to this setting require a reboot to take effect.
DisableEncryption = 1

  如果想要啟用加密和壓縮功能，請將1修改為0。

3.3.參考連結

  NTFS Wiki

4.系統設定

  由於Windows OS是面向所有應用程式開發的，雖然它具有對於SQL Server資料庫系統的自適應能力，但是，我們還是需要針對SQL Server資料庫資料存取服務的特點來定製化系統的一些設定。

4.1.Best Performance

  修改作業系統為最佳效能模式，節約一些不必要的效能開銷，方法如下：
Start => Run => SystemPropertiesAdvanced => Settings => Adjust for best performance => OK => OK

4.2.Background Services

  由於SQL Server是屬於資料存取的資料庫後臺服務程式，所以，請將作業系統如何使用排程器的方法修改為Background services。方法如下：
Start => Run => SystemPropertiesAdvanced => Settings => Advanced => Adjust for best performance of Background services => OK => OK

4.3.Virtual Memory

  關於虛擬記憶體設定，其實存在很大的分歧。按照Microsoft官方的解釋是建議SQL Server宿主機虛擬記憶體為主機實體記憶體的1.5倍大小；如果SQL Server需要使用Full-Text元件，建議虛擬記憶體設定為實體記憶體的3倍大小；建議SQL Server的Max Server Memory為實體記憶體的1.5倍大小。
個人對虛擬記憶體大小的建議持保留態度，兩點理由：

• 理由一：SQL Server Max Server Memory如果是實體記憶體的1.5的話，SQL Server會盡可能多做資料快取，當實體記憶體塞滿時，勢必會導致虛擬記憶體的使用。由於虛擬記憶體實際上是將磁碟當著記憶體來使用，我們知道磁碟讀寫速度是遠遠低於記憶體讀寫的，哪怕是SSD。那麼，這個時候，資料庫會明顯的變慢，連線暴增，甚至會導致服務掛起，最終導致資料庫服務死掉。
• 理由二：1.5或者3倍實體記憶體空間的虛擬記憶體大小，導致大量的磁碟空間浪費。因為，目前的伺服器動輒上100GB的記憶體已是家常便飯的事了，有的生產環境的伺服器已經達到256GB記憶體空間了，這樣的話，有378GB – 768GB的空間浪費，如果磁碟是SSD的話，浪費的成本很更高。

  個人對虛擬記憶體大小設定的建議是，使用Windows效能監視器觀察Pageing File % Usage計數器值一段時間（比如一天甚至一週，時間越長越準確），然後讓虛擬記憶體的最大值和最小值保持一致，均設定為這個計數器的最大值再加上一個數字（比如10GB）。效能監視器中計數器的新增方法如下：

  虛擬記憶體大小設定方法如下： Start => Run => SystemPropertiesAdvanced => Settings => Advanced => Change => Custom Size => Set => OK。

  虛擬記憶體大小設定Microsoft的官方解釋參見連結：Configuring Virtual Memory

4.4.Maximizing Data Throughput for Network Applications

  為了給SQL Server提供更好的系統記憶體優化，我們還應該限制系統使用檔案快取的記憶體數量，來為SQL Server提供更多的系統記憶體。所以，請確保系統快取策略為Maximize data throughput for network applications，而不是Maximize Data Throughput for File Sharing。以下是Windows Server 2003的配置方法（Windows Server 2008的Maximize data throughput for network applications屬性按鈕是灰色的）：
Start => Run => Control => Network Connects => Local Area Connection => General => Properties => File and Printer Sharing Microsoft Networks => Properties => Maximize data throughput for network applications

  以下是對四種系統快取優化方案適用場景的解釋：

• Minimize memory used : 具有少量客戶端連線的伺服器；
• Balance : 多用途使用的伺服器優化。比如，提供檔案共享和列印服務的互動式工作站。
• Maximize data throughput for file sharing : 儘可能多的提供檔案和列印服務的專有伺服器。
• Maximize data throughput for network applications : 為擁有自己的記憶體快取機制的分散式應用程式優化系統快取，比如像微軟的SQL Server服務。

  參考連結，To configure File and Printer Sharing for Microsoft Networks

4.5.Lock Pages in Memory

  這個組策略項比較有意思，雖然它與SQL Server系統效能沒有直接關係。但是，它關係著32位SQL Server是否有許可權使用AWE(Address Windowing Extensions)。所以，它間接關係著32 位的SQL Server效能。

  請將Lock pages in memory策略裡設定SQL Server啟動使用者擁有這個許可權，否則，當我們啟用AWE的時候，會報告如下錯誤：

Address Windowing Extensions (AWE) requires the `lock pages in memory` privilege which is not currently present in the access token of the process.

  設定該策略的方法如下： Start => Run => gpedit.msc => Computer Configuration => Windows Settings => Security Settings => Local Policies => User Rights Assignment => Lock Pages in memory

4.6.Replication Memory Using Limitation Setting

  如果是單個SQL Server資料庫例項上建立過多的Replication Publication鏈，可能會遭遇如下的錯誤：

Agent `%s` is retrying after an error. %d retries attempted. See agent job history in the Jobs folder for more details.

  這是因為SQL Server對Replication釋出鏈使用的記憶體量有限制，我們通過修改登錄檔的 [HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSession ManagerSubSystemsWindows]鍵值來解決Replication的問題。修改的方法是將SharedSection=1024,20480,768修改為SharedSection=1024,20480,2048

%SystemRoot%system32csrss.exe ObjectDirectory=Windows SharedSection=1024,20480,2048 Windows=On SubSystemType=Windows ServerDll=basesrv,1 ServerDll=winsrv:UserServerDllInitialization,3 ServerDll=winsrv:ConServerDllInitialization,2 ServerDll=sxssrv,4 ProfileControl=Off MaxRequestThreads=16

5.安全

  關於安全，看起來好像和SQL Server效能沒有什麼直接的聯絡。但是，我們反過來想，如果SQL Server成為黑客的“肉雞”來隨意使用的話，當然會浪費大量的效能開銷。

5.1.Service Pack

  Microsoft Windows作業系統或者SQL Server本身的Service Pack，要麼解決了效能問題，要麼解決了安全隱患，要麼就是一些Bug Fix。所以，新的Service Pack出來並且穩定以後，我們還是很有必要打上這些Service Pack的。

5.2.Microsoft-Certified Hardware Drivers

  最常見的情況是與儲存和網路相關的驅動器會影響到SQL Server主機效能，進而影響到SQL Server的效能。因此，我們最好能夠週期性的檢查伺服器是否有最新，微軟認證的硬體驅動器。微軟認證這一點很重要，沒有通過認證的驅動安全性，穩定性很難保證。所以，為了系統效能和穩定性，我們需要耐心等待微軟認證的版本。至於檢查的方法，我們需要到供應商的官方網站去查詢或者開啟微軟更新服務。

6.尾聲

  聽完老鳥的娓娓道來，菜鳥茅塞頓開，趕緊去試驗去了。