艾锑知识 | SQL Server误设置max server memory的处理方法

2020-03-08 19:06 作者：艾锑无限 浏览量：

   
2015年我第一次参加阿里云杭州全球云栖大会时，看到的是惊喜和无限的可能性。
 
“2015杭州云栖大会”于10月14日至15日在杭州云栖小镇如期召开。大会以“互联网、创新、创业”为本届主题，展现“互联网+”时代下无处不在的云计算与各行各业的交错连接，介绍云计算为产业升级和改革创新提供的源源动力，挖掘云计算助力下生生不息的创业激情和机遇。
 
2015年正式更名为“云栖大会”，阿里云的名字不再出现在会议名称中，而是更加强调云计算、大数据生态的定位。量子计算、人工智能、生物识别、深度学习等前沿的科技创新力量首次在大会亮相。大会吸引了全球超过20个国家的21500名开发者，参展企业达到219家，参与企业3000多家，现场参观超过42584人次，全球直播收看人数超过127万人，成为全球最大规模的云计算峰会之一。
 
走进会场别开生面，丰富多彩，让人忘掉了科技的冰冷，就像走进了游乐园，有运动，有演出，有游戏，有展示，有互动，4万多人把整个会场挤得水泄不通，来自全球的技术爱好者，企业家，科学家，政府官员，阿里云的合作伙伴，竞争对手，闲杂人等五花八门，你能想到的人都聚齐了。
 
甚至还有票贩子，是什么吸引了各路好汉齐聚一堂，挤破头也要参加一场听起来非常专业性的云栖大会呢?
 
除了上面说的阿里云的技术能力和运营能力以外，其实还有一个非常重要的原因，就是能亲眼见到马云马老师，甚至还能有机会与他零距离接触.
 
从2015到2018年，在这四年中马老师每年都会参加杭州云栖大会的分享，而且还会出现在云栖现场的不同地方，这让很多喜欢马老师的伙伴兴奋极了，你想你一生能有几次亲眼见到“外星人”呢.


2015年他在主会场20分钟的分享表达了一个重要的观点，就是在未来5年无企业，不上云。上云是企业战略而不是战术，未来是一个万物互联的时代，只有那些在云上的企业才能快速有效的抓住时代变化的先机，才能使用计算的能力，才能利用好数据的价值.
 
当时听了马老师的演讲，我当刻就决定了和阿里云的合作，让艾锑无限成为了阿里云的战略合作伙伴，和阿里云一起为中小企业提供上云解决方案服务。
 
四年多过去了，马老师当年说无企业，不上云，如今，如果你不知道云，你的企业没有用云，可能你在这次的疫情中根本就没有战斗力，你都不知道别人企业是如何赢得这次疫情战斗胜利的.
 
企业上云不仅能为企业节省70%以上的成本，更能为企业提升500%以上的效率，甚至它的有些价值是无法计算的，这些年艾锑无限为上千家企业提供了上云服务，亲身经历了有一些企业上云变革后带来的增长和变化，我记得服务了一家装修行业的领头企业，在2016年这家企业因人员场地和机房成本，让这家公司举步维艰，离破产仅一步之遥，我和这家企业的CEO是好朋友，有一天我去看他，听到他和我分享现在遇到的巨大困境，我问了他几个关键的问题，最后确认了是成本不断提升，业绩却没有提高，效率低下从而导致企业经营困难，后来艾锑无限的云解决方案团队帮助他出了三个解决方案:
 
把他们现有机房近百台服务器迁到云上，释放出近百平米的机房空间和硬件运维升级成本，因为云是弹性的，可以根据企业用量来支付费用，这一下就帮他们减轻了一年上百万的硬件投入成本.
 
帮助他们打通各个管理系统，让企业内部数据更透明，让整体效率一下子就提升了上来，而且当他发现整个环节全部通透后，有1/3的人力是可以节省的，完全没必要用这么多人，这一下让他们每年节省了近200万的人力支出，这就是科技的力量，虽然对失业的员工有些残忍，但对与一家商业企业来说这是正确的选择.
 
重建营销系统，让传统的销售模式重新变革，提升人员的产出比，让全员进行销售，整体销售额提高了200%，这个时代职责和名称将会越来越模糊，谁能为企业创造价值，谁就是企业最重要的员工.
 
大家可以想象一下，一家企业人员降低原来的1/3，但销售额却提升原来的两倍，这意味着盈利能力和盈利水平呈指数级的增长，这就是变革后的成果.
 
无企业，不上云，你的企业上云了吗？

 
如果你的企业还没有下云也没有关系，也许你的企业还没有遇到挑战，假如你有和我朋友同样的困境，那不妨与艾锑无限的云工程师和技术专家聊一聊，说不定就让你眼前一亮，打开了你遇到的限制和困境，从而让你的企业迈上了生长的第二春。

艾锑知识 |SQL Server误设置max server memory的处理方法

在SSMS的UI界面设置“max server memory”，即使你设置为10M大小，但是它会“悄悄”默认修改为128M，你用Profile跟踪或者设置后会发现，它偷偷“修改”了你的设置值（改为了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.

如果你没有注意这些细节，或者不信这个事情，那么也可以用脚本测试一下，如下所示，它提示你这个值（10M）不是一个有效值。


当你对“max server memory”做了错误设置后，那么基本上，任何查询或连接都会出现类似下面这样的错误：
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 - 远程主机强迫关闭了一个现有的连接。) (Microsoft SQL Server, Error: 10054)
 
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476
 
------------------------------
远程主机强迫关闭了一个现有的连接。 
你检查数据库的错误日志，就会发现有很多额外信息，摘抄部分如下：
.........................................................
.........................................................
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
---------------------------------------- ----------
.........................................................
......................................................... 
要解决这个问题，你需要关闭数据库服务， 然后以单用户模式+最小配置启动数据库实例，然后去修改max server memory参数。 关闭数据库过程中如果遇到一些问题，可以通过重启服务器解决问题（这个要根据具体实际情况决定，有时候不会遇到问题，有时候会遇到一些问题，例如net stop mssqlserver命令卡住，出现service_state[MSSQLSERVER]): Stop pending）
注意：如果以单用户模式启动，然后以sqlcmd去连接数据库，就会出现下面错误，所以必须以单用户模式+最小配置启动数据库实例

1 2 3 4 5

EXEC sys.sp_configure 'max server memory (MB)',4096; #根据实际情况设置内存大小。    RECONFIGURE    GO

然后重启SQL Server实例，问题就解决了。 当然你也可以还原master库的备份到其它测试数据库，然后用还原后master数据库的相关文件替换当前数据库master的相关文件来解决问题。但是那样会相对麻烦，没有这种方法简便、有效！

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.

总结
以上就是这篇文章的全部内容了