美創運維日記|SQL Server安裝後，需立即修改的三個引數

導讀：SQL Server的預設安裝在很多方面的配置是錯誤的，為了獲得更快的效能，在SQL Server安裝完成後，有三個需要立即修改的引數配置：並行的開銷閾值、最大伺服器記憶體、最大並行度。

 

當大家用MSSQL的安裝嚮導“下一步”、“下一步”安裝了全新的SQL Server軟體，最後你點選了完成安裝按鈕。

歐耶~~~ 大功告成！！！

現在我們可以把我們的伺服器放入生產了！

 

-對不起，那並不是真的

-why？

-因為你的剛剛安裝的SQL Server資料庫的預設配置是錯誤的！！！

-Are you kidding me？？

 

是的，你沒看錯，小編向你說的就是：SQL Server的預設安裝在很多方面的配置是錯誤的。

 

在今天的文章裡，美創運維團隊向你展示，為了更快的效能，在SQL Server安裝完成後，有三個需要立即修改的引數配置。

 

那，我們開始吧！

 

 

01   並行開銷閾--Cost Threshold for Parallelism

第一個你需要修改的配置選項是SQL Server處理並行開銷的閾值。

 

首先，我們根據官方文件的定義來理解下並行開銷閾值的含義。

cost threshold for parallelism 選項指定 SQL Server 建立和執行並行查詢計劃的閾值。僅當執行同一查詢的序列計劃的估計開銷高於在“並行的開銷閾值” 中設定的值時，SQL Server 才建立和執行該查詢的並行計劃。成本指的是在特定硬體配置中執行序列計劃估計需要花費的成本，而不是時間單位。“並行的開銷閾值” 選項可設定為0 到 32767 之間的任何值。預設值為 5。

 

並行意味著SQL Server能透過多個工作執行緒執行執行計劃裡的運算子。並行的目的是提高你查詢的吞吐量。SQL Server裡第1個影響並行的配置選項是所謂的 並行開銷閾值：

並行開銷閾值

 

這裡你配置的數字定義查詢成本，查詢最佳化器用它來找更便宜的並行執行計劃。如果找到的並行計劃更便宜，這個計劃會被執行，不然序列計劃會被執行。從剛才的圖你可以看到，SQL Server預設配置使用5的成本閾值。當你的序列計劃查詢成本大於5，然後查詢最佳化器再次執行查詢最佳化來找更便宜並行執行計劃的可能。

 

然而遺憾的是，5的成本值當下來說是個很小的數字。因此SQL Server會太快嘗試並行你的執行計劃。

 

而實際上呢，當你處理更大的查詢時，並行才有意義——例如報表或資料倉儲情形。在純OLTP情形下，並行計劃象徵著糟糕的索引設計，因為當你有缺失索引時，SQL Server需要掃描你的整個聚集索引，因此你的查詢成本越來越大，它們超過成本閾值，最後查詢最佳化器經過判斷給你並行計劃。當人們看到並行計劃出現時，總會擔心資料庫效能是不是有問題！但問題根源其實是缺失非聚集索引。

 

對於並行的成本閾值，我推薦至少20，甚至50。那樣的話，你確保SQL Server只會對更大的查詢進行並行。即使在你面前有個並行計劃，你也應該考慮下是否可以透過增加一個支援的非聚集索引來使這個查詢的成本更低。另外，CXPACKET（並行度）並不意味著在你的系統裡你有並行問題！

 

02   最大伺服器記憶體--Max Server Memory

 

現在在你面前你應該有個64位的SQL Server。64位意味著你可以理論上訪問2^64的記憶體大小——那是10億GB！因為這些巨量的記憶體，計算機供應商當前限制64位系統的地址匯流排“只有”48位——完全64位沒有真正意義。用48位的地址空間，你可以訪問256TB的記憶體——那還是大量的空間！！！

 

你可以使用 最大伺服器記憶體配置選項來配置SQL Server可以消耗的記憶體大小。下圖顯示的是在64位系統上SQL Server預設安裝後的配置選項。

最大伺服器記憶體

從剛才的圖片你可以看到，SQL Server預設配置是可以消耗上至2147483647MB的記憶體。嗯，用48位的地址匯流排我們只能物理訪問256TB的記憶體，現在SQL Server可以消耗上至20億MB的記憶體？這裡有什麼東西不對…… 最大伺服器記憶體設定比32位最大整形值還大——2147483647。不考慮別的因素消耗，但是因此SQL Server可以消耗比實體地址更多的記憶體？這是一個很不好的預設配置。SQL Server預設可以吃光你整個實體記憶體！

 

你總應該修改下這個配置選項，這樣的話你可以給作業系統一些記憶體，讓它可以活著喘口氣吧？

 

一般來說（在伺服器上沒有其它程式/程式）你也應該給系統至少10%的實體記憶體。這就是說你需要調低最大伺服器記憶體設定。例如，有64GB的實體記憶體我會配置最大伺服器記憶體為56GB，這樣的話作業系統可以用剩下的8G來消耗和工作。

 

03   最大並行度--Max Degree of Parallelism (MAXDOP)

 

當在SQL Server裡一個執行計劃進入並行， 最大並行度定義了執行計劃裡每個並行運算子可用工作執行緒。下圖顯示了這個選項的預設配置。

最大並行度

如你所見，SQL Server使用預設值0。這個值意味著SQL Server嘗試並行化你的執行計劃超過分配給SQL Server的所有CPU核心（即使預設情況所有核心都分配給SQL Server！）。你應該能看出這樣的設定沒有意義，尤其當你有大量CPU核心的系統。並行化本身帶來負擔，一旦你使用越多的工作執行緒，這個負擔越大。

 

一個建議是設定最大並行度為在一個NUMA結點裡擁有的核心數。因此在查詢執行時，SQL Server會嘗試在一個NUMA結點裡保持並行計劃，這也會提高效能。

 

有時你也會看到建議去設定最大並行度為1。這個是不好的建議，因為這個使你的“整個”SQL Server 單執行緒！即使維護操作（例如索引重建）已單執行緒執行，這會嚴重傷及效能！

 

 

小結

 

在你安裝完SQL Server後，DBA的真正工作才開始：你需要配置你的SQL Server安裝到你的硬體配置。在這篇文章裡你已看到，SQL Server的預設配置是明顯錯誤的。因此在安裝後立即修改一些配置選項非常重要。我已經見過生產環境裡SQL Server使用我這裡提到的預設選項，因為它們“稍後“會被配置，“稍後”就從未發生了……

 

 

美創運維中心資料庫服務團隊擁有Oracle ACE 1人、OCM 10餘人、數十名Oracle OCP、MySQL OCP、紅帽RHCA、中介軟體weblogic、tuxedo認證、達夢工程師 ，著有《Oracle DBA實戰攻略》，《Oracle資料庫效能最佳化方法和最佳實踐》，《Oracle核心技術揭秘》等多本資料運維最佳化書籍。目前運維各類資料庫合計2000餘套，精通Oracle、MySQL、SQLServer、DB2、PostgreSQL、達夢等主流商業和開源資料庫。併成為首批國內達夢戰略合作伙伴之一，擁有海量經驗和完善的人員培養體系。並同時提供超融合，私有云整體解決方案。