艾銻知識 | SQL Server誤設(shè)置max server memory的處理方法

2020-03-08 19:06 作者：艾銻無限 瀏覽量：

   
2015年我第一次參加阿里云杭州全球云棲大會時，看到的是驚喜和無限的可能性。
 
“2015杭州云棲大會”于10月14日至15日在杭州云棲小鎮(zhèn)如期召開。大會以“互聯(lián)網(wǎng)、創(chuàng)新、創(chuàng)業(yè)”為本屆主題，展現(xiàn)“互聯(lián)網(wǎng)+”時代下無處不在的云計算與各行各業(yè)的交錯連接，介紹云計算為產(chǎn)業(yè)升級和改革創(chuàng)新提供的源源動力，挖掘云計算助力下生生不息的創(chuàng)業(yè)激情和機(jī)遇。
 
2015年正式更名為“云棲大會”，阿里云的名字不再出現(xiàn)在會議名稱中，而是更加強(qiáng)調(diào)云計算、大數(shù)據(jù)生態(tài)的定位。量子計算、人工智能、生物識別、深度學(xué)習(xí)等前沿的科技創(chuàng)新力量首次在大會亮相。大會吸引了全球超過20個國家的21500名開發(fā)者，參展企業(yè)達(dá)到219家，參與企業(yè)3000多家，現(xiàn)場參觀超過42584人次，全球直播收看人數(shù)超過127萬人，成為全球最大規(guī)模的云計算峰會之一。
 
走進(jìn)會場別開生面，豐富多彩，讓人忘掉了科技的冰冷，就像走進(jìn)了游樂園，有運(yùn)動，有演出，有游戲，有展示，有互動，4萬多人把整個會場擠得水泄不通，來自全球的技術(shù)愛好者，企業(yè)家，科學(xué)家，政府官員，阿里云的合作伙伴，競爭對手，閑雜人等五花八門，你能想到的人都聚齊了。
 
甚至還有票販子，是什么吸引了各路好漢齊聚一堂，擠破頭也要參加一場聽起來非常專業(yè)性的云棲大會呢?
 
除了上面說的阿里云的技術(shù)能力和運(yùn)營能力以外，其實(shí)還有一個非常重要的原因，就是能親眼見到馬云馬老師，甚至還能有機(jī)會與他零距離接觸.
 
從2015到2018年，在這四年中馬老師每年都會參加杭州云棲大會的分享，而且還會出現(xiàn)在云棲現(xiàn)場的不同地方，這讓很多喜歡馬老師的伙伴興奮極了，你想你一生能有幾次親眼見到“外星人”呢.


2015年他在主會場20分鐘的分享表達(dá)了一個重要的觀點(diǎn)，就是在未來5年無企業(yè)，不上云。上云是企業(yè)戰(zhàn)略而不是戰(zhàn)術(shù)，未來是一個萬物互聯(lián)的時代，只有那些在云上的企業(yè)才能快速有效的抓住時代變化的先機(jī)，才能使用計算的能力，才能利用好數(shù)據(jù)的價值.
 
當(dāng)時聽了馬老師的演講，我當(dāng)刻就決定了和阿里云的合作，讓艾銻無限成為了阿里云的戰(zhàn)略合作伙伴，和阿里云一起為中小企業(yè)提供上云解決方案服務(wù)。
 
四年多過去了，馬老師當(dāng)年說無企業(yè)，不上云，如今，如果你不知道云，你的企業(yè)沒有用云，可能你在這次的疫情中根本就沒有戰(zhàn)斗力，你都不知道別人企業(yè)是如何贏得這次疫情戰(zhàn)斗勝利的.
 
企業(yè)上云不僅能為企業(yè)節(jié)省70%以上的成本，更能為企業(yè)提升500%以上的效率，甚至它的有些價值是無法計算的，這些年艾銻無限為上千家企業(yè)提供了上云服務(wù)，親身經(jīng)歷了有一些企業(yè)上云變革后帶來的增長和變化，我記得服務(wù)了一家裝修行業(yè)的領(lǐng)頭企業(yè)，在2016年這家企業(yè)因人員場地和機(jī)房成本，讓這家公司舉步維艱，離破產(chǎn)僅一步之遙，我和這家企業(yè)的CEO是好朋友，有一天我去看他，聽到他和我分享現(xiàn)在遇到的巨大困境，我問了他幾個關(guān)鍵的問題，最后確認(rèn)了是成本不斷提升，業(yè)績卻沒有提高，效率低下從而導(dǎo)致企業(yè)經(jīng)營困難，后來艾銻無限的云解決方案團(tuán)隊(duì)幫助他出了三個解決方案:
 
把他們現(xiàn)有機(jī)房近百臺服務(wù)器遷到云上，釋放出近百平米的機(jī)房空間和硬件運(yùn)維升級成本，因?yàn)樵剖菑椥缘模梢愿鶕?jù)企業(yè)用量來支付費(fèi)用，這一下就幫他們減輕了一年上百萬的硬件投入成本.
 
幫助他們打通各個管理系統(tǒng)，讓企業(yè)內(nèi)部數(shù)據(jù)更透明，讓整體效率一下子就提升了上來，而且當(dāng)他發(fā)現(xiàn)整個環(huán)節(jié)全部通透后，有1/3的人力是可以節(jié)省的，完全沒必要用這么多人，這一下讓他們每年節(jié)省了近200萬的人力支出，這就是科技的力量，雖然對失業(yè)的員工有些殘忍，但對與一家商業(yè)企業(yè)來說這是正確的選擇.
 
重建營銷系統(tǒng)，讓傳統(tǒng)的銷售模式重新變革，提升人員的產(chǎn)出比，讓全員進(jìn)行銷售，整體銷售額提高了200%，這個時代職責(zé)和名稱將會越來越模糊，誰能為企業(yè)創(chuàng)造價值，誰就是企業(yè)最重要的員工.
 
大家可以想象一下，一家企業(yè)人員降低原來的1/3，但銷售額卻提升原來的兩倍，這意味著盈利能力和盈利水平呈指數(shù)級的增長，這就是變革后的成果.
 
無企業(yè)，不上云，你的企業(yè)上云了嗎？

 
如果你的企業(yè)還沒有下云也沒有關(guān)系，也許你的企業(yè)還沒有遇到挑戰(zhàn)，假如你有和我朋友同樣的困境，那不妨與艾銻無限的云工程師和技術(shù)專家聊一聊，說不定就讓你眼前一亮，打開了你遇到的限制和困境，從而讓你的企業(yè)邁上了生長的第二春。

艾銻知識 |SQL Server誤設(shè)置max server memory的處理方法

在SSMS的UI界面設(shè)置“max server memory”，即使你設(shè)置為10M大小，但是它會“悄悄”默認(rèn)修改為128M，你用Profile跟蹤或者設(shè)置后會發(fā)現(xiàn)，它偷偷“修改”了你的設(shè)置值（改為了128M），

1 2 3 4 5 6

EXEC sys.sp_configure N'max server memory (MB)', N'128' GO RECONFIGURE WITH OVERRIDE GO    Configuration option 'max server memory (MB)' changed from 4096 to 128. Run the RECONFIGURE statement to install.

如果你沒有注意這些細(xì)節(jié)，或者不信這個事情，那么也可以用腳本測試一下，如下所示，它提示你這個值（10M）不是一個有效值。


當(dāng)你對“max server memory”做了錯誤設(shè)置后，那么基本上，任何查詢或連接都會出現(xiàn)類似下面這樣的錯誤：
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
 
------------------------------
There is insufficient system memory in resource pool 'internal' to run this query. (Microsoft SQL Server, Error: 701)
 
 
 
 
------------------------------
ADDITIONAL INFORMATION:
 
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - 遠(yuǎn)程主機(jī)強(qiáng)迫關(guān)閉了一個現(xiàn)有的連接。) (Microsoft SQL Server, Error: 10054)
 
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476
 
------------------------------
遠(yuǎn)程主機(jī)強(qiáng)迫關(guān)閉了一個現(xiàn)有的連接。 
你檢查數(shù)據(jù)庫的錯誤日志，就會發(fā)現(xiàn)有很多額外信息，摘抄部分如下：
.........................................................
.........................................................
2019-12-24 10:15:32.84 spid53      There is insufficient system memory in resource pool 'internal' to run this query.
2019-12-24 10:15:52.88 spid53      Error: 18056, Severity: 20, State: 29. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2019-12-24 10:15:55.89 Server      Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2019-12-24 10:16:12.70 Server       Failed allocate pages: FAIL_PAGE_ALLOCATION 1
2019-12-24 10:16:12.70 Server  
 
Process/System Counts                         Value
---------------------------------------- ----------
Available Physical Memory                6614454272
Available Virtual Memory                 140726213148672
Available Paging File                    7776440320
Working Set                                95432704
Percent of Committed Memory in WS               100
Page Faults                                   57030
System physical memory high                       1
System physical memory low                        0
Process physical memory low                       1
Process virtual memory low                        0
2019-12-24 10:16:12.70 Server     
Memory Manager                                   KB
---------------------------------------- ----------
VM Reserved                                10652776
VM Committed                                  57972
Locked Pages Allocated                        86472
Large Pages Allocated                             0
Emergency Memory                               1024
Emergency Memory In Use                          16
Target Committed                             131072
Current Committed                            144448
Pages Allocated                               84176
Pages Reserved                                    0
Pages Free                                        0
Pages In Use                                 144432
Page Alloc Potential                         -19912
NUMA Growth Phase                                 2
Last OOM Factor                                   1
Last OS Error                                     0
2019-12-24 10:16:12.70 Server     
Memory node Id = 0                               KB
---------------------------------------- ----------
VM Reserved                                10652712
VM Committed                                  57952
Locked Pages Allocated                        86472
Pages Allocated                               84176
Pages Free                                        0
Target Committed                             131048
Current Committed                            144424
Foreign Committed                                 0
Away Committed                                    0
Taken Away Committed                              0
2019-12-24 10:16:12.70 Server     
Memory node Id = 64                              KB
---------------------------------------- ----------
VM Reserved                                       0
VM Committed                                     20
Locked Pages Allocated                            0
2019-12-24 10:16:12.70 Server     
MEMORYCLERK_SQLGENERAL (node 0)                  KB
---------------------------------------- ----------
.........................................................
......................................................... 
要解決這個問題，你需要關(guān)閉數(shù)據(jù)庫服務(wù)， 然后以單用戶模式+最小配置啟動數(shù)據(jù)庫實(shí)例，然后去修改max server memory參數(shù)。 關(guān)閉數(shù)據(jù)庫過程中如果遇到一些問題，可以通過重啟服務(wù)器解決問題（這個要根據(jù)具體實(shí)際情況決定，有時候不會遇到問題，有時候會遇到一些問題，例如net stop mssqlserver命令卡住，出現(xiàn)service_state[MSSQLSERVER]): Stop pending）
注意：如果以單用戶模式啟動，然后以sqlcmd去連接數(shù)據(jù)庫，就會出現(xiàn)下面錯誤，所以必須以單用戶模式+最小配置啟動數(shù)據(jù)庫實(shí)例

1 2 3 4 5

EXEC sys.sp_configure 'max server memory (MB)',4096; #根據(jù)實(shí)際情況設(shè)置內(nèi)存大小。    RECONFIGURE    GO

然后重啟SQL Server實(shí)例，問題就解決了。 當(dāng)然你也可以還原master庫的備份到其它測試數(shù)據(jù)庫，然后用還原后master數(shù)據(jù)庫的相關(guān)文件替換當(dāng)前數(shù)據(jù)庫master的相關(guān)文件來解決問題。但是那樣會相對麻煩，沒有這種方法簡便、有效！

C:\Windows\system32>net stop mssqlserver
The SQL Server (MSSQLSERVER) service is stopping.
The SQL Server (MSSQLSERVER) service was stopped successfully.
C:\Windows\system32>net start mssqlserver
The SQL Server (MSSQLSERVER) service is starting.
The SQL Server (MSSQLSERVER) service was started successfully.

總結(jié)
以上就是這篇文章的全部內(nèi)容了